Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
PL/SQL Release 11.2.0.3.0
IBM/AIX RISC System
Hello Experts,
I have a table with 14mill records. This table is partitioned on a coloum that has year. Following is the split.
2013: 809,400
2012: 3,555,430
2011: 3,555,670
2010: 3,296,890
2009: 3,330,860
Most of our ad-hoc queries hit one year partition (with no where clause). So its a full partition scan. Since I dont have a where clause I dont have any indexes. To increase performance, I use parallel. But still my query takes 3 min. Following is plan.
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 208 | 2920 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 13 | 208 | 2920 | | | Q1,01 | P->S | QC (RAND) |
| 3 | SORT GROUP BY | | 13 | 208 | 2920 | | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 13 | 208 | 2920 | | | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 13 | 208 | 2920 | | | Q1,00 | P->P | HASH |
| 6 | SORT GROUP BY | | 13 | 208 | 2920 | | | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 3553K| 54M| 2909 | 1 | 5 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL | Tab_year | 3553K| 54M| 2909 | 1 | 5 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------
Is there a way full partition scan perf be increased ?
Can do a sub partition based on HASH. Will that help the Parallel hint to fetch data faster ?
Are three any options where full partition scan or full table scan are handled in Oracle.
Thank you.
Aj