explain plan for select /*+leading (T6) use_nl( T1 T6)*/ * from T_PM_ACCT_DTL_AF T1,(SELECT
*
FROM S_PM_MGR_DEPT_RELA A
WHERE DEPT1_CODE <> '999999999') T6
where T1.MGR_CODE = T6.MGR_CODE
and t1.data_date>20130101;
select * from table(dbms_xplan.display());
Plan hash value: 3887218478
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1173M| 358G| 52G (2)|999:59:59 | | |
| 1 | NESTED LOOPS | | 1173M| 358G| 52G (2)|999:59:59 | | |
|* 2 | TABLE ACCESS FULL | S_PM_MGR_DEPT_RELA | 6303 | 683K| 45 (0)| 00:00:01 | | |
| 3 | PARTITION LIST ITERATOR| | 186K| 38M| 8354K (2)| 27:50:58 | KEY | KEY |
|* 4 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 186K| 38M| 8354K (2)| 27:50:58 | KEY | KEY |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPT1_CODE"<>'999999999')
4 - filter("T1"."MGR_CODE"="A"."MGR_CODE")
key key Oracle不知道要扫描那些分区,动态分区裁剪
explain plan for select * from T_PM_ACCT_DTL_AF t1
where t1.data_date='20130101';
select * from table(dbms_xplan.display());
Plan hash value: 1825735905
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1312K| 267M| 9232 (2)| 00:01:51 | | |
| 1 | PARTITION LIST SINGLE| | 1312K| 267M| 9232 (2)| 00:01:51 | KEY | KEY |
| 2 | TABLE ACCESS FULL | T_PM_ACCT_DTL_AF | 1312K| 267M| 9232 (2)| 00:01:51 | 368 | 368 |
----------------------------------------------------------------------------------------------------------
