Re: Partition by three column values
- From: Deepak Sharma <sharmakdeep_oracle@xxxxxxxxx>
- To: Sandeep Dubey <dubey.sandeep@xxxxxxxxx>
- Date: Thu, 23 Feb 2006 09:33:42 -0800 (PST)
--- Sandeep Dubey <dubey.sandeep@xxxxxxxxx> wrote:
> 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@xxxxxxxxx> 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
__________________________________________________
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:
