Re: Partition by three column values

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

• Original Message ----- From: "Sandeep Dubey" <dubey.sandeep_at_gmail.com> To: "Deepak Sharma" <sharmakdeep_oracle_at_yahoo.com> Cc: <oracle-l_at_freelists.org> Sent: Wednesday, February 22, 2006 7:23 PM Subject: Re: Partition by three column values

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