composite partition key or subpartitioning

  • From: Grzegorz Goryszewski <grzegorzof@xxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 24 Nov 2011 19:02:12 +0100

 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
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
looks simple :)

other is
partition by time_id and subpartition by application_id lists partitioning .
seems more obvious and by book

My question is, which approach is better from, performance/maintanance
point of view ?


Other related posts: