Re: Partition by three column values

From: Deepak Sharma <sharmakdeep_oracle_at_yahoo.com>
Date: Wed, 22 Feb 2006 20:09:14 -0800 (PST)
Message-ID: <20060223040914.50430.qmail@web52810.mail.yahoo.com>

As Sandeep had mentioned, all the queries will definitely have acct_id. But he didn't mention whether cr_date will also 'always' be there. So, if we partition by cr_date and the query does not specify it, we wouldn't see a 'PARTITION RANGE ITERATOR', but we would still get a 'PARTITION LIST ITERATOR' for the sub-partitions on ACCT_ID (not as efficient as when table would have been partitioned on acct_id).

So, in the approach I had suggested (partition by acct_id and subpartition by ret_key):

We get partition elimination since acct_id is always there.
We address retention by clubing Site_id and Cr_date together.

In queries where acct_id and cr_date are mentioned, we could have a LOCAL index created on cr_date only. So, partition pruning would happen for acct_id, and then index-access would happen for cr_date condition. Same would hold true by creating a Local index for Site_Id.

For the above design, Best would be if the application could "ADD" a RET_KEY condition to the queries. I was thinking of FGAC, Table Function where you could add a predicate for RET_KEY (transparent to the user) to any query accessing this table, but that needs some testing.

Deepak

jaromir nemec <jaromir_at_db-nemec.com> wrote:

> 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.

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

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

Received on Wed Feb 22 2006 - 22:09:14 CST