Home » RDBMS Server » Performance Tuning » Partition table selfjoin for Time series (oracle 10g)
Partition table selfjoin for Time series [message #386981] |
Tue, 17 February 2009 08:05 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
test2day
Messages: 1 Registered: February 2009
|
Junior Member |
|
|
Partition table selfjoin for Time series
What is the best way to index a partitioned table which is partitioned by date
cycle_eom_date when doing time series
pk is on cycle_eom_date , ACCOUNT_ID - ACCOUNT_ID is unique every month
sample data
cycle_eom_date ACCOUNT_ID
01/31/2009 11500
01/31/2009 11521
01/31/2009 11526
01/31/2009 11527
01/31/2009 11528
example of query
SELECT a.account_id, a.cycle_eom_date AS t_start, b.cycle_eom_date,
CASE
WHEN b.delqlscycles < 2
THEN 0
ELSE b.delqlscycles - 1
END AS delq_reg
FROM userqry.vw_riskonus a,
userqry.vw_riskonus b
WHERE
a.account_id = b.account_id
AND a.cycle_eom_date BETWEEN add_months(a.cycle_eom_date ,- 11) AND add_months(b.cycle_eom_date, - 1)
AND b.cycle_eom_date > '31-dec-2007';
there is an index on (account_id and cycle_eom_Date) however the explain plan is scanning the entire partitioned table.
see below. How can I eliminate the full scan of the table?
Plan
SELECT STATEMENT ALL_ROWSCost: 23,265 Bytes: 45,708,901 Cardinality: 1,576,169
13 PX COORDINATOR
12 PX SEND QC (RANDOM) PARALLEL_TO_SERIAL SYS.:TQ10002 Cost: 23,265 Bytes: 45,708,901 Cardinality: 1,576,169
11 HASH JOIN BUFFERED PARALLEL_COMBINED_WITH_PARENT Cost: 23,265 Bytes: 45,708,901 Cardinality: 1,576,169
5 PX JOIN FILTER CREATE PARALLEL_COMBINED_WITH_PARENT SYS.:BF0000 Cost: 7,010 Bytes: 53,761,630 Cardinality: 4,135,510
4 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT Cost: 7,010 Bytes: 53,761,630 Cardinality: 4,135,510
3 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10000 Cost: 7,010 Bytes: 53,761,630 Cardinality: 4,135,510
2 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD Cost: 7,010 Bytes: 53,761,630 Cardinality: 4,135,510
1 INDEX FAST FULL SCAN INDEX PARALLEL_COMBINED_WITH_PARENT RISKONUS.IDX_RISKONUS_CYCACCTV9 Cost: 7,010 Bytes: 53,761,630 Cardinality: 4,135,510
10 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT Cost: 16,187 Bytes: 178,959,616 Cardinality: 11,184,976
9 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10001 Cost: 16,187 Bytes: 178,959,616 Cardinality: 11,184,976
8 PX JOIN FILTER USE PARALLEL_COMBINED_WITH_PARENT SYS.:BF0000 Cost: 16,187 Bytes: 178,959,616 Cardinality: 11,184,976
7 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD Cost: 16,187 Bytes: 178,959,616 Cardinality: 11,184,976
6 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT RISKONUS.TBL_RISKONUS Cost: 16,187 Bytes: 178,959,616 Cardinality: 11,184,976
|
|
|
Re: Partition table selfjoin for Time series [message #387096 is a reply to message #386981] |
Tue, 17 February 2009 22:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Since you are performing a range predicate on the date (BETWEEN), you cannot use it as the leading column of a composite index.
You could index either:
-Unique Local (Account, Date)
-Non Unique Local (Account)
They would probably perform about the same. The first one is probably a little safer, but will use more space.
Ross Leishman
|
|
|
Goto Forum:
Current Time: Sat Sep 07 18:40:56 CDT 2024
|