Dear Experts,
Please help me in this question.
https://community.oracle.com/thread/3511578
I am trying to get count for the records in one partition of a table. oracle database version 11.2.0
Table is of size 700GB.
Table is partitioned on COVERED_LINE_END_DATE using range.
I want my query to be run on particular partition
I have created local partition index on COVERED_LINE_END_DATE
I want to restrict my queries to single partition and then I want to use index to get the fast results.
Currently my queries is taking hours, and same table table without partition performs well.
Please suggest me how to partition this table.
I always queries this table using date range that is quarterly.
so I partiton table quaterly. and then i want to index particular partition.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID a75mjmfsd873v
--------------------
select count(1) FROM X_3_PV_TD_LINE_ITEMS_FF_REL_2 A WHERE
A.COVERAGE = 'COVERED' AND A.SERIALIZED = 'Y' AND
A.COVERED_LINE_END_DATE BETWEEN sysdate-30 AND sysdate
Plan hash value: 3089225921
------
| Id | Operation | Name| Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT || | | 1425 (100)| | | | | | |
| 1 | SORT AGGREGATE || 1 | 21 | | | | | | | |
| 2 | PX COORDINATOR || | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000| 1 | 21 | | | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE || 1 | 21 | | | | | Q1,00 | PCWP | |
| 5 | FILTER || | | | | | | Q1,00 | PCWC | |
| 6 | PX PARTITION LIST ALL || 1307 | 27447 | 1425 (1)| 00:00:18 | 1 | 5 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID| X_3_PV_TD_LINE_ITEMS_FF_REL_2| 1307 | 27447 | 1425 (1)| 00:00:18 | KEY | KEY | Q1,00 | PCWP | |
| 8 | INDEX RANGE SCAN | X_3_COVERED_LINE_END_DATE_IDX| 5229 | | 209 (0)| 00:00:03 | KEY | KEY | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
22 rows selected.
Thanks in Advance for your help and time.