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

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 Wed Aug 25 2004 - 11:20:00 CDT