| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Partition by three column values
> 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
>
Is this a STAR Schema's FACT table, or a regular (standalone) table. In case of 1st, a LOCAL Bitmap on should be created on FACT table's columns that reference Dimension table(s). If it is a standalone table, and, if Acct_Id would always exist in queries then creating LOCAL BTree Indexes would be a better option.
Thanks,
Deepak
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Feb 23 2006 - 11:33:42 CST
 |
 |