Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Partition by three column values

Re: Partition by three column values

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Wed, 22 Feb 2006 23:11:16 +0100
Message-ID: <032b01c637fc$e7615600$3c02a8c0@JARAWIN>


Sandeep,

a slightly modification of the design of Deepak is as follows

range partition by cr_date

subpartition by acct_id

this option is optimized for access; queries with equal condition on acct_it and cr_date prune to only one subpartition. Queries with interval of cr_date (between) prune to a subset of subpartitions. The trade off here is that you can only drop subpartition when all of the site_id in a particular acct_id are timed out. Prior that delete must be used.

This issue can be resolved using the concatenated key (acct_id + site_id) for subpartitioning (as proposed by Deepak) but the price is that you loose a great part of pruning described above.

You should therefore definitely consider the priorities of access vs. administration.

Regards,

Jaromir D.B. Nemec

Deepak,

Thanks for the response. Yes, your design looks promising and good solution to the partitioning. I am evaluating it. I am also evaluating should I have that many partitions - subpartitions or have a simple partition by acct_id and use parallel delete do purging. Any thoughts on this.

Thanks

Sandeep
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Wed Feb 22 2006 - 16:11:16 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US