こっちがサブプラン発動時(HASH JOINに変更された時)の実行計画
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('aqkqdv23rmnj7', NULL, 'ADAPTIVE ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
SQL_ID aqkqdv23rmnj7, child number 0
-------------------------------------
SELECT /*+ MONITOR */ DTL.* FROM SALES SAL ,
SALES_DETAIL DTL WHERE SAL.RECEIPT_NUM = DTL.RECEIPT_NUM AND
TO_CHAR(SAL.SALES_DATE, 'YYYYMMDD') = '20151101'
Plan hash value: 310527788
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.52 | 2982 | | | |
| * 1 | HASH JOIN | | 1 | 1 | 1 |00:00:00.52 | 2982 | 51M| 7163K| 69M (0)|
|- 2 | NESTED LOOPS | | 1 | 1 | 870K|00:00:00.31 | 2965 | | | |
|- 3 | NESTED LOOPS | | 1 | 1 | 870K|00:00:00.23 | 2965 | | | |
|- 4 | STATISTICS COLLECTOR | | 1 | | 870K|00:00:00.15 | 2965 | | | |
| 5 | TABLE ACCESS FULL | SALES_DETAIL | 1 | 1 | 870K|00:00:00.06 | 2965 | | | |
|- * 6 | INDEX UNIQUE SCAN | SALES_PK | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|- * 7 | TABLE ACCESS BY INDEX ROWID| SALES | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| * 8 | TABLE ACCESS FULL | SALES | 1 | 1 | 1 |00:00:00.01 | 17 | | | |
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("SAL"."RECEIPT_NUM"="DTL"."RECEIPT_NUM")
6 - access("SAL"."RECEIPT_NUM"="DTL"."RECEIPT_NUM")
7 - filter(TO_CHAR(INTERNAL_FUNCTION("SAL"."SALES_DATE"),'YYYYMMDD')='20151101')
8 - filter(TO_CHAR(INTERNAL_FUNCTION("SAL"."SALES_DATE"),'YYYYMMDD')='20151101')
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
Noteに「(rows marked '-' are inactive)」の記載が有る通り、
NESTED LOOPS のステップは動作しないんやね彡(゚)(゚)
そしてこっちがサブプランが発動しなかった時(NESTED LOOPSのまま)の実行計画
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('aqkqdv23rmnj7', NULL, 'ADAPTIVE ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
SQL_ID aqkqdv23rmnj7, child number 0
-------------------------------------
SELECT /*+ MONITOR */ DTL.* FROM SALES SAL ,
SALES_DETAIL DTL WHERE SAL.RECEIPT_NUM = DTL.RECEIPT_NUM AND
TO_CHAR(SAL.SALES_DATE, 'YYYYMMDD') = '20151101'
Plan hash value: 1292918998
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 22 |
|- * 1 | HASH JOIN | | 1 | 300 | 1 |00:00:00.01 | 22 |
| 2 | NESTED LOOPS | | 1 | 300 | 1 |00:00:00.01 | 22 |
| 3 | NESTED LOOPS | | 1 | 300 | 1 |00:00:00.01 | 21 |
|- 4 | STATISTICS COLLECTOR | | 1 | | 1 |00:00:00.01 | 17 |
| * 5 | TABLE ACCESS FULL | SALES | 1 | 1 | 1 |00:00:00.01 | 17 |
| * 6 | INDEX RANGE SCAN | SALES_DETAIL_I1 | 1 | 300 | 1 |00:00:00.01 | 4 |
| 7 | TABLE ACCESS BY INDEX ROWID| SALES_DETAIL | 1 | 300 | 1 |00:00:00.01 | 1 |
|- 8 | TABLE ACCESS FULL | SALES_DETAIL | 0 | 300 | 0 |00:00:00.01 | 0 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("SAL"."RECEIPT_NUM"="DTL"."RECEIPT_NUM")
5 - filter(TO_CHAR(INTERNAL_FUNCTION("SALES_DATE"),'YYYYMMDD')='20151101')
6 - access("SAL"."RECEIPT_NUM"="DTL"."RECEIPT_NUM")
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
33 rows selected.
動作しなかった HASH JOIN のとこに、
- がマークされてるやね彡(^)(^)