环境准备
create table DEPT
(
deptno NUMBER(2),
dname VARCHAR2(14) not null,
loc VARCHAR2(13) not null
)
;
create table EMP
(
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
)
;
insert into DEPT (deptno, dname, loc)
values (50, 'aa', 'ccc');
insert into DEPT (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into DEPT (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into DEPT (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into DEPT (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');
commit;
prompt 5 records loaded
prompt Loading EMP...
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, null, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450, null, 10);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, null, 10);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, null, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, null, 10);
commit;
create table dao_objects1
as select * from dba_objects ;
create table dao_objects2
as select * from dba_objects ;
create index idx_obj_id_1 on dao_objects1(object_id) ;
create index idx_obj_name_1 on dao_objects1(object_name) ;
优化器相关
[oracle@daoserver ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 2 09:39:12 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set linesize 200
SQL> set pagesize 20000
SQL> show parameter opt
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string none
object_cache_optimal_size integer 102400
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.2.0.3
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
plsql_optimize_level integer 2
optimizer_features_enable
指定优化器版本,通常在升级后使用
create table test_skip (c1 number , c2 number ) ;
begin
for i in 1..1000000
loop
insert into test_skip values(0,i) ;
insert into test_skip values(1,i) ;
if mod(i,5000)=0 then
commit ;
end if ;
end loop ;
end ;
create index index_skip on test_skip(c1,c2);
begin
dbms_stats.gather_table_stats(ownname => 'DAO',tabname => 'TEST_SKIP',estimate_percent => 100,cascade => TRUE);
end ;
SQL> set autotrace trace
SQL> set linesize 200
SQL> set pagesize 2000
SQL> select * from test_skip where c2 =2 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2486253378
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 16 | 4 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | INDEX_SKIP | 2 | 16 | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C2"=2)
filter("C2"=2)
SQL> select /*+ optimizer_features_enable('8.0.0') */ * from test_skip where c2 =2 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2954005509
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 16 | 535 |
|* 1 | TABLE ACCESS FULL| TEST_SKIP | 2 | 16 | 535 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C2"=2)
optimizer_mode
指定优化器模式(ALL_ROWS,FIRST_ROWS)
SQL> select *
from dao_objects1 d1, dao_objects d2
where d1.object_id = d2.object_id ;
2 3
75278 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3133027981
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74819 | 21M| | 1769 (1)| 00:00:22 |
|* 1 | HASH JOIN | | 74819 | 21M| 8040K| 1769 (1)| 00:00:22 |
| 2 | TABLE ACCESS FULL| DAO_OBJECTS | 75483 | 7150K| | 302 (1)| 00:00:04 |
| 3 | TABLE ACCESS FULL| DAO_OBJECTS1 | 74819 | 14M| | 301 (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)
SQL> select /*+ first_rows */ *
from dao_objects1 d1, dao_objects d2
where d1.object_id = d2.object_id
2 3 4 ;
75278 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3367069654
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74819 | 21M| 150K (1)| 00:30:08 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 74819 | 21M| 150K (1)| 00:30:08 |
| 3 | TABLE ACCESS FULL | DAO_OBJECTS | 75483 | 7150K| 302 (1)| 00:00:04 |
|* 4 | INDEX RANGE SCAN | IDX_OBJ_ID_1 | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECTS1 | 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)
访问路径
SQL> select *
from dao_objects1
where object_id = 51
and object_name = 'I_CON1' ; 2 3 4
Execution Plan
----------------------------------------------------------
Plan hash value: 4283110399
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 1449 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECTS1 | 7 | 1449 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJ_ID_1 | 12 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='I_CON1')
2 - access("OBJECT_ID"=51)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
74 consistent gets
0 physical reads
0 redo size
1606 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
full
指定表访问路径为全表扫描
SQL> select /*+ full( d1 ) */ *
from dao_objects1 d1
where object_id = 51
and object_name = 'I_CON1' ; 2 3 4
Execution Plan
----------------------------------------------------------
Plan hash value: 415406725
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 1449 | 300 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| DAO_OBJECTS1 | 7 | 1449 | 300 (1)| 00:00:04 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=51 AND "OBJECT_NAME"='I_CON1')
Note
-----
- dynamic sampling used for this statement (level=2)
index
使用指定的索引,格式为index(表名,索引名)
- 注意:在SQL语句中,表有别名,一定要用别名
SQL> select /*+ index( d1 idx_obj_name_1) */ *
from dao_objects1 d1
where object_id = 51
and object_name = 'I_CON1' ; 2 3 4
Execution Plan
----------------------------------------------------------
Plan hash value: 3564663942
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 1449 | 10 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECTS1 | 7 | 1449 | 10 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJ_NAME_1 | 12 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=51)
2 - access("OBJECT_NAME"='I_CON1')
Note
-----
- dynamic sampling used for this statement (level=2)
no_index
- 禁用指定的索引,格式为index(表名,索引名)
SQL> select /*+ no_index( d1 IDX_OBJ_ID_1) */ *
from dao_objects1 d1
where object_id = 51
and object_name = 'I_CON1' ; 2 3 4
Execution Plan
----------------------------------------------------------
Plan hash value: 3564663942
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 1449 | 10 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECTS1 | 7 | 1449 | 10 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJ_NAME_1 | 12 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=51)
2 - access("OBJECT_NAME"='I_CON1')
Note
-----
- dynamic sampling used for this statement (level=2)
关联方式
USE_HASH
SQL> select *
from dao_objects1 d1, dao_objects d2
where d1.object_id = d2.object_id
and rownum =1 ; 2 3 4
Execution Plan
----------------------------------------------------------
Plan hash value: 3282424980
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 304 | 7 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 3 | 912 | 7 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | DAO_OBJECTS | 3 | 291 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_OBJ_ID_1 | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECTS1 | 1 | 207 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
5 - access("D1"."OBJECT_ID"="D2"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> select /*+ use_hash(d1 d2 ) */ *
from dao_objects1 d1, dao_objects d2
where d1.object_id = d2.object_id
and rownum =1 ; 2 3 4
Execution Plan
----------------------------------------------------------
Plan hash value: 1706612812
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 511 | | 1471 (1)| 00:00:18 |
|* 1 | COUNT STOPKEY | | | | | | |
|* 2 | HASH JOIN | | 74819 | 36M| 8040K| 1471 (1)| 00:00:18 |
| 3 | TABLE ACCESS FULL| DAO_OBJECTS | 75483 | 7150K| | 302 (1)| 00:00:04 |
| 4 | TABLE ACCESS FULL| DAO_OBJECTS1 | 74819 | 14M| | 3 (34)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
2 - access("D1"."OBJECT_ID"="D2"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
USE_NL
SQL> select *
from dao_objects1 d1, dao_objects d2
where d1.object_id = d2.object_id ; 2 3
Execution Plan
----------------------------------------------------------
Plan hash value: 3133027981
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74819 | 21M| | 1769 (1)| 00:00:22 |
|* 1 | HASH JOIN | | 74819 | 21M| 8040K| 1769 (1)| 00:00:22 |
| 2 | TABLE ACCESS FULL| DAO_OBJECTS | 75483 | 7150K| | 302 (1)| 00:00:04 |
| 3 | TABLE ACCESS FULL| DAO_OBJECTS1 | 74819 | 14M| | 301 (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)
SQL> select /*+ use_nl(d1 d2 ) */ *
from dao_objects1 d1, dao_objects d2
where d1.object_id = d2.object_id ; 2 3
Execution Plan
----------------------------------------------------------
Plan hash value: 3367069654
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74819 | 21M| 150K (1)| 00:30:08 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 74819 | 21M| 150K (1)| 00:30:08 |
| 3 | TABLE ACCESS FULL | DAO_OBJECTS | 75483 | 7150K| 302 (1)| 00:00:04 |
|* 4 | INDEX RANGE SCAN | IDX_OBJ_ID_1 | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECTS1 | 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)
USE_MERGE
SQL> select *
from dao_objects1 d1, dao_objects d2
where d1.object_id = d2.object_id ; 2 3
Execution Plan
----------------------------------------------------------
Plan hash value: 3133027981
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74819 | 21M| | 1769 (1)| 00:00:22 |
|* 1 | HASH JOIN | | 74819 | 21M| 8040K| 1769 (1)| 00:00:22 |
| 2 | TABLE ACCESS FULL| DAO_OBJECTS | 75483 | 7150K| | 302 (1)| 00:00:04 |
| 3 | TABLE ACCESS FULL| DAO_OBJECTS1 | 74819 | 14M| | 301 (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)
SQL> select /*+ use_merge(d1 d2 ) */ *
from dao_objects1 d1, dao_objects d2
where d1.object_id = d2.object_id ; 2 3
Execution Plan
----------------------------------------------------------
Plan hash value: 1838142143
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74819 | 21M| | 3366 (1)| 00:00:41 |
| 1 | MERGE JOIN | | 74819 | 21M| | 3366 (1)| 00:00:41 |
| 2 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECTS1 | 74819 | 14M| | 1389 (1)| 00:00:17 |
| 3 | INDEX FULL SCAN | IDX_OBJ_ID_1 | 74819 | | | 182 (1)| 00:00:03 |
|* 4 | SORT JOIN | | 75483 | 7150K| 19M| 1977 (1)| 00:00:24 |
| 5 | TABLE ACCESS FULL | DAO_OBJECTS | 75483 | 7150K| | 302 (1)| 00:00:04 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D1"."OBJECT_ID"="D2"."OBJECT_ID")
filter("D1"."OBJECT_ID"="D2"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
以上提示适用于普通连接,外连接,笛卡尔积连接(use_hash 不适用于笛卡尔积连接)
NO_USE_HASH
SQL> SELECT *
FROM EMP , DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO ; 2 3
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 784 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 784 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 5 | 90 | 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("EMP"."DEPTNO"="DEPT"."DEPTNO")
SQL> SELECT /*+ NO_USE_HASH(EMP DEPT) */ *
FROM EMP , DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO ; 2 3
Execution Plan
----------------------------------------------------------
Plan hash value: 1407029907
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 784 | 8 (25)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 784 | 8 (25)| 00:00:01 |
| 2 | SORT JOIN | | 5 | 90 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 5 | 90 | 3 (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("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
NO_USE_MERGE
SQL> SELECT /*+ NO_USE_HASH(EMP DEPT) NO_USE_MERGE(EMP DEPT) */ *
FROM EMP , DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO ; 2 3
Execution Plan
----------------------------------------------------------
Plan hash value: 4192419542
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 784 | 10 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 784 | 10 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 5 | 90 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 3 | 114 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
NO_USE_NL
SQL> select *
from dao_objects1 d1, dao_objects d2
where d1.object_id = d2.object_id
and rownum =1 ; 2 3 4
Execution Plan
----------------------------------------------------------
Plan hash value: 3282424980
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 304 | 7 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 3 | 912 | 7 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | DAO_OBJECTS | 3 | 291 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_OBJ_ID_1 | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECTS1 | 1 | 207 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
5 - access("D1"."OBJECT_ID"="D2"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> select /*+ no_use_nl(d1 d2) */ *
from dao_objects1 d1, dao_objects d2
where d1.object_id = d2.object_id
and rownum =1 ; 2 3 4
Execution Plan
----------------------------------------------------------
Plan hash value: 1706612812
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 511 | | 1471 (1)| 00:00:18 |
|* 1 | COUNT STOPKEY | | | | | | |
|* 2 | HASH JOIN | | 74819 | 36M| 8040K| 1471 (1)| 00:00:18 |
| 3 | TABLE ACCESS FULL| DAO_OBJECTS | 75483 | 7150K| | 302 (1)| 00:00:04 |
| 4 | TABLE ACCESS FULL| DAO_OBJECTS1 | 74819 | 14M| | 3 (34)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
2 - access("D1"."OBJECT_ID"="D2"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
HASH_SJ
SQL> select *
from dao_objects1
where object_id in (select
deptno
from emp);
2 3 4 5
Execution Plan
----------------------------------------------------------
Plan hash value: 232411586
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 630 | 10 (10)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 3 | 630 | 10 (10)| 00:00:01 |
| 3 | SORT UNIQUE | | 14 | 42 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_OBJ_ID_1 | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECTS1 | 1 | 207 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("OBJECT_ID"="DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> select *
from dao_objects1
where object_id in (select /*+ hash_sj */
deptno
from emp);
2 3 4 5
Execution Plan
----------------------------------------------------------
Plan hash value: 70541257
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 630 | 305 (1)| 00:00:04 |
|* 1 | HASH JOIN RIGHT SEMI| | 3 | 630 | 305 (1)| 00:00:04 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DAO_OBJECTS1 | 74819 | 14M| 301 (1)| 00:00:04 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"="DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
NL_SJ
SQL> select *
from emp
where deptno in (select deptno
from dept) ; 2 3 4
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 | 5 | 15 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="DEPTNO")
SQL> select *
from emp
where deptno in (select /*+ nl_sj */ deptno
from dept) 2 3 4 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2069258075
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 574 | 20 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 14 | 574 | 20 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| DEPT | 5 | 15 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DEPTNO"="DEPTNO")
MERGE_SJ
SQL> select *
from emp
where deptno in (select deptno
from dept) ; 2 3 4
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 | 5 | 15 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="DEPTNO")
SQL> select *
from emp
where deptno in (select /*+ merge_sj */
deptno
from dept); 2 3 4 5
Execution Plan
----------------------------------------------------------
Plan hash value: 492114260
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 574 | 8 (25)| 00:00:01 |
| 1 | MERGE JOIN SEMI | | 14 | 574 | 8 (25)| 00:00:01 |
| 2 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 5 | 15 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| DEPT | 5 | 15 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPTNO"="DEPTNO")
filter("DEPTNO"="DEPTNO")
HASH_AJ
SQL> select *
from emp e
where not exists
(select object_id
from dao_objects1 d1
where e.empno = d1.object_id); 2 3 4 5 6
Execution Plan
----------------------------------------------------------
Plan hash value: 87989786
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 714 | 17 (0)| 00:00:01 |
| 1 | NESTED LOOPS ANTI | | 14 | 714 | 17 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_OBJ_ID_1 | 1 | 13 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."EMPNO"="D1"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> select *
from emp e
where not exists
(select /*+ hash_aj */ object_id
from dao_objects1 d1
where e.empno = d1.object_id); 2 3 4 5 6
Execution Plan
----------------------------------------------------------
Plan hash value: 300146118
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 714 | 55 (2)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 14 | 714 | 55 (2)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| IDX_OBJ_ID_1 | 74819 | 949K| 51 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."EMPNO"="D1"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
NL_AJ
SQL> select *
from dept
where not exists (select 1
from emp
where dept.deptno = emp.empno); 2 3 4 5
Execution Plan
----------------------------------------------------------
Plan hash value: 474461924
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 110 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 5 | 110 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 5 | 90 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 56 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPT"."DEPTNO"="EMP"."EMPNO")
SQL> select *
from dept
where not exists (select /*+ nl_aj */ 1
from emp
where dept.deptno = emp.empno);
2 3 4 5
Execution Plan
----------------------------------------------------------
Plan hash value: 1522491139
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 110 | 10 (0)| 00:00:01 |
| 1 | NESTED LOOPS ANTI | | 5 | 110 | 10 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 5 | 90 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DEPT"."DEPTNO"="EMP"."EMPNO")
MERGE_AJ
SQL> select *
from emp e
where not exists
(select object_id
from dao_objects1 d1
where e.empno = d1.object_id); 2 3 4 5 6
Execution Plan
----------------------------------------------------------
Plan hash value: 87989786
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 714 | 17 (0)| 00:00:01 |
| 1 | NESTED LOOPS ANTI | | 14 | 714 | 17 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_OBJ_ID_1 | 1 | 13 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."EMPNO"="D1"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> select *
from emp e
where not exists
(select /*+ MERGE_AJ */ object_id
from dao_objects1 d1
where e.empno = d1.object_id); 2 3 4 5 6
Execution Plan
----------------------------------------------------------
Plan hash value: 919708645
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 714 | | 415 (2)| 00:00:05 |
| 1 | MERGE JOIN ANTI | | 14 | 714 | | 415 (2)| 00:00:05 |
| 2 | SORT JOIN | | 14 | 532 | | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 532 | | 3 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 74819 | 949K| 2952K| 411 (1)| 00:00:05 |
| 5 | INDEX FAST FULL SCAN| IDX_OBJ_ID_1 | 74819 | 949K| | 51 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."EMPNO"="D1"."OBJECT_ID")
filter("E"."EMPNO"="D1"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
关联顺序
ordered
指定多表关联的关联顺序
SQL> select *
from dept d ,dao_objects1 d1 ,emp e
where e.deptno = d.deptno
and e.empno = d1.object_id ; 2 3 4
Execution Plan
----------------------------------------------------------
Plan hash value: 4244767586
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 3682 | 35 (3)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 14 | 3682 | 35 (3)| 00:00:01 |
|* 3 | HASH JOIN | | 14 | 784 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL | DEPT | 5 | 90 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_OBJ_ID_1 | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| DAO_OBJECTS1 | 1 | 207 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."DEPTNO"="D"."DEPTNO")
6 - access("E"."EMPNO"="D1"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> select /*+ ordered */ *
from dept d ,dao_objects1 d1 ,emp e
where e.deptno = d.deptno
and e.empno = d1.object_id ; 2 3 4
Execution Plan
----------------------------------------------------------
Plan hash value: 2457440359
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 3682 | 1508 (1)| 00:00:19 |
|* 1 | HASH JOIN | | 14 | 3682 | 1508 (1)| 00:00:19 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN| | 374K| 80M| 1503 (1)| 00:00:19 |
| 4 | TABLE ACCESS FULL | DEPT | 5 | 90 | 3 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 74819 | 14M| 1500 (1)| 00:00:18 |
| 6 | TABLE ACCESS FULL | DAO_OBJECTS1 | 74819 | 14M| 300 (1)| 00:00:04 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO" AND "E"."EMPNO"="D1"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
leading
指定表成为第一次关联中的驱动表
SQL> select /*+ leading( d1) */ *
from dept d ,dao_objects1 d1 ,emp e
where e.deptno = d.deptno
and e.empno = d1.object_id ; 2 3 4
Execution Plan
----------------------------------------------------------
Plan hash value: 3156926386
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 3682 | | 1085 (1)| 00:00:14 |
|* 1 | HASH JOIN | | 14 | 3682 | | 1085 (1)| 00:00:14 |
|* 2 | HASH JOIN | | 14 | 3430 | 15M| 1081 (1)| 00:00:13 |
| 3 | TABLE ACCESS FULL| DAO_OBJECTS1 | 74819 | 14M| | 301 (1)| 00:00:04 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 532 | | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | DEPT | 5 | 90 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
2 - access("E"."EMPNO"="D1"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)