实验数据准备
SQL> create table scott.htz1 as select * from dba_objects;
SQL> create table scott.htz2 as select * from dba_objects;
SQL> create table scott.htz3 as select * from dba_objects;
SQL> create index scott.ind_htz3_object_owner on scott.htz3(object_id,owner);
set linesize 500
set termout off
alter session set statistics_level=all;
SQL> host cat plan.sql
set linesize 200 pagesize 999
测试一、驱动表无重复值
SQL>
select *
from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id
and a.object_id in (select object_id
from scott.htz3 c
where c.owner = 'SCOTT'
and a.owner = 'SYS')
;
select * from table(dbms_xplan.display_cursor(null,null,'advanced -PROJECTION -bytes iostats,last'));
Plan hash value: 1177288075
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 772 (100)| | 0 |00:00:00.06 | 1519 |
|* 1 | HASH JOIN | | 1 | 14 | 772 (1)| 00:00:10 | 0 |00:00:00.06 | 1519 |
|* 2 | HASH JOIN RIGHT SEMI | | 1 | 14 | 424 (1)| 00:00:06 | 0 |00:00:00.06 | 1519 |
|* 3 | INDEX FAST FULL SCAN| IND_HTZ3_OBJECT_OWNER | 1 | 14 | 77 (0)| 00:00:01 | 64 |00:00:00.02 | 270 |
|* 4 | TABLE ACCESS FULL | HTZ1 | 1 | 37820 | 347 (1)| 00:00:05 | 37820 |00:00:00.02 | 1249 |
| 5 | TABLE ACCESS FULL | HTZ2 | 0 | 97015 | 347 (1)| 00:00:05 | 0 |00:00:00.01 | 0 |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
2 - access("A"."OBJECT_ID"="OBJECT_ID")
3 - filter("C"."OWNER"='SCOTT')
4 - filter("A"."OWNER"='SYS')
--想整一个 filter执行计划！ 不出来,说明Oracle 版本升级后优化器确实存在内部提升！
select *
from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id
and a.object_id in
(select /*+ NO_USE_HASH(c) */ object_id
from scott.htz3 c
where c.owner = 'SCOTT'
and a.owner = 'SYS')
;
select * from table(dbms_xplan.display_cursor(null,null,'advanced -PROJECTION -bytes iostats,last'));
参考
https://www.cndba.cn/cndba/dave/article/4352
select /*+ optimizer_features_enable('10.2.0.5') */ *
from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id
and a.object_id in
(select object_id
from scott.htz3 c
where c.owner = 'SCOTT'
and a.owner = 'SYS')
;
select * from table(dbms_xplan.display_cursor(null,null,'advanced -PROJECTION -bytes iostats,last'));
Plan hash value: 286543034
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 80894 (100)| | 0 |00:00:00.78 | 41608 | 926 |
|* 1 | FILTER | | 1 | | | | 0 |00:00:00.78 | 41608 | 926 |
|* 2 | HASH JOIN | | 1 | 80459 | 2533 (1)| 00:00:31 | 87117 |00:00:00.52 | 2498 | 926 |
| 3 | TABLE ACCESS FULL| HTZ1 | 1 | 80459 | 347 (1)| 00:00:05 | 87119 |00:00:00.05 | 1249 | 0 |
| 4 | TABLE ACCESS FULL| HTZ2 | 1 | 97015 | 347 (1)| 00:00:05 | 87120 |00:00:00.08 | 1249 | 926 |
|* 5 | FILTER | | 87117 | | | | 0 |00:00:00.14 | 39110 | 0 |
|* 6 | INDEX RANGE SCAN | IND_HTZ3_OBJECT_OWNER | 37819 | 1 | 1 (0)| 00:00:01 | 0 |00:00:00.10 | 39110 | 0 |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
2 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
5 - filter(:B1='SYS')
6 - access("OBJECT_ID"=:B1 AND "C"."OWNER"='SCOTT')
select count(*) from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id;
COUNT(*)
----------
87117
select count(*) from (
select distinct a.object_id from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id);
COUNT(*)
----------
87117
select count(*) from (
select distinct a.object_id from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id and a.owner='SYS');
COUNT(*)
----------
37819
!!!Filter 驱动表的需要循环执行的次数,是驱动结果集的数量！
--index scott.ind_htz3_object_owner [object_id,owner]
select count(*) from (
select object_id,owner from scott.htz3 group by object_id,owner);
COUNT(*)
----------
87121
测试二、驱动表存在重复值
SQL> insert into scott.htz1 select * from scott.htz1;
commit;
select /*+ optimizer_features_enable('10.2.0.5') */ *
from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id
and a.object_id in
(select object_id
from scott.htz3 c
where c.owner = 'SCOTT'
and a.owner = 'SYS')
;
select * from table(dbms_xplan.display_cursor(null,null,'advanced -PROJECTION -bytes iostats,last'));
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 291K(100)| | 0 |00:00:01.13 | 86565 |
|* 1 | FILTER | | 1 | | | | 0 |00:00:01.13 | 86565 |
|* 2 | HASH JOIN | | 1 | 288K| 5409 (1)| 00:01:05 | 174K|00:00:00.69 | 3767 |
| 3 | TABLE ACCESS FULL| HTZ2 | 1 | 97015 | 347 (1)| 00:00:05 | 87120 |00:00:00.03 | 1249 |
| 4 | TABLE ACCESS FULL| HTZ1 | 1 | 324K| 694 (1)| 00:00:09 | 174K|00:00:00.06 | 2518 |
|* 5 | FILTER | | 173K| | | | 0 |00:00:00.26 | 82798 |
|* 6 | INDEX RANGE SCAN | IND_HTZ3_OBJECT_OWNER | 74784 | 1 | 1 (0)| 00:00:01 | 0 |00:00:00.17 | 82798 |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
2 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
5 - filter(:B1='SYS')
6 - access("OBJECT_ID"=:B1 AND "C"."OWNER"='SCOTT')
select count(*) from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id;
COUNT(*)
----------
174234
select count(*) from (
select distinct a.object_id from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id);
COUNT(*)
----------
87117
select count(*) from (
select distinct a.object_id from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id and a.owner='SYS');
COUNT(*)
----------
37819
select count(*) from (
select a.object_id from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id and a.owner='SYS');
COUNT(*)
----------
75638
select count(*) from scott.htz3;
87121
select count(*) from scott.htz3 where owner='SCOTT';
64
select count(*) from (
select tmp_b.object_id from (
select a.object_id from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id) tmp_a,scott.htz1 tmp_b
where tmp_a.object_id=tmp_b.object_id and tmp_b.owner='SYS');
COUNT(*)
----------
151276
select count(*) from (
select distinct tmp_b.object_id from (
select a.object_id from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id) tmp_a,scott.htz1 tmp_b
where tmp_a.object_id=tmp_b.object_id and tmp_b.owner='SYS');
COUNT(*)
----------
37819
select count(*) from (
select tmp_b.object_id from (
select a.object_id from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id and a.owner='SYS') tmp_a,scott.htz1 tmp_b
where tmp_a.object_id=tmp_b.object_id );
select *
from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id
and a.object_id in
(select /*+ no_unnest */ object_id
from scott.htz3 c
where c.owner = 'SCOTT'
and a.owner = 'SYS')
;
select * from table(dbms_xplan.display_cursor(null,null,'advanced -PROJECTION -bytes iostats,last'));
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2663 (100)| | 0 |00:00:00.53 | 42877 |
|* 1 | FILTER | | 1 | | | | 0 |00:00:00.53 | 42877 |
|* 2 | HASH JOIN | | 1 | 3246 | 1040 (1)| 00:00:13 | 75638 |00:00:00.33 | 3767 |
|* 3 | TABLE ACCESS FULL| HTZ1 | 1 | 3246 | 692 (1)| 00:00:09 | 75640 |00:00:00.04 | 2518 |
| 4 | TABLE ACCESS FULL| HTZ2 | 1 | 97015 | 347 (1)| 00:00:05 | 87120 |00:00:00.04 | 1249 |
|* 5 | INDEX RANGE SCAN | IND_HTZ3_OBJECT_OWNER | 37819 | 1 | 1 (0)| 00:00:01 | 0 |00:00:00.12 | 39110 |
-------------------------------------------------------------------------------------------------------------------------------
浙公网安备 33010602011771号