OraFAQ Forum: Performance Tuning » Partition table selfjoin for Time series

Home » RDBMS Server » Performance Tuning » Partition table selfjoin for Time series (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Partition table selfjoin for Time series [message #386981]

Tue, 17 February 2009 08:05

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

Report message to a moderator

Re: Partition table selfjoin for Time series [message #387096 is a reply to message #386981]

Tue, 17 February 2009 22:10

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

Report message to a moderator

Previous Topic:	Tune a select query which contains a pl/sql table type
Next Topic:	Hard Code values in sql vs lookup

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 17:59:57 CST 2024