first_rows
将优化器模式改为以最快的速度返回前几行
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
在all_rows 模式下运行sql
SQL> select *
2 from dao_object1 d1 ,dao_object1 d2
3 where d1.object_id=d2.object_id ;
75505 rows selected.
Elapsed: 00:00:01.61
Execution Plan
----------------------------------------------------------
Plan hash value: 3079158492
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 73488 | 29M| | 2128 (1)| 00:00:26 |
|* 1 | HASH JOIN | | 73488 | 29M| 15M| 2128 (1)| 00:00:26 |
| 2 | TABLE ACCESS FULL| DAO_OBJECT1 | 73487 | 14M| | 302 (1)| 00:00:04 |
| 3 | TABLE ACCESS FULL| DAO_OBJECT1 | 73487 | 14M| | 302 (1)| 00:00:04 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D1"."OBJECT_ID"="D2"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7121 consistent gets
0 physical reads
0 redo size
6626043 bytes sent via SQL*Net to client
55886 bytes received via SQL*Net from client
5035 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
75505 rows processed
SQL> select /*+first_rows */*
2 from dao_object1 d1 ,dao_object1 d2
3 where d1.object_id=d2.object_id ;
75505 rows selected.
Elapsed: 00:00:01.67
Execution Plan
----------------------------------------------------------
Plan hash value: 2313347235
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 73488 | 29M| 147K (1)| 00:29:28 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 73488 | 29M| 147K (1)| 00:29:28 |
| 3 | TABLE ACCESS FULL | DAO_OBJECT1 | 73487 | 14M| 302 (1)| 00:00:04 |
|* 4 | INDEX RANGE SCAN | IDX_DAO_OBJ1 | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECT1 | 1 | 207 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D1"."OBJECT_ID"="D2"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23656 consistent gets
0 physical reads
0 redo size
6626043 bytes sent via SQL*Net to client
55886 bytes received via SQL*Net from client
5035 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
75505 rows processed
optimizer_features_enable
optimizer_features_enable 指定优化器版本
SQL> show parameter optimizer_features_enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable string 11.2.0.3
OPTIMIZER_FEATURES_ENABLE =
{ 8.0.0 | 8.0.3 | 8.0.4 | 8.0.5 | 8.0.6 | 8.0.7 | 8.1.0 | 8.1.3 | 8.1.4 | 8.1.5 | 8.1.6 | 8.1.7 |
9.0.0 | 9.0.1 | 9.2.0 | 9.2.0.8 |
10.1.0 | 10.1.0.3 | 10.1.0.4 | 10.1.0.5 | 10.2.0.1 | 10.2.0.2 | 10.2.0.3 | 10.2.0.4 | 10.2.0.5 |
11.1.0.6 | 11.1.0.7 | 11.2.0.1 |11.2.0.2 |11.2.0.3 |11.2.0.4 }
此参数SESSION SYSTEM 级别都可改
也可以使用本文推荐的提示 optimizer_features_enable 顾名思义就是在某条sql语句中声明优化器使用的版本
下面做一个实验看一下
SQL> create table test_skip_scan
2 as
3 select mod(level,10) low_dist ,level uniq
4 from dual
5 connect by level <=1000000;
Table created.
SQL> create index IDX_DAO_SKIP on TEST_SKIP_SCAN (LOW_DIST, UNIQ);
Index created.
SQL> ANALYZE TABLE test_skip_scan COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;
Table analyzed.
SQL> select * from test_skip_scan where uniq=888 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1582535247
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 12 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | IDX_DAO_SKIP | 1 | 6 | 12 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("UNIQ"=888)
filter("UNIQ"=888)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
100 consistent gets
19 physical reads
0 redo size
596 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
select /*+ optimizer_features_enable('8.0.0') */ * from test_skip_scan where uniq=888 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2650644146
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 277 |
|* 1 | TABLE ACCESS FULL| TEST_SKIP_SCAN | 1 | 26 | 277 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("UNIQ"=888)
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1788 consistent gets
0 physical reads
0 redo size
596 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
访问路径
FULL
FULL 提示建议优化器对表进行全表扫描
SQL> select * from test_skip_scan where uniq=888 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1582535247
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 12 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | IDX_DAO_SKIP | 1 | 8 | 12 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("UNIQ"=888)
filter("UNIQ"=888)
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
46 consistent gets
0 physical reads
0 redo size
596 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+full(t) */* from test_skip_scan t where uniq=888 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2650644146
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 506 (3)| 00:00:07 |
|* 1 | TABLE ACCESS FULL| TEST_SKIP_SCAN | 1 | 8 | 506 (3)| 00:00:07 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("UNIQ"=888)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1787 consistent gets
0 physical reads
0 redo size
596 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows process
INDEX
INDEX 提示建议优化器使用索引 使用方法为表别名+索引名
SQL> select /*+index (t IDX_DAO_OBJ1) */ *
2 from DAO_OBJECT1 t
3 where t.object_id >2 ;
75504 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3915033435
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 75504 | 7152K| 1371 (1)| 00:00:17 |
| 1 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECT1 | 75504 | 7152K| 1371 (1)| 00:00:17 |
|* 2 | INDEX RANGE SCAN | IDX_DAO_OBJ1 | 75504 | | 169 (1)| 00:00:03 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."OBJECT_ID">2)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
11347 consistent gets
1 physical reads
0 redo size
8656126 bytes sent via SQL*Net to client
55886 bytes received via SQL*Net from client
5035 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
75504 rows processed
NO_INDEX
NO_INDEX 建议优化器跳过某一个索引 用法与INDEX 相同
SQL> select *
2 from DAO_OBJECT1 t
3 where t.object_id =2 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3915033435
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECT1 | 1 | 97 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_DAO_OBJ1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."OBJECT_ID"=2)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1611 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+no_index (t IDX_DAO_OBJ1) */ *
2 from DAO_OBJECT1 t
3 where t.object_id = 2
4 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1429985617
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 301 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| DAO_OBJECT1 | 1 | 97 | 301 (1)| 00:00:04 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."OBJECT_ID"=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1080 consistent gets
0 physical reads
0 redo size
1608 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
INDEX_ASC
INDEX_ASC 使索引升序扫描
这里就有一个很有意思的现象，相同的SQL 提示不同返回结果会不同
SQL> select /*+ INDEX_ASC (t IDX_DAO_OBJ1) */
2 t.object_id
3 from DAO_OBJECT1 t
4 where rownum = 1
5 and t.object_id is not null ;
OBJECT_ID
----------
2
Execution Plan
----------------------------------------------------------
Plan hash value: 1906602838
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | INDEX FULL SCAN| IDX_DAO_OBJ1 | 1 | 5 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
2 - filter("T"."OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
INDEX_DESC
INDEX_DESC 使索引降序扫描
SQL> select /*+ INDEX_DESC (t IDX_DAO_OBJ1) */
2 t.object_id
3 from DAO_OBJECT1 t
4 where rownum = 1
5 and t.object_id is not null ;
OBJECT_ID
----------
78322
Execution Plan
----------------------------------------------------------
Plan hash value: 4019600732
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | INDEX FULL SCAN DESCENDING| IDX_DAO_OBJ1 | 1 | 5 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
2 - filter("T"."OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
INDEX_COMBINE
INDEX_COMBINE 提示索引合并 分别从多个索引中去数据 之后做合并
SQL> select t.object_id
2 from DAO_OBJECT1 t
3 where t.object_id > 2
4 or t.object_name = 'DBA_OBJECTS';
75504 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1429985617
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 75504 | 2212K| 301 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| DAO_OBJECT1 | 75504 | 2212K| 301 (1)| 00:00:04 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."OBJECT_ID">2 OR "T"."OBJECT_NAME"='DBA_OBJECTS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6046 consistent gets
0 physical reads
0 redo size
1379105 bytes sent via SQL*Net to client
55886 bytes received via SQL*Net from client
5035 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
75504 rows processed
SQL> select /*+ INDEX_COMBINE(t idx_dao_obj1 idx_dao_obj2 ) */
2 t.object_id
3 from DAO_OBJECT1 t
4 where t.object_id > 2
5 or t.object_name = 'DBA_OBJECTS';
75504 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3655448469
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 75504 | 2212K| 1374 (1)| 00:00:17 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECT1 | 2 | 60 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_DAO_OBJ2 | 2 | | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECT1 | 75502 | 2211K| 1370 (1)| 00:00:17 |
|* 5 | INDEX RANGE SCAN | IDX_DAO_OBJ1 | 75504 | | 169 (1)| 00:00:03 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T"."OBJECT_NAME"='DBA_OBJECTS')
4 - filter(LNNVL("T"."OBJECT_NAME"='DBA_OBJECTS'))
5 - access("T"."OBJECT_ID">2)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
11350 consistent gets
2 physical reads
0 redo size
1379105 bytes sent via SQL*Net to client
55886 bytes received via SQL*Net from client
5035 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
75504 rows processed
INDEX_JOIN
INDEX_JOIN 提示优化器 分别从多个索引中取出符合条件的ROWID 之后做连接
SQL> select *
2 from DAO_OBJECT1 t
3 where t.object_id > 2
4 and t.object_name = 'DBA_OBJECTS';
Execution Plan
----------------------------------------------------------
Plan hash value: 3878902825
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 194 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECT1 | 2 | 194 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_DAO_OBJ2 | 2 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."OBJECT_ID">2)
2 - access("T"."OBJECT_NAME"='DBA_OBJECTS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1745 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> select /*+ INDEX_JOIN(t idx_dao_obj1 idx_dao_obj2 ) */
2 t.object_id
3 from DAO_OBJECT1 t
4 where t.object_id > 2
5 and t.object_name = 'DBA_OBJECTS';
Execution Plan
----------------------------------------------------------
Plan hash value: 2617002982
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 60 | 173 (2)| 00:00:03 |
|* 1 | VIEW | index$_join$_001 | 2 | 60 | 173 (2)| 00:00:03 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_DAO_OBJ2 | 2 | 60 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| IDX_DAO_OBJ1 | 2 | 60 | 169 (1)| 00:00:03 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."OBJECT_NAME"='DBA_OBJECTS' AND "T"."OBJECT_ID">2)
2 - access(ROWID=ROWID)
3 - access("T"."OBJECT_NAME"='DBA_OBJECTS')
4 - access("T"."OBJECT_ID">2)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
172 consistent gets
0 physical reads
0 redo size
583 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
INDEX_FFS
INDEX_FFS 建议优化器使用索引全扫描
SQL> select object_id
2 from DAO_OBJECT1 t
3 where t.object_id is not null
4 order by object_id
5 ;
75505 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3367051663
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 75505 | 368K| 169 (1)| 00:00:03 |
|* 1 | INDEX FULL SCAN | IDX_DAO_OBJ1 | 75505 | 368K| 169 (1)| 00:00:03 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5191 consistent gets
0 physical reads
0 redo size
1379109 bytes sent via SQL*Net to client
55886 bytes received via SQL*Net from client
5035 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
75505 rows processed
SQL> select /*+ INDEX_FFS(t IDX_DAO_OBJ1) */object_id
2 from DAO_OBJECT1 t
3 where t.object_id is not null
4 order by object_id ;
75505 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 934087791
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 75505 | 368K| | 281 (2)| 00:00:04 |
| 1 | SORT ORDER BY | | 75505 | 368K| 904K| 281 (2)| 00:00:04 |
|* 2 | INDEX FAST FULL SCAN| IDX_DAO_OBJ1 | 75505 | 368K| | 48 (3)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
174 consistent gets
0 physical reads
0 redo size
1379109 bytes sent via SQL*Net to client
55886 bytes received via SQL*Net from client
5035 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
75505 rows processed
NO_INDEX_FFS
NO_INDEX_FFS 建议优化器不要使用索引全扫描
SQL> select object_id
2 from DAO_OBJECT1 t
3 where t.object_id is not null ;
75505 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2475545151
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 75505 | 368K| 48 (3)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_DAO_OBJ1 | 75505 | 368K| 48 (3)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5197 consistent gets
0 physical reads
0 redo size
1379109 bytes sent via SQL*Net to client
55886 bytes received via SQL*Net from client
5035 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
75505 rows processed
SQL> select /*+ NO_INDEX_FFS(t IDX_DAO_OBJ1) */object_id
2 from DAO_OBJECT1 t
3 where t.object_id is not null
4 ;
75505 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3367051663
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 75505 | 368K| 169 (1)| 00:00:03 |
|* 1 | INDEX FULL SCAN | IDX_DAO_OBJ1 | 75505 | 368K| 169 (1)| 00:00:03 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5191 consistent gets
0 physical reads
0 redo size
1379109 bytes sent via SQL*Net to client
55886 bytes received via SQL*Net from client
5035 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
75505 rows processed
INDEX_SS_ASC
使索引跳跃扫描升序读取 一样会引起 相同语句提示不同返回数据不同的情况
SQL> select /*+ INDEX_SS_ASC (t IDX_DAO_SKIP) */ *
2 from TEST_SKIP_SCAN t
3 where rownum =1
4 and t.uniq is not null ;
LOW_DIST UNIQ
---------- ----------
0 10
Execution Plan
----------------------------------------------------------
Plan hash value: 1469963589
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 3 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | INDEX SKIP SCAN| IDX_DAO_SKIP | 1 | 8 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
2 - filter("T"."UNIQ" IS NOT NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
594 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
INDEX_SS_DESC
使索引跳跃扫描降序读取
SQL> select /*+ INDEX_SS_DESC (t IDX_DAO_SKIP) */ *
2 from TEST_SKIP_SCAN t
3 where rownum =1
4 and t.uniq is not null ;
LOW_DIST UNIQ
---------- ----------
9 999999
Execution Plan
----------------------------------------------------------
Plan hash value: 3906157
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 3 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | INDEX SKIP SCAN DESCENDING| IDX_DAO_SKIP | 1 | 8 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
2 - filter("T"."UNIQ" IS NOT NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
597 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
连接方式
使用排序合并连接
建议优化器将连接方式改为SORT MERGE JOIN
SQL> select *
2 from emp e ,dept d
3 where e.deptno=d.deptno ;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 812 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 532 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
Statistics
----------------------------------------------------------
48 recursive calls
0 db block gets
80 consistent gets
4 physical reads
0 redo size
2015 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
14 rows processed
SQL> select /*+ USE_MERGE(E,D) */*
2 from emp e ,dept d
3 where e.deptno=d.deptno;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1224078174
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 5 (20)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 812 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | IDX_DAO_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 532 | 3 (34)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
1 physical reads
0 redo size
1843 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
NO_USE_MERGE
NO_USE_MERGE 建议优化器不使用SORT MERGE JOIN
SQL> select *
2 from scott.emp e ,scott.dept d
3 where d.deptno=e.deptno;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 812 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
8 physical reads
0 redo size
1903 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
SQL> select /*+ NO_USE_MERGE(E D) */
2 *
3 from scott.emp e, scott.dept d
4 where d.deptno = e.deptno ;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 812 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
13 consistent gets
5 physical reads
0 redo size
2027 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
USE_NL
USE_NL 建议优化器将连接方式改为NESTED_LOOP
SQL> select *
2 from scott.emp e ,scott.dept d
3 where d.deptno=e.deptno;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 812 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
8 physical reads
0 redo size
1903 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
SQL> select /*+ USE_NL(E,D) */*
2 from emp e ,dept d
3 where e.deptno=d.deptno
4 ;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4192419542
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 5 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 812 | 5 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 4 | 152 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."DEPTNO"="D"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
1840 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
NO_USE_NL
NO_USE_NL 建议优化器不使用NESTED LOOP
SQL> SELECT *
2 FROM DEPT D,DAO_OBJECT1 DO
3 WHERE D.DEPTNO=DO.OBJECT_ID;
Execution Plan
----------------------------------------------------------
Plan hash value: 2753141441
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 468 | 10 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 4 | 468 | 10 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 4 | 80 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_DAO_OBJ1 | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECT1 | 1 | 97 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="DO"."OBJECT_ID")
Statistics
----------------------------------------------------------
40 recursive calls
0 db block gets
88 consistent gets
3 physical reads
0 redo size
2073 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
4 rows processed
SQL> SELECT /*+NO_USE_NL */*
2 FROM DEPT D,DAO_OBJECT1 DO
3 WHERE D.DEPTNO=DO.OBJECT_ID ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2753141441
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 468 | 10 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 4 | 468 | 10 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 4 | 80 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_DAO_OBJ1 | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECT1 | 1 | 97 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="DO"."OBJECT_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
2073 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
USE_HASH
使用哈希连接
USE_HASH 建议优化器将连接方式改为HASH_JOIN
注意:HASH JOIN 只能用于等式连接
HASH JoiN 不能用于笛卡尔积
SQL> select *
2 from scott.emp e ,scott.dept d
3 where d.deptno=e.deptno;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 812 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
8 physical reads
0 redo size
1903 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
SQL> SELECT /*+USE_HASH(D DO) */*
2 FROM DEPT D,DAO_OBJECT1 DO
3 WHERE D.DEPTNO=DO.OBJECT_ID;
Execution Plan
----------------------------------------------------------
Plan hash value: 2392879685
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 468 | 298 (2)| 00:00:04 |
|* 1 | HASH JOIN | | 4 | 468 | 298 (2)| 00:00:04 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DAO_OBJECT1 | 75500 | 7151K| 295 (1)| 00:00:04 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="DO"."OBJECT_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1079 consistent gets
1074 physical reads
0 redo size
2074 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
NO_USE_HASH
–NO_USE_HASH 建议优化器不使用HASH JOIN
SQL> select *
2 from emp e ,dept d
3 where e.deptno=d.deptno ;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 812 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 532 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
Statistics
----------------------------------------------------------
48 recursive calls
0 db block gets
80 consistent gets
4 physical reads
0 redo size
2015 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
14 rows processed
SQL> select /*+ NO_USE_HASH(E,D) */*
2 from emp e ,dept d
3 where e.deptno=d.deptno;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1224078174
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 5 (20)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 812 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | IDX_DAO_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 532 | 3 (34)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1843 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
半连接
原执行计划
SQL> select * from dept where deptno in (select deptno from emp) ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1754319153
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 129 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 3 | 129 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="DEPTNO")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
277 recursive calls
0 db block gets
48 consistent gets
7 physical reads
0 redo size
749 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
3 rows processed
NL_SJ
NL_SJ 使用嵌套循环半连接
SQL> select * from dept where deptno in (select /*+ nl_sj */deptno from emp) ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2645846736
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 129 | 8 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 3 | 129 | 8 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 11 | 143 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DEPTNO"="DEPTNO")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
749 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
3 rows processed
MERGE_SJ
merge_sj 使用排序合并半连接
SQL> select * from dept where deptno in (select /*+ merge_sj */deptno from emp) ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1477344847
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 129 | 8 (25)| 00:00:01 |
| 1 | MERGE JOIN SEMI | | 3 | 129 | 8 (25)| 00:00:01 |
| 2 | SORT JOIN | | 4 | 120 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 14 | 182 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPTNO"="DEPTNO")
filter("DEPTNO"="DEPTNO")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
749 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
3 rows processed
HASH_SJ
HASH_SJ 使用哈希半连接
SQL> select * from dept where deptno in (select /*+ hash_sj */deptno from emp) ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1754319153
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 129 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 3 | 129 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="DEPTNO")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
749 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
3 rows processed
反连接
SQL> select * from dept d where not exists (select 1 from emp e where d.deptno=e.deptno) ;
Execution Plan
----------------------------------------------------------
Plan hash value: 474461924
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 1 | 43 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
655 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
NL_AJ
nl_aj 使用嵌套反连接
SQL> select * from dept d where not exists (select /*+ nl_aj */ 1 from emp e where d.deptno=e.deptno) ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1522491139
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 8 (0)| 00:00:01 |
| 1 | NESTED LOOPS ANTI | | 1 | 43 | 8 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 11 | 143 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("D"."DEPTNO"="E"."DEPTNO")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
655 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
MERGE_AJ
MERGE_AJ 使用排序合并反连接
SQL> select * from dept d where not exists (select /*+ merge_aj */ 1 from emp e where d.deptno=e.deptno) ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2422640784
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 8 (25)| 00:00:01 |
| 1 | MERGE JOIN ANTI | | 1 | 43 | 8 (25)| 00:00:01 |
| 2 | SORT JOIN | | 4 | 120 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 14 | 182 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
655 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
HASH_AJ
HASH_AJ 使用哈希反连接
SQL> select * from dept d where not exists (select /*+ hash_aj */ 1 from emp e where d.deptno=e.deptno) ;
Execution Plan
----------------------------------------------------------
Plan hash value: 474461924
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 1 | 43 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
655 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
联结顺序
ordered
### SWAP_JOIN_INPUTS
SQL> explain plan for select * from emp e, dept d where d.deptno=e.deptno ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 798 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 798 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
15 rows selected.
SQL> explain plan for select /*+ SWAP_JOIN_INPUTS(e) */ * from emp e, dept d where d.deptno=e.deptno ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1123238657
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 798 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 798 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
15 rows selected.
SQL> select /*+ ordered */*
2 from dao_objects1 d1 ,dept d ,emp e
3 where d1.object_id=e.empno
4 and d.deptno=e.deptno ;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1585757589
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 75529 | 11M| 82237 (1)| 00:16:27 |
|* 1 | HASH JOIN | | 75529 | 11M| 82237 (1)| 00:16:27 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN| | 302K| 33M| 82232 (1)| 00:16:27 |
| 4 | TABLE ACCESS FULL | DAO_OBJECTS1 | 75529 | 7154K| 302 (1)| 00:00:04 |
| 5 | BUFFER SORT | | 4 | 80 | 81930 (1)| 00:16:24 |
| 6 | TABLE ACCESS FULL | DEPT | 4 | 80 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D1"."OBJECT_ID"="E"."EMPNO" AND "D"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1083 consistent gets
1076 physical reads
0 redo size
3859 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
leading
SQL> explain plan for select /*+ leading(e) */ * from emp e, dept d where d.deptno=e.deptno ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1123238657
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 798 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 798 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
15 rows selected.
查询改写部分
NO_QUERY_TRANSFORMATION
NO_QUERY_TRANSFORMATION 禁止查询转换
SQL> SELECT /*+ NO_QUERY_TRANSFORMATION */ employee_id, last_name
2 FROM (SELECT * FROM hr.employees e) v
3 WHERE v.last_name = 'Smith';
Execution Plan
----------------------------------------------------------
Plan hash value: 3982138326
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 2 (0)| 00:00:01 |
| 1 | VIEW | | 1 | 27 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 12 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_NAME_IX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."LAST_NAME"='Smith')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
668 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> SELECT employee_id, last_name
2 FROM (SELECT * FROM hr.employees e) v
3 WHERE v.last_name = 'Smith';
Execution Plan
----------------------------------------------------------
Plan hash value: 2077747057
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 12 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_NAME_IX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."LAST_NAME"='Smith')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
668 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
OR扩展相关
USE_CONCAT
这个提示提示优化器对OR 进行打开 即分别运行之后把结果集整合
SQL> select * from dao_objects1 where owner = 'SYS' or OBJECT_id > 2 ;
75211 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 415406725
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 75210 | 7124K| 301 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| DAO_OBJECTS1 | 75210 | 7124K| 301 (1)| 00:00:04 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID">2 OR "OWNER"='SYS')
Statistics
----------------------------------------------------------
30 recursive calls
0 db block gets
6040 consistent gets
0 physical reads
0 redo size
3905009 bytes sent via SQL*Net to client
55677 bytes received via SQL*Net from client
5016 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
75211 rows processed
SQL> select /*+ USE_CONCAT */ * from dao_objects1 where owner = 'SYS' or OBJECT_id > 2 ;
75211 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2018010538
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 75210 | 7124K| 374 (1)| 00:00:05 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECTS1 | 2507 | 237K| 73 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_OBJECTS4 | 2507 | | 6 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | DAO_OBJECTS1 | 72703 | 6886K| 301 (1)| 00:00:04 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OWNER"='SYS')
4 - filter("OBJECT_ID">2 AND LNNVL("OWNER"='SYS'))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8965 consistent gets
0 physical reads
0 redo size
3827399 bytes sent via SQL*Net to client
55677 bytes received via SQL*Net from client
5016 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
75211 rows processed
NO_EXPAND 这个提示提示禁止优化器对OR 进行打开
SQL> SELECT *
2 FROM hr.employees e, hr.departments d
3 WHERE e.manager_id = 108
4 OR d.department_id = 110;
237 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3123135571
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 237 | 20856 | 13 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | NESTED LOOPS | | 107 | 9416 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 20 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 |
| 6 | MERGE JOIN CARTESIAN | | 130 | 11440 | 9 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 5 | 340 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | EMP_MANAGER_IX | 5 | | 1 (0)| 00:00:01 |
| 9 | BUFFER SORT | | 26 | 520 | 7 (0)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | DEPARTMENTS | 26 | 520 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPARTMENT_ID"=110)
8 - access("E"."MANAGER_ID"=108)
10 - filter(LNNVL("D"."DEPARTMENT_ID"=110))
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
37 consistent gets
1 physical reads
0 redo size
19597 bytes sent via SQL*Net to client
550 bytes received via SQL*Net from client
17 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
237 rows processed
SQL> SELECT /*+ NO_EXPAND */ *
2 FROM hr.employees e, hr.departments d
3 WHERE e.manager_id = 108
4 OR d.department_id = 110 ;
237 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2968905875
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 238 | 20944 | 34 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 238 | 20944 | 34 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 540 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMPLOYEES | 9 | 612 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."MANAGER_ID"=108 OR "D"."DEPARTMENT_ID"=110)
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
224 consistent gets
0 physical reads
0 redo size
23060 bytes sent via SQL*Net to client
550 bytes received via SQL*Net from client
17 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
237 rows processed
UNNEST
SQL> select *
2 from dept d
3 where d.deptno in (select deptno from emp where rownum=1 );
Execution Plan
----------------------------------------------------------
Plan hash value: 4249928535
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 5 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 120 | 3 (0)| 00:00:01 |
|* 3 | FILTER | | | | | |
|* 4 | COUNT STOPKEY | | | | | |
| 5 | TABLE ACCESS FULL| EMP | 14 | 182 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (???)
3 - filter("DEPTNO"=:B1)
4 - filter(ROWNUM=1)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
49 recursive calls
0 db block gets
80 consistent gets
0 physical reads
0 redo size
533 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select *
2 from dept d
3 where d.deptno in (select /*+ unnest */deptno from emp where rownum=1 );
Execution Plan
----------------------------------------------------------
Plan hash value: 1946887907
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 43 | 6 (0)| 00:00:01 |
| 2 | VIEW | VW_NSO_1 | 1 | 13 | 3 (0)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | DEPT | 1 | 30 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM=1)
5 - filter("D"."DEPTNO"="$nso_col_1")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
533 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
NO_UNNEST
SQL> select *
2 from dept d
3 where d.deptno in (select deptno from emp ) ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1754319153
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 129 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 3 | 129 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="DEPTNO")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
614 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
3 rows processed
SQL> select *
2 from dept d
3 where d.deptno in (select /*+ NO_UNNEST */ deptno from emp ) ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3547749009
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 120 | 6 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 1 | 13 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "EMP" "EMP" WHERE
"DEPTNO"=:B1))
3 - filter("DEPTNO"=:B1)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
32 consistent gets
0 physical reads
0 redo size
614 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
APPEND
SQL> set linesize 200
SQL> set pagesize 2000
SQL> set autotrace trace ;
SQL> create table test_append as select * from dba_objects where 1=2 ;
SQL> insert into test_append select * from dao_objects1 ;
50320 rows created.
Execution Plan
----------------------------------------------------------
Plan hash value: 415406725
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 46429 | 8025K| 160 (2)| 00:00:02 |
| 1 | TABLE ACCESS FULL| DAO_OBJECTS1 | 46429 | 8025K| 160 (2)| 00:00:02 |
----------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
690 recursive calls
5209 db block gets
2224 consistent gets
694 physical reads
5570244 redo size
678 bytes sent via SQL*Net to client
588 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
50320 rows processed
SQL> explain plan for
2 insert /*+ append */ into test_append select * from dao_objects1 ;
Explained.
SQL> select * from table(dbms_xplan.display) ;
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 1809248951
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 46429 | 8025K| 160 (2)| 00:00:02 |
| 1 | LOAD AS SELECT | TEST_APPEND | | | | |
| 2 | TABLE ACCESS FULL| DAO_OBJECTS1 | 46429 | 8025K| 160 (2)| 00:00:02 |
-----------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
13 rows selected.
NO_APPED
SQL> explain plan for insert /*+ PARALLEL(dao_objects2) */ into dao_objects2 select * from dao_objects1 t ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4239587559
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 50320 | 4570K| 161 (3)| 00:00:02 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 50320 | 4570K| 161 (3)| 00:00:02 | Q1,01 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | DAO_OBJECTS2 | | | | | Q1,01 | PCWP | |
| 4 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 5 | PX RECEIVE | | 50320 | 4570K| 161 (3)| 00:00:02 | Q1,01 | PCWP | |
| 6 | PX SEND ROUND-ROBIN| :TQ10000 | 50320 | 4570K| 161 (3)| 00:00:02 | | S->P | RND-ROBIN |
| 7 | TABLE ACCESS FULL | DAO_OBJECTS1 | 50320 | 4570K| 161 (3)| 00:00:02 | | | |
----------------------------------------------------------------------------------------------------------------------
14 rows selected.
SQL> explain plan for insert /*+ PARALLEL(dao_objects2) NOAPPEND */ into dao_objects2 select * from dao_objects1 t ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 415406725
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 50320 | 4570K| 161 (3)| 00:00:02 |
| 1 | TABLE ACCESS FULL| DAO_OBJECTS1 | 50320 | 4570K| 161 (3)| 00:00:02 |
----------------------------------------------------------------------------------
8 rows selected.
CARDINALITY
CARDINALITY 指定基数
SQL> explain plan for select * from emp e, dept d where d.deptno=e.deptno ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 798 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 798 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
15 rows selected.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 1123238657
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 35000 | 1948K| 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 35000 | 1948K| 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 10000 | 195K| 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
15 rows selected.
AND-EQUAL
感觉有些类似与INDEX JOIN
SQL> explain plan for
2 select *
3 from dao_objects1 t
4 where t.owner='SYS'
5 and t.object_type='TABLE' ;
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1173779596
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 745 | 69285 | 76 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECTS1 | 745 | 69285 | 76 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECTS3 | 1636 | | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."OWNER"='SYS')
2 - access("T"."OBJECT_TYPE"='TABLE')
15 rows selected.
SQL> explain plan for select /*+ AND_EQUAL(t IDX_OBJECTS3 IDX_OBJECTS4) */*
2 from dao_objects1 t
3 where t.owner='SYS'
4 and t.object_type='TABLE' ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4021177426
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 745 | 69285 | 521 (1)| 00:00:07 |
|* 1 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECTS1 | 745 | 69285 | 521 (1)| 00:00:07 |
| 2 | AND-EQUAL | | | | | |
|* 3 | INDEX RANGE SCAN | IDX_OBJECTS3 | 1636 | | 5 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_OBJECTS4 | 22893 | | 55 (2)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."OBJECT_TYPE"='TABLE' AND "T"."OWNER"='SYS')
3 - access("T"."OBJECT_TYPE"='TABLE')
4 - access("T"."OWNER"='SYS')
18 rows selected.
视图合并
MERGE
SQL> select *
2 from dept d ,(select deptno,count(*) from emp group by deptno) e
3 where d.deptno=10
4 and d.deptno=e.deptno ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3977374068
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 138 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 3 | 138 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | DEPT | 1 | 20 | 3 (0)| 00:00:01 |
| 3 | VIEW | | 3 | 78 | 3 (0)| 00:00:01 |
| 4 | HASH GROUP BY | | 3 | 9 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| EMP | 3 | 9 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
2 - filter("D"."DEPTNO"=10)
5 - filter("DEPTNO"=10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
657 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)
1 rows processed
SQL> select *
2 from dept d ,(select /*+ merge */deptno,count(*) from emp group by deptno) e
3 where d.deptno=10
4 and d.deptno=e.deptno ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2006461124
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 8 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 23 | 8 (25)| 00:00:01 |
|* 2 | HASH JOIN | | 3 | 69 | 7 (15)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DEPT | 1 | 20 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMP | 3 | 9 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("D"."DEPTNO"="DEPTNO")
3 - filter("D"."DEPTNO"=10)
4 - filter("DEPTNO"=10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
657 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)
1 rows processed
NO_MERGE
SQL> SELECT *
2 FROM DEPT D ,(SELECT * FROM EMP E ) E
3 WHERE D.DEPTNO= E.DEPTNO ;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 798 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 798 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1766 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)
14 rows processed
SQL> SELECT *
2 FROM DEPT D ,(SELECT /*+ NO_MERGE */* FROM EMP E ) E
3 WHERE D.DEPTNO= E.DEPTNO ;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1099136003
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1498 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 1498 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 3 | VIEW | | 14 | 1218 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1766 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)
14 rows processed ```
PUSH_SUBQ
PUSH_SUBQ 将谓词推入子查询
SQL> select *
2 from emp e, dept d
3 where d.deptno(+) = e.deptno
4 and exists (select
5 1
6 from dao_objects1 d1
7 where e.ename = d1.object_name
8 and d1.owner = 'SYS');
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 421559285
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1232 | 35 (3)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 14 | 1232 | 35 (3)| 00:00:01 |
|* 2 | HASH JOIN OUTER | | 14 | 798 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECTS1 | 22893 | 693K| 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_OBJECTS2 | 2 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("D"."DEPTNO"(+)="E"."DEPTNO")
5 - filter("D1"."OWNER"='SYS')
6 - access("E"."ENAME"="D1"."OBJECT_NAME")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
820 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> select *
2 from emp e, dept d
3 where d.deptno(+) = e.deptno
4 and exists (select /*+ push_subq no_unnest */
5 1
6 from dao_objects1 d1
7 where e.ename = d1.object_name
8 and d1.owner = 'SYS');
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 4121379735
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 57 | 9 (12)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 1 | 57 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 37 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECTS1 | 1 | 31 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_OBJECTS2 | 2 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"(+)="E"."DEPTNO")
2 - filter( EXISTS (SELECT /*+ NO_UNNEST PUSH_SUBQ */ 0 FROM "DAO_OBJECTS1" "D1"
WHERE "D1"."OBJECT_NAME"=:B1 AND "D1"."OWNER"='SYS'))
3 - filter("D1"."OWNER"='SYS')
4 - access("D1"."OBJECT_NAME"=:B1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
31 consistent gets
0 physical reads
0 redo size
820 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
PUSH_PRED
PUSH_PRED 将谓词推进到unmergeable视图中去
SQL> select *
2 from emp e ,(select * from dao_objects1 union all select * from dao_objects2 ) v_dao
3 where e.deptno=v_dao.object_id(+);
28 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 612344248
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 469K| 95M| 331 (5)| 00:00:04 |
|* 1 | HASH JOIN OUTER | | 469K| 95M| 331 (5)| 00:00:04 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 3 | VIEW | | 100K| 16M| 321 (3)| 00:00:04 |
| 4 | UNION-ALL | | | | | |
| 5 | TABLE ACCESS FULL| DAO_OBJECTS1 | 50320 | 4570K| 161 (3)| 00:00:02 |
| 6 | TABLE ACCESS FULL| DAO_OBJECTS2 | 50330 | 4570K| 161 (3)| 00:00:02 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="V_DAO"."OBJECT_ID"(+))
Statistics
----------------------------------------------------------
2 recursive calls
1 db block gets
2160 consistent gets
0 physical reads
176 redo size
2995 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
28 rows processed
SQL> select /*+ PUSH_PRED(v_dao) */ *
2 from emp e ,(select * from dao_objects1 union all select * from dao_objects2 ) v_dao
3 where e.deptno=v_dao.object_id(+);
28 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4021029556
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 469K| 98M| 2265 (2)| 00:00:28 |
| 1 | NESTED LOOPS OUTER | | 469K| 98M| 2265 (2)| 00:00:28 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 183 | 162 (2)| 00:00:02 |
| 4 | UNION ALL PUSHED PREDICATE | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECTS1 | 1 | 93 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | IDX_OBJECTS1 | 1 | | 1 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | DAO_OBJECTS2 | 1 | 93 | 160 (2)| 00:00:02 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("DAO_OBJECTS1"."OBJECT_ID"="E"."DEPTNO")
7 - filter("DAO_OBJECTS2"."OBJECT_ID"="E"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
20278 consistent gets
0 physical reads
0 redo size
2745 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
28 rows processed
NO_PUSH_PRED
SQL> create index IDX2_DAO_OBJECTS2 ON DAO_OBJECTS2(OBJECT_ID) ;
Index created.
SQL> select *
2 from emp e ,(select * from dao_objects1 union all select * from dao_objects2 ) v_dao
3 where e.deptno=v_dao.object_id(+)
4 ;
28 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2730216525
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 469K| 98M| 59 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 469K| 98M| 59 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 183 | 4 (0)| 00:00:01 |
| 4 | UNION ALL PUSHED PREDICATE | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECTS1 | 1 | 93 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | IDX_OBJECTS1 | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECTS2 | 1 | 93 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX2_DAO_OBJECTS2 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("DAO_OBJECTS1"."OBJECT_ID"="E"."DEPTNO")
8 - access("DAO_OBJECTS2"."OBJECT_ID"="E"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
90 consistent gets
0 physical reads
0 redo size
2745 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
28 rows processed
SQL> select /*+ NO_PUSH_PRED(v_dao) */ *
2 from emp e ,(select * from dao_objects1 union all select * from dao_objects2 ) v_dao
3 where e.deptno=v_dao.object_id(+) ;
28 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 612344248
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 469K| 95M| 331 (5)| 00:00:04 |
|* 1 | HASH JOIN OUTER | | 469K| 95M| 331 (5)| 00:00:04 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 3 | VIEW | | 100K| 16M| 321 (3)| 00:00:04 |
| 4 | UNION-ALL | | | | | |
| 5 | TABLE ACCESS FULL| DAO_OBJECTS1 | 50320 | 4570K| 161 (3)| 00:00:02 |
| 6 | TABLE ACCESS FULL| DAO_OBJECTS2 | 50330 | 4570K| 161 (3)| 00:00:02 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="V_DAO"."OBJECT_ID"(+))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2146 consistent gets
0 physical reads
0 redo size
2995 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
28 rows processed
谓词排序
ORDERED_PREDICATES
CREATE TABLE TEST_ORDERED_PREDICATES AS SELECT distinct object_id,object_NAME FROM DBA_OBJECTS WHERE OWNER='SYS' and object_type='TABLE';
SQL> SELECT *
2 FROM TEST_ORDERED_PREDICATES
3 WHERE OBJECT_NAME='CON$'
4 AND OBJECT_ID=28
5 AND exists (select /*+ NO_UNNEST */ 1 from DEPT where OBJECT_ID=DEPTNO)
6 and exists (select /*+ NO_UNNEST */ 1 from emp where ename=OBJECT_NAME) ;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 285810380
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 10 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| TEST_ORDERED_PREDICATES | 1 | 79 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DEPT | 1 | 3 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMP | 1 | 6 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPT" "DEPT" WHERE
"DEPTNO"=:B1) AND EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "EMP" "EMP" WHERE
"ENAME"=:B2))
2 - filter("OBJECT_NAME"='CON$' AND "OBJECT_ID"=28)
3 - filter("DEPTNO"=:B1)
4 - filter("ENAME"=:B1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> SELECT /*+ ORDERED_PREDICATES */ *
2 FROM TEST_ORDERED_PREDICATES
3 WHERE OBJECT_NAME='CON$'
4 AND OBJECT_ID=28
5 and exists (select /*+ NO_UNNEST */ 1 from emp where ename=OBJECT_NAME)
6 AND exists (select /*+ NO_UNNEST */ 1 from DEPT where OBJECT_ID=DEPTNO) ;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2868967593
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 10 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| TEST_ORDERED_PREDICATES | 1 | 79 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 1 | 6 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| DEPT | 1 | 3 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "EMP" "EMP" WHERE "ENAME"=:B1)
AND EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPT" "DEPT" WHERE "DEPTNO"=:B2))
2 - filter("OBJECT_NAME"='CON$' AND "OBJECT_ID"=28)
3 - filter("ENAME"=:B1)
4 - filter("DEPTNO"=:B1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
其他提示
QB_NAME
SQL> select /*+ use_nl(dept emp) */ *
2 from emp
3 where deptno in (select /*+ qb_name(subq) */ deptno from dept) ;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 230627304
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 574 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 14 | 574 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 12 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1545 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL> select /*+ use_nl(dept@subq emp) */ *
2 from emp
3 where deptno in (select /*+ qb_name(subq) */ deptno from dept) ;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2633660966
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 574 | 8 (13)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 574 | 8 (13)| 00:00:01 |
| 2 | SORT UNIQUE | | 4 | 12 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 12 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMP | 4 | 152 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("DEPTNO"="DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
1546 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed