different explain plan
Hi,on 11.2.0.3 on Win 2008,
the same query has different EXplain Plan :
DBREC :
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 31 | 9334 (1)|
| 1 | LOAD TABLE CONVENTIONAL | PS_AP_BI_ACOMPTE_I | | | |
| 2 | VIEW | VM_NWVW_2 | 1 | 31 | 9334 (1)|
| 3 | SORT UNIQUE | | 1 | 157 | 9334 (1)|
|* 4 | FILTER | | | | |
| 5 | NESTED LOOPS | | 1 | 157 | 9325 (1)|
| 6 | NESTED LOOPS | | 1 | 102 | 9323 (1)|
|* 7 | HASH JOIN | | 1 | 66 | 9319 (1)|
|* 8 | TABLE ACCESS FULL | PS_PYMNT_VCHR_XREF | 3019 | 33209 | 4651 (1)|
| 9 | MERGE JOIN CARTESIAN | | 160K| 8613K| 4667 (1)|
|* 10 | INDEX RANGE SCAN | PS_AP_FACT_I | 1 | 21 | 3 (0)|
| 11 | BUFFER SORT | | 320K| 10M| 4664 (1)|
| 12 | TABLE ACCESS FULL | PS_PYMNT_VCHR_XREF | 320K| 10M| 4664 (1)|
| 13 | SORT UNIQUE | | 1 | 36 | 4 (0)|
| 14 | TABLE ACCESS BY INDEX ROWID| PS_PYMNT_VCHR_XREF | 1 | 36 | 4 (0)|
|* 15 | INDEX SKIP SCAN | PSFPYMNT_VCHR_XREF | 1 | | 3 (0)|
| 16 | TABLE ACCESS BY INDEX ROWID | PS_PAYMENT_TBL | 1 | 55 | 2 (0)|
the same query has different EXplain Plan :
DBREC :
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 31 | 9334 (1)|
| 1 | LOAD TABLE CONVENTIONAL | PS_AP_BI_ACOMPTE_I | | | |
| 2 | VIEW | VM_NWVW_2 | 1 | 31 | 9334 (1)|
| 3 | SORT UNIQUE | | 1 | 157 | 9334 (1)|
|* 4 | FILTER | | | | |
| 5 | NESTED LOOPS | | 1 | 157 | 9325 (1)|
| 6 | NESTED LOOPS | | 1 | 102 | 9323 (1)|
|* 7 | HASH JOIN | | 1 | 66 | 9319 (1)|
|* 8 | TABLE ACCESS FULL | PS_PYMNT_VCHR_XREF | 3019 | 33209 | 4651 (1)|
| 9 | MERGE JOIN CARTESIAN | | 160K| 8613K| 4667 (1)|
|* 10 | INDEX RANGE SCAN | PS_AP_FACT_I | 1 | 21 | 3 (0)|
| 11 | BUFFER SORT | | 320K| 10M| 4664 (1)|
| 12 | TABLE ACCESS FULL | PS_PYMNT_VCHR_XREF | 320K| 10M| 4664 (1)|
| 13 | SORT UNIQUE | | 1 | 36 | 4 (0)|
| 14 | TABLE ACCESS BY INDEX ROWID| PS_PYMNT_VCHR_XREF | 1 | 36 | 4 (0)|
|* 15 | INDEX SKIP SCAN | PSFPYMNT_VCHR_XREF | 1 | | 3 (0)|
| 16 | TABLE ACCESS BY INDEX ROWID | PS_PAYMENT_TBL | 1 | 55 | 2 (0)|
0