Re: Partition by three column values
- From: Deepak Sharma <sharmakdeep_oracle@xxxxxxxxx>
- To: jaromir@xxxxxxxxxxxx, dubey.sandeep@xxxxxxxxx
- Date: Wed, 22 Feb 2006 20:09:14 -0800 (PST)
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):
1) We get partition elimination since acct_id is
always there.
2) 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@xxxxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
