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: Sandeep Dubey <dubey.sandeep_at_gmail.com>
Date: Thu, 23 Feb 2006 12:06:27 -0500
Message-ID: <bf2f74740602230906h1622c218x4e85ef543aa6b2eb@mail.gmail.com>


Acct_id will always be in query but cr_date may not be in the query. Other column like NDC can be queried. There will be more columns that can be queried but I didn't mention in my example for sake of simplicity.

Any suggestion on type of index on columns like NDC local/global/bitmap. This is not OLTP database. Data will be loaded in batch.

Thanks

Sandeep

On 2/22/06, Deepak Sharma <sharmakdeep_oracle_at_yahoo.com> wrote:
> 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

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Feb 23 2006 - 11:06:27 CST

Original text of this message

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