分享
 
 
 

Oracle9i数据库WITH查询语法小议

王朝oracle·作者佚名  2008-05-21
窄屏简体版  字體: |||超大  

Oracle9i新增了WITH语法功能,可以将查询中的子查询命名,放到SELECT语句的最前面。

下面看一个简单的例子:

SQL> WITH

2 SEG AS (SELECT SEGMENT_NAME, SUM(BYTES)/1024 K FROM USER_SEGMENTS GROUP BY SEGMENT_NAME),

3 OBJ AS (SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS)

4 SELECT O.OBJECT_NAME, OBJECT_TYPE, NVL(S.K, 0) SIZE_K

5 FROM OBJ O, SEG S

6 WHERE O.OBJECT_NAME = S.SEGMENT_NAME (+)

7 ;

OBJECT_NAME OBJECT_TYPE SIZE_K

DAIJC_TEST TABLE 128

P_TEST PROCEDURE 0

IND_DAIJC_TEST_C1 INDEX 128

通过WITH语句定义了两个子查询SEG和OBJ,在随后的SELECT语句中可以直接对预定义的子查询进行查询。从上面的例子也可以看出,使用WITH语句,将一个包含聚集、外连接等操作SQL清晰的展现出来。

WITH定义的子查询不仅可以使查询语句更加简单、清晰,而且WITH定义的子查询还具有在SELECT语句的任意层均可见的特点。

即使是在WITH的定义层中,后定义的子查询都可以使用前面已经定义好的子查询:

bordercolorlight = "black" bordercolordark = "#FFFFFF" align="center"

SQL> WITH

2 Q1 AS (SELECT 3 + 5 S FROM DUAL),

3 Q2 AS (SELECT 3 * 5 M FROM DUAL),

4 Q3 AS (SELECT S, M, S + M, S * M FROM Q1, Q2)

5 SELECT * FROM Q3;

S M S+M S*M

8 15 23 120

利用WITH定义查询中出现多次的子查询还能带来性能提示。Oracle会对WITH进行性能优化,当需要多次访问WITH定义的子查询时,Oracle会将子查询的结果放到一个临时表中,避免同样的子查询多次执行,从而有效的减少了查询的IO数量。

看一个简单的例子,首先构造一张大表,现在要取出大表中ID最小、ID最大以及ID等于平均值的记录,看看普通写法和WITH语句的区别:

bordercolorlight = "black" bordercolordark = "#FFFFFF" align="center"

SQL> CREATE TABLE T_WITH AS SELECT ROWNUM ID, A.* FROM DBA_SOURCE A WHERE ROWNUM < 100001;

表已创建。

SQL> SET TIMING ON

SQL> SET AUTOT ON

SQL> SELECT ID, NAME FROM T_WITH

2 WHERE ID IN

3 (

4 SELECT MAX(ID) FROM T_WITH

5 UNION ALL

6 SELECT MIN(ID) FROM T_WITH

7 UNION ALL

8 SELECT TRUNC(AVG(ID)) FROM T_WITH

9 );

ID NAME

1 STANDARD

50000 DBMS_BACKUP_RESTORE

100000 INITJVMAUX

已用时间: 00: 00: 00.09

执行计划

Plan hash value: 647530712

-----------------------------------------------------------

| Id | Operation | Name | Rows | Bytes |

-----------------------------------------------------------

| 0 | SELECT STATEMENT | | 3 | 129 |

|* 1 | HASH JOIN | | 3 | 129 |

| 2 | VIEW | VW_NSO_1 | 3 | 39 |

| 3 | HASH UNIQUE | | 3 | 39 |

| 4 | UNION-ALL | | | |

| 5 | SORT AGGREGATE | | 1 | 13 |

| 6 | TABLE ACCESS FULL| T_WITH | 112K| 1429K|

| 7 | SORT AGGREGATE | | 1 | 13 |

| 8 | TABLE ACCESS FULL| T_WITH | 112K| 1429K|

| 9 | SORT AGGREGATE | | 1 | 13 |

| 10 | TABLE ACCESS FULL| T_WITH | 112K| 1429K|

| 11 | TABLE ACCESS FULL | T_WITH | 112K| 3299K|

-----------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("ID"="$nso_col_1")

Note

-----

- dynamic sampling used for this statement

统计信息

----------------------------------------------------------

0 recursive calls

0 db block gets

5529 consistent gets

0 physical reads

0 redo size

543 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

3 rows processed

为了避免第一次执行时物理读的影响,查询结果选取了SQL的第三次运行,物理读为0时的统计信息。

观察执行计划可以看到,先后对T_WITH表进行了4次全表扫描,并产生了5529个逻辑读。下面看看WITH语句的表现:

bordercolorlight = "black" bordercolordark = "#FFFFFF" align="center"

SQL> WITH

2 AGG AS (SELECT MAX(ID) MAX, MIN(ID) MIN, TRUNC(AVG(ID)) AVG FROM T_WITH)

3 SELECT ID, NAME FROM T_WITH

4 WHERE ID IN

5 (

6 SELECT MAX FROM AGG

7 UNION ALL

8 SELECT MIN FROM AGG

9 UNION ALL

10 SELECT AVG FROM AGG

11 );

ID NAME

---------- ------------------------------

1 STANDARD

50000 DBMS_BACKUP_RESTORE

100000 INITJVMAUX

已用时间: 00: 00: 00.07

执行计划

----------------------------------------------------------

Plan hash value: 1033356310

----------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes |

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3 | 129 |

| 1 | TEMP TABLE TRANSFORMATION | | | |

| 2 | LOAD AS SELECT | T_WITH | | |

| 3 | SORT AGGREGATE | | 1 | 13 |

| 4 | TABLE ACCESS FULL | T_WITH | 112K| 1429K|

|* 5 | HASH JOIN | | 3 | 129 |

| 6 | VIEW | VW_NSO_1 | 3 | 39 |

| 7 | HASH UNIQUE | | 3 | 39 |

| 8 | UNION-ALL | | | |

| 9 | VIEW | | 1 | 13 |

| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662E_BF2EDF12 | 1 | 13 |

| 11 | VIEW | | 1 | 13 |

| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662E_BF2EDF12 | 1 | 13 |

| 13 | VIEW | | 1 | 13 |

| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662E_BF2EDF12 | 1 | 13 |

| 15 | TABLE ACCESS FULL | T_WITH | 112K| 3299K|

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

5 - access("ID"="$nso_col_1")

Note

-----

- dynamic sampling used for this statement

统计信息

----------------------------------------------------------

2 recursive calls

8 db block gets

2776 consistent gets

1 physical reads

648 redo size

543 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

3 rows processed

观察这次的执行计划,发现只对T_WITH表进行了两次全表扫描,而从逻辑读上也可以观察到,这次只产生了2776的逻辑读,正好是上面不使用WITH语句的一半。

通过分析执行计划,Oracle执行了WITH子查询一次,并将结果放到了临时表中,在随后对子查询的多次访问中,都从临时表中直接读取了数据,这应该也是那1个物理读的由来。

通过上面的例子可以看到,将子查询放到WITH语句中不仅可以简化查询语句的结构,对于子查询需要多次执行的情况,还有可能提示查询的性能。

可惜的是,WITH语句只能用在SELECT语句中,UPDATE和DELETE语句不支持WITH语法:

bordercolorlight = "black" bordercolordark = "#FFFFFF" align="center"

SQL> SET AUTOT OFF

SQL> SET TIMING OFF

SQL> WITH SUBQ AS (SELECT 1 FROM DUAL)

2 SELECT ID, NAME FROM T_WITH WHERE ID IN (SELECT * FROM SUBQ);

ID NAME

---------- ------------------------------

1 STANDARD

SQL> WITH SUBQ AS (SELECT 1 FROM DUAL)

2 UPDATE T_WITH SET ID = 1 WHERE ID IN (SELECT * FROM SUBQ);

UPDATE T_WITH SET ID = 1 WHERE ID IN (SELECT * FROM SUBQ)

*第 2 行出现错误:

ORA-00928: 缺失 SELECT 关键字

SQL> WITH SUBQ AS (SELECT 1 FROM DUAL)

2 DELETE T_WITH WHERE ID IN (SELECT * FROM SUBQ);

DELETE T_WITH WHERE ID IN (SELECT * FROM SUBQ)

*第 2 行出现错误:

ORA-00928: 缺失 SELECT 关键字

(T114)

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有