composite partition key or subpartitioning
From: Grzegorz Goryszewski <grzegorzof_at_interia.pl>
Date: Thu, 24 Nov 2011 19:02:12 +0100
Message-ID: <4ECE86A4.5070504_at_interia.pl>
Hi,
Im considering which partitioning option to choose but first some background .
DW database and a lot of tables partitioned by time_id which is number representing day number from some time , and second column which is application_id (fixed numbers like 1,2,3,4 no more than 10) .
Date: Thu, 24 Nov 2011 19:02:12 +0100
Message-ID: <4ECE86A4.5070504_at_interia.pl>
Hi,
Im considering which partitioning option to choose but first some background .
DW database and a lot of tables partitioned by time_id which is number representing day number from some time , and second column which is application_id (fixed numbers like 1,2,3,4 no more than 10) .
Queries are like that (simple joins with some predicates to get
partition pruning )
select * from
tab1 , tab2
where
tab1.id=tab2.id
and tab1.time_id = 123 and tab1.application_id in (1,2) .
So the ETL process is based on exchange partitions and regathering stats .
First solution is
partition by (time_id, application_id) --composite partition key
pros:
looks simple :)
other is
partition by time_id and subpartition by application_id lists partitioning .
pros:
seems more obvious and by book
My question is, which approach is better from, performance/maintanance
point of view ?
Regards
GregG
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Nov 24 2011 - 12:02:12 CST