SQL> explain plan for select rowid rid from
2 scott.emp e where e.empno >100 and e.empno < 1000 and e.deptno in (select deptno from scott.dept where dname like 'a%');
已解释。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ADVANCED -PROJECTION'));
PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 1465018025 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 32 | 3 (0)| 00:00:01 | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 1 | 32 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 19 | 2 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | |* 6 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$5DA710D3 3 - SEL$5DA710D3 / E@SEL$1 4 - SEL$5DA710D3 / E@SEL$1 5 - SEL$5DA710D3 / DEPT@SEL$2 6 - SEL$5DA710D3 / DEPT@SEL$2 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA NLJ_BATCHING(@"SEL$5DA710D3" "DEPT"@"SEL$2") USE_NL(@"SEL$5DA710D3" "DEPT"@"SEL$2") LEADING(@"SEL$5DA710D3" "E"@"SEL$1" "DEPT"@"SEL$2") INDEX(@"SEL$5DA710D3" "DEPT"@"SEL$2" ("DEPT"."DEPTNO")) INDEX_RS_ASC(@"SEL$5DA710D3" "E"@"SEL$1" ("EMP"."EMPNO")) OUTLINE(@"SEL$2") OUTLINE(@"SEL$1") UNNEST(@"SEL$2") OUTLINE_LEAF(@"SEL$5DA710D3") ALL_ROWS DB_VERSION('11.2.0.1') OPTIMIZER_FEATURES_ENABLE('11.2.0.1') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("E"."EMPNO">100 AND "E"."EMPNO"<1000) 5 - access("E"."DEPTNO"="DEPTNO") 6 - filter("DNAME" LIKE 'a%')
已选择50行。
SQL> explain plan for select /*+leading(DEPT@SEL$2)*/rowid rid from
2 scott.emp e where e.empno >100 and e.empno < 1000 and e.deptno in (select deptno from scott.dept where dname like 'a%');
已解释。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ADVANCED -PROJECTION'));
PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 817787759 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 32 | 5 (20)| 00:00:01 | | 1 | MERGE JOIN | | 1 | 32 | 5 (20)| 00:00:01 | |* 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 | 2 (0)| 00:00:01 | | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | |* 4 | SORT JOIN | | 1 | 19 | 3 (34)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 19 | 2 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$5DA710D3 2 - SEL$5DA710D3 / DEPT@SEL$2 3 - SEL$5DA710D3 / DEPT@SEL$2 5 - SEL$5DA710D3 / E@SEL$1 6 - SEL$5DA710D3 / E@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA USE_MERGE(@"SEL$5DA710D3" "E"@"SEL$1") LEADING(@"SEL$5DA710D3" "DEPT"@"SEL$2" "E"@"SEL$1") INDEX_RS_ASC(@"SEL$5DA710D3" "E"@"SEL$1" ("EMP"."EMPNO")) INDEX(@"SEL$5DA710D3" "DEPT"@"SEL$2" ("DEPT"."DEPTNO")) OUTLINE(@"SEL$2") OUTLINE(@"SEL$1") UNNEST(@"SEL$2") OUTLINE_LEAF(@"SEL$5DA710D3") ALL_ROWS DB_VERSION('11.2.0.1') OPTIMIZER_FEATURES_ENABLE('11.2.0.1') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("DNAME" LIKE 'a%') 4 - access("E"."DEPTNO"="DEPTNO") filter("E"."DEPTNO"="DEPTNO") 6 - access("E"."EMPNO">100 AND "E"."EMPNO"<1000) 已选择50行。
SQL> explain plan for select /*+leading(DEPT)*/rowid rid from
2 scott.emp e where e.empno >100 and e.empno < 1000 and e.deptno in (select deptno from scott.dept where dname like 'a%');
已解释。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ADVANCED -PROJECTION'));
PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 817787759 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 32 | 5 (20)| 00:00:01 | | 1 | MERGE JOIN | | 1 | 32 | 5 (20)| 00:00:01 | |* 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 | 2 (0)| 00:00:01 | | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | |* 4 | SORT JOIN | | 1 | 19 | 3 (34)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 19 | 2 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$5DA710D3 2 - SEL$5DA710D3 / DEPT@SEL$2 3 - SEL$5DA710D3 / DEPT@SEL$2 5 - SEL$5DA710D3 / E@SEL$1 6 - SEL$5DA710D3 / E@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA USE_MERGE(@"SEL$5DA710D3" "E"@"SEL$1") LEADING(@"SEL$5DA710D3" "DEPT"@"SEL$2" "E"@"SEL$1") INDEX_RS_ASC(@"SEL$5DA710D3" "E"@"SEL$1" ("EMP"."EMPNO")) INDEX(@"SEL$5DA710D3" "DEPT"@"SEL$2" ("DEPT"."DEPTNO")) OUTLINE(@"SEL$2") OUTLINE(@"SEL$1") UNNEST(@"SEL$2") OUTLINE_LEAF(@"SEL$5DA710D3") ALL_ROWS DB_VERSION('11.2.0.1') OPTIMIZER_FEATURES_ENABLE('11.2.0.1') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("DNAME" LIKE 'a%') 4 - access("E"."DEPTNO"="DEPTNO") filter("E"."DEPTNO"="DEPTNO") 6 - access("E"."EMPNO">100 AND "E"."EMPNO"<1000) 已选择50行。