Re: Possible? Index on partitioned table with index only being associated with one of the partitions...

Hi Greg,

As far as I know it's not possible to create an index on the individual partitions of a table. How often is data prior to 2004 queried? If it's not very often, then prehaps you could think about keeping the data for 2004 and 2005 in a seperate table and then write some dynamic SQL to exchange partitions prior to querying the entire data set.

Just a thought

Andy

gforestieri9_at_yahoo.com (Greg Forestieri) wrote in message news:<6a8cdd95.0408250820.5fa272f3_at_posting.google.com>...
> Possible? Index on partitioned table with index only being associated
> with one of the partitions...
>
> System
>
> Compaq Alpha 4100 True64 5x
>
> Oracle 9.2.0.4.0
>
> Background - we have a "datawarehouse" (I use the term loosely here)
> that has several multi-million row tables. The tables have been
> partitioned on year, so that the partitions look like this:
>
> YR1999 (all before EOY1999)
> YR2000
> YR2001
> YR2002
> YR2003
> YR2004
> YR2005
> YRDEFAULT (date mistakes end up here)
>
> Now the data is distributed across the years in a fairly even manner,
> i.e. 2004 has roughly 1/5 of the rows in the table.
>
> For the purpose of saving index space is it possible to build an index
> that only indexes the YR2004 partition? We realize that in such a
> situation *if possible* that retrieval speed on queries drawing data
> prior to 2004 could be quite slow. This would be expected and easily
> explained. Most queries are on current year data, but our tables are
> such that an index can easily use several GBs. I can find nothing
> that looks exactly like this in the doc and that may easily mean it's
> not possible or that I need to RTFM more closely.
>
> Any thoughts? I'd be happy to elaborate if helpful. My general role
> here is as developer / analyst / jr assistant DBA.
>
> Greg
Received on Tue Aug 31 2004 - 09:47:20 CDT