The version is 9.2.0.4.0.
The "distinct" in the IN() select clause was a part of my testing and I
have run it with and without. Whoever put together SF_QUAL_PAYCODES had
put in duplicate values.
I know that stats are run nightly. The DBA would know more about the
details.
I've never used hints before but after a bit of Googling I tried the
following hints which gave better results:
/*+ HASH_SJ */
00:06:02.02
/*+ MERGE_SJ */
00:06:16.05
/*+ NL_SJ */
00:06:33.05
Anything else I should try? I don't have experience with manipulating
the CBO and analyzing plans. Any Oracle Education classes or online
resources you recommend?
Thanks to all for responding!
-=Cliff
Here's the plan:
Plan Table
| Operation | Name | Rows | Bytes| Cost |
Pstart| Pstop |
| SELECT STATEMENT | | 19K| 1M| 205 |
| |
| SORT GROUP BY | | 19K| 1M| 205 |
| |
| NESTED LOOPS | | 19K| 1M| 69 |
| |
| SORT UNIQUE | | | | |
| |
| TABLE ACCESS FULL |SF_QUAL_P | 59 | 826 | 2 |
| |
| VIEW |VP_ALLTOT | 329 | 20K| 1 |
| |
| UNION-ALL PARTITION | | | | |
| |
| HASH JOIN | | 112 | 17K| 11546 |
| |
| HASH JOIN | | 391 | 56K| 11418 |
| |
| TABLE ACCESS FULL |PAYPERIOD | 10K| 274K| 16 |
| |
| HASH JOIN | | 288K| 33M| 11345 |
| |
| TABLE ACCESS FULL |PAYPERIOD | 2K| 56K| 16 |
| |
| NESTED LOOPS | | 19K| 1M| 11327 |
| |
| HASH JOIN | | 19K| 1M| 11327 |
| |
| TABLE ACCESS BY |PAYCODE | 1 | 19 | 1 |
| |
| INDEX UNIQUE SC|XU1_PAYCO | 82 | | |
| |
| HASH JOIN | | 1M| 137M| 11321 |
| |
| TABLE ACCESS FU|PERSON | 15K| 201K| 45 |
| |
| HASH JOIN | | 1M| 114M| 11078 |
| |
| TABLE ACCESS F|PAYCODE1M | 315 | 7K| 2 |
| |
| HASH JOIN | | 4M| 185M| 11061 |
| |
| TABLE ACCESS |WTKEMPLOY | 15K| 217K| 25 |
| |
| TABLE ACCESS |WFCTOTAL | 4M| 120M| 10769 |
| |
| INDEX UNIQUE SCAN|PK_LABORA | 1 | 5 | |
| |
| INDEX FULL SCAN |XU2_PAYPE | 3K| 44K| 127 |
| |
| NESTED LOOPS | | 1 | 183 | 37 |
| |
| NESTED LOOPS | | 1 | 171 | 36 |
| |
| NESTED LOOPS | | 1 | 143 | 35 |
| |
| NESTED LOOPS | | 1 | 123 | 34 |
| |
| NESTED LOOPS | | 1 | 118 | 34 |
| |
| NESTED LOOPS | | 1 | 99 | 33 |
| |
| NESTED LOOPS | | 1 | 86 | 32 |
| |
| HASH JOIN | | 1 | 62 | 31 |
| |
| TABLE ACCESS |WTKEMPLOY | 15K| 217K| 25 |
| |
| TABLE ACCESS |SCHEDULED | 1 | 48 | 2 |
| |
| TABLE ACCESS B|PAYCODE1M | 1 | 24 | 1 |
| |
| INDEX RANGE S|PK_PAYCOD | 1 | | 1 |
| |
| TABLE ACCESS BY|PERSON | 1 | 13 | 1 |
| |
| INDEX UNIQUE S|PK_PERSON | 1 | | |
| |
| TABLE ACCESS BY |PAYCODE | 1 | 19 | 1 |
| |
| INDEX UNIQUE SC|PK_PAYCOD | 82 | | |
| |
| INDEX UNIQUE SCAN|PK_LABORA | 1 | 5 | |
| |
| INDEX RANGE SCAN |XU2_PAYPE | 15 | 300 | 1 |
| |
| INDEX RANGE SCAN |XU2_PAYPE | 1 | 28 | 1 |
| |
| INDEX RANGE SCAN |XU2_PAYPE | 1 | 12 | 1 |
| |
| NESTED LOOPS | | 1 | 183 | 37 |
| |
| NESTED LOOPS | | 1 | 171 | 36 |
| |
| NESTED LOOPS | | 1 | 143 | 35 |
| |
| NESTED LOOPS | | 1 | 123 | 34 |
| |
| NESTED LOOPS | | 1 | 118 | 34 |
| |
| NESTED LOOPS | | 1 | 99 | 33 |
| |
| NESTED LOOPS | | 1 | 86 | 32 |
| |
| HASH JOIN | | 1 | 62 | 31 |
| |
| TABLE ACCESS |WTKEMPLOY | 15K| 217K| 25 |
| |
| TABLE ACCESS |PROJECTED | 1 | 48 | 2 |
| |
| TABLE ACCESS B|PAYCODE1M | 1 | 24 | 1 |
| |
| INDEX RANGE S|PK_PAYCOD | 1 | | 1 |
| |
| TABLE ACCESS BY|PERSON | 1 | 13 | 1 |
| |
| INDEX UNIQUE S|PK_PERSON | 1 | | |
| |
| TABLE ACCESS BY |PAYCODE | 1 | 19 | 1 |
| |
| INDEX UNIQUE SC|PK_PAYCOD | 82 | | |
| |
| INDEX UNIQUE SCAN|PK_LABORA | 1 | 5 | |
| |
| INDEX RANGE SCAN |XU2_PAYPE | 15 | 300 | 1 |
| |
| INDEX RANGE SCAN |XU2_PAYPE | 1 | 28 | 1 |
| |
| INDEX RANGE SCAN |XU2_PAYPE | 1 | 12 | 1 |
| |
Received on Wed Aug 09 2006 - 12:27:39 CDT
