欢迎大家加入ORACLE超级群：
17115662 免费解决各种ORACLE问题
本实验测试的是子查询中包括主表的过滤条件时，子查询是否会unnest，通常情况下，unnest的效率会更高，但是如果遇到子查询只还回几行的时候，其实我们可以把子查询当成一个查询结果用于做驱动表，从10.2.0.5以后的版本，子查询中包括 主表的过滤条件时，也会自动unnset。下面测试的版本，包括 10.2.0.1/10.2.0.4/10.2.0.5，最后测试了如果不自动unnest，怎么优化这样的SQL
1，10.2.0.1版本
2，10.2.0.4版本
3，10.2.0.5版本
4，10.2.0.1版本的优化技巧
创建测试表
|
SQL> drop table scott.test; Table dropped. SQL> drop table scott.test1; Table dropped. SQL> create table scott.test as select * from dba_objects; Table created. SQL> create table scott.test1 as select * from dba_objects where rownum<1000; Table created. |
1，10.2.0.1版本
|
SQL> select * from v$version;
BANNER —————————————————————- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bi PL/SQL Release 10.2.0.4.0 – Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 – Production NLSRTL Version 10.2.0.4.0 – Production
SQL> set lines 200 SQL> select a.object_name, owner 2 from scott.test a 3 where exists (select 1 4 from scott.test1 b 5 where a.object_id = b.object_id 6 and a.owner = ‘SYS’);
913 rows selected.
Execution Plan ———————————————————- Plan hash value: 353051353
—————————————————————————– | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | —————————————————————————– | 0 | SELECT STATEMENT | | 1 | 96 | 85477 (1)| 00:17:06 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL | TEST | 44716 | 4192K| 158 (1)| 00:00:02 | |* 3 | FILTER | | | | | | |* 4 | TABLE ACCESS FULL| TEST1 | 10 | 130 | 2 (0)| 00:00:01 | —————————————————————————–
Predicate Information (identified by operation id): —————————————————
1 – filter( EXISTS (SELECT /*+ */ 0 FROM “SCOTT”.”TEST1″ “B” WHERE :B1=’SYS’ AND “B”.”OBJECT_ID”=:B2)) 3 – filter(:B1=’SYS’) 4 – filter(“B”.”OBJECT_ID”=:B1)
Note —– – dynamic sampling used for this statement
Statistics ———————————————————- 0 recursive calls 0 db block gets 339145 consistent gets 0 physical reads 0 redo size 29653 bytes sent via SQL*Net to client 1152 bytes received via SQL*Net from client 62 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 913 rows processed |
这里发现，子查询根本没有unnest
下面通过提示要强制unnest
|
SQL> select a.object_name, owner 2 from scott.test a 3 where exists (select /*+ unnest */ 1 4 from scott.test1 b 5 where a.object_id = b.object_id 6 and a.owner = ‘SYS’);
913 rows selected.
Execution Plan ———————————————————- Plan hash value: 353051353
—————————————————————————– | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | —————————————————————————– | 0 | SELECT STATEMENT | | 1 | 96 | 114K (1)| 00:22:49 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL | TEST | 58924 | 5524K| 157 (2)| 00:00:02 | |* 3 | FILTER | | | | | | |* 4 | TABLE ACCESS FULL| TEST1 | 10 | 130 | 2 (0)| 00:00:01 | —————————————————————————– |
仍然无效，说明根本不能进行unnest
2,10.2.0.4版本
|
SQL> select * from v$version;
BANNER —————————————————————- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bi PL/SQL Release 10.2.0.4.0 – Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 – Production NLSRTL Version 10.2.0.4.0 – Production
SQL> set lines 200 SQL> select a.object_name, owner 2 from scott.test a 3 where exists (select 1 4 from scott.test1 b 5 where a.object_id = b.object_id 6 and a.owner = ‘SYS’);
913 rows selected.
Execution Plan ———————————————————- Plan hash value: 353051353
—————————————————————————– | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | —————————————————————————– | 0 | SELECT STATEMENT | | 1 | 96 | 85477 (1)| 00:17:06 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL | TEST | 44716 | 4192K| 158 (1)| 00:00:02 | |* 3 | FILTER | | | | | | |* 4 | TABLE ACCESS FULL| TEST1 | 10 | 130 | 2 (0)| 00:00:01 | —————————————————————————–
Predicate Information (identified by operation id): —————————————————
1 – filter( EXISTS (SELECT /*+ */ 0 FROM “SCOTT”.”TEST1″ “B” WHERE :B1=’SYS’ AND “B”.”OBJECT_ID”=:B2)) 3 – filter(:B1=’SYS’) 4 – filter(“B”.”OBJECT_ID”=:B1)
Note —– – dynamic sampling used for this statement
Statistics ———————————————————- 0 recursive calls 0 db block gets 339145 consistent gets 0 physical reads 0 redo size 29653 bytes sent via SQL*Net to client 1152 bytes received via SQL*Net from client 62 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 913 rows processed |
下面通过提示来强制
|
SQL> set autotrace traceonly; SQL> select a.object_name, owner 2 from scott.test a 3 where exists (select /*+ unnest */ 1 4 from scott.test1 b 5 where a.object_id = b.object_id 6 and a.owner = ‘SYS’);
913 rows selected.
Execution Plan ———————————————————- Plan hash value: 353051353
—————————————————————————– | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | —————————————————————————– | 0 | SELECT STATEMENT | | 1 | 96 | 85477 (1)| 00:17:06 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL | TEST | 44716 | 4192K| 158 (1)| 00:00:02 | |* 3 | FILTER | | | | | | |* 4 | TABLE ACCESS FULL| TEST1 | 10 | 130 | 2 (0)| 00:00:01 | —————————————————————————–
Predicate Information (identified by operation id): —————————————————
1 – filter( EXISTS (SELECT /*+ UNNEST */ 0 FROM “SCOTT”.”TEST1″ “B” WHERE :B1=’SYS’ AND “B”.”OBJECT_ID”=:B2)) 3 – filter(:B1=’SYS’) 4 – filter(“B”.”OBJECT_ID”=:B1)
Note —– – dynamic sampling used for this statement |
提示仍然无效
3.10.2.0.5
|
SQL> select * from v$version;
BANNER —————————————————————- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bi PL/SQL Release 10.2.0.5.0 – Production CORE 10.2.0.5.0 Production TNS for Linux: Version 10.2.0.5.0 – Production NLSRTL Version 10.2.0.5.0 – Production
SQL> select a.object_name, owner 2 from scott.test a 3 where exists (select 1 4 from scott.test1 b 5 where a.object_id = b.object_id 6 and a.owner = ‘SYS’);
913 rows selected.
Execution Plan ———————————————————- Plan hash value: 2133522026
—————————————————————————— | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | —————————————————————————— | 0 | SELECT STATEMENT | | 999 | 106K| 165 (2)| 00:00:02 | |* 1 | HASH JOIN RIGHT SEMI| | 999 | 106K| 165 (2)| 00:00:02 | | 2 | TABLE ACCESS FULL | TEST1 | 999 | 12987 | 5 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL | TEST | 26856 | 2517K| 159 (2)| 00:00:02 | ——————————————————————————
Predicate Information (identified by operation id): —————————————————
1 – access(“A”.”OBJECT_ID”=”B”.”OBJECT_ID”) 3 – filter(“A”.”OWNER”=’SYS’)
Note —– – dynamic sampling used for this statement
Statistics ———————————————————- 528 recursive calls 0 db block gets 923 consistent gets 712 physical reads 332 redo size 29656 bytes sent via SQL*Net to client 1152 bytes received via SQL*Net from client 62 SQL*Net roundtrips to/from client 10 sorts (memory) 0 sorts (disk) 913 rows processed |
这里发生了Unnest
下面是测试unnest与no_unnest的效率
|
SQL> select a.object_name, owner 2 from scott.test a 3 where exists (select 1 4 from scott.test1 b 5 where a.object_id = b.object_id 6 and a.owner = ‘SYS’);
913 rows selected.
Elapsed: 00:00:00.01
Execution Plan ———————————————————- Plan hash value: 2133522026
—————————————————————————— | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | —————————————————————————— | 0 | SELECT STATEMENT | | 999 | 106K| 165 (2)| 00:00:02 | |* 1 | HASH JOIN RIGHT SEMI| | 999 | 106K| 165 (2)| 00:00:02 | | 2 | TABLE ACCESS FULL | TEST1 | 999 | 12987 | 5 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL | TEST | 26856 | 2517K| 159 (2)| 00:00:02 | ——————————————————————————
Predicate Information (identified by operation id): —————————————————
1 – access(“A”.”OBJECT_ID”=”B”.”OBJECT_ID”) 3 – filter(“A”.”OWNER”=’SYS’)
Note —– – dynamic sampling used for this statement
Statistics ———————————————————- 0 recursive calls 0 db block gets 770 consistent gets 0 physical reads 0 redo size 29656 bytes sent via SQL*Net to client 1152 bytes received via SQL*Net from client 62 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 913 rows processed
SQL> select a.object_name, owner 2 from scott.test a 3 where exists (select /*+ no_unnest */ 1 4 from scott.test1 b 5 where a.object_id = b.object_id 6 and a.owner = ‘SYS’);
913 rows selected.
Elapsed: 00:00:00.94
Execution Plan ———————————————————- Plan hash value: 353051353
—————————————————————————– | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | —————————————————————————– | 0 | SELECT STATEMENT | | 1 | 96 | 107K (1)| 00:21:28 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL | TEST | 55572 | 5209K| 159 (2)| 00:00:02 | |* 3 | FILTER | | | | | | |* 4 | TABLE ACCESS FULL| TEST1 | 10 | 130 | 2 (0)| 00:00:01 | —————————————————————————–
Predicate Information (identified by operation id): —————————————————
1 – filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM “SCOTT”.”TEST1″ “B” WHERE :B1=’SYS’ AND “B”.”OBJECT_ID”=:B2)) 3 – filter(:B1=’SYS’) 4 – filter(“B”.”OBJECT_ID”=:B1)
Note —– – dynamic sampling used for this statement
Statistics ———————————————————- 15 recursive calls 0 db block gets 340705 consistent gets 0 physical reads 0 redo size 29656 bytes sent via SQL*Net to client 1152 bytes received via SQL*Net from client 62 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 913 rows processed |
4，10.2.0.1版本中怎么优化
|
SQL> set timing on; SQL> select a.object_name, owner 2 from scott.test a 3 where exists (select 1 4 from scott.test1 b 5 where a.object_id = b.object_id 6 and a.owner = ‘SYS’);
913 rows selected.
Elapsed: 00:00:00.91 这里发现使用时间是00.91 Execution Plan ———————————————————- Plan hash value: 353051353
—————————————————————————– | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | —————————————————————————– | 0 | SELECT STATEMENT | | 1 | 96 | 114K (1)| 00:22:49 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL | TEST | 58924 | 5524K| 157 (2)| 00:00:02 | |* 3 | FILTER | | | | | | |* 4 | TABLE ACCESS FULL| TEST1 | 10 | 130 | 2 (0)| 00:00:01 | —————————————————————————–
Predicate Information (identified by operation id): —————————————————
1 – filter( EXISTS (SELECT /*+ */ 0 FROM “SCOTT”.”TEST1″ “B” WHERE :B1=’SYS’ AND “B”.”OBJECT_ID”=:B2)) 3 – filter(:B1=’SYS’) 4 – filter(“B”.”OBJECT_ID”=:B1)
Note —– – dynamic sampling used for this statement
Statistics ———————————————————- 0 recursive calls 0 db block gets 338788 consistent gets 0 physical reads 0 redo size 29654 bytes sent via SQL*Net to client 1129 bytes received via SQL*Net from client 62 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 913 rows processed
下面是优化后的效果 SQL> with b as 2 (select /*+ materialize */ 3 object_id 4 from scott.test1) 5 select a.object_name, a.owner 6 from scott.test a 7 where exists (select 1 8 from b 9 where a.object_id = b.object_id 10 and a.owner = ‘SYS’);
913 rows selected.
Elapsed: 00:00:00.82 这里的效果，时间减少不是很多，因为子查询中的表本来就不大， Execution Plan ———————————————————- Plan hash value: 1200970861
——————————————————————————————————- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ——————————————————————————————————- | 0 | SELECT STATEMENT | | 1 | 96 | 114K (1)| 00:22:59 | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | 2 | LOAD AS SELECT | TEST | | | | | | 3 | TABLE ACCESS FULL | TEST1 | 999 | 12987 | 5 (0)| 00:00:01 | |* 4 | FILTER | | | | | | | 5 | TABLE ACCESS FULL | TEST | 58924 | 5524K| 157 (2)| 00:00:02 | |* 6 | FILTER | | | | | | |* 7 | VIEW | | 999 | 12987 | 2 (0)| 00:00:01 | | 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6604_876E8 | 999 | 12987 | 2 (0)| 00:00:01 | ——————————————————————————————————-
Predicate Information (identified by operation id): —————————————————
4 – filter( EXISTS (SELECT /*+ */ 0 FROM (SELECT /*+ CACHE_TEMP_TABLE (“T1”) */ “C0” “OBJECT_ID” FROM “SYS”.”SYS_TEMP_0FD9D6604_876E8″ “T1”) “B” WHERE :B1=’SYS’ AND “B”.”OBJECT_ID”=:B2)) 6 – filter(:B1=’SYS’) 7 – filter(“B”.”OBJECT_ID”=:B1)
Note —– – dynamic sampling used for this statement
Statistics ———————————————————- 2 recursive calls 9 db block gets 91726 consistent gets 这里发现逻辑读减少很多的。 2 physical reads 604 redo size 29654 bytes sent via SQL*Net to client 1129 bytes received via SQL*Net from client 62 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 913 rows processed |
子查询中有主表过滤条件，子查询是否unnest：等您坐沙发呢！