Re: Creating Partitioned Indexes on a Partitioned table

peter_at_peternolan.com wrote:
> Hi All,
> I now have a table with 16 partitions and lots of indexes.
>
> The field that defines which partition a row is in is OAP_EVT_TYPE_SRC.
> It can be 1-16.
>
> I am creating indexes as listed below. The way the manual reads is
> that I must create the index on the whole 'table' which means all 16
> partitions.

Correct.

> But I would have thought it was really reasonable to be
> able to create indexes only on those partitions which we really wanted
> those indexes on.

To me, the mere suggestion makes no sense. If you query partition A by column BLAH, then an index on BLAH for that partition makes sense. But if you query partition A by BLAH, then you surely also query partitions B and C by BLAH, too, and hence an index on that column for all partitions makes sense.

If you say to me that you query different partitions by different columns, then I would say your design was wrong. That doesn't sound like one table that has been partitioned, but as several different tables that have somehow been munged together into being one, poorly-behaving object.

In any case, whether I think it sensible or not, you can't do it. You create indexes on tables, and however many partitions you have, you have one table. Syntactically, if you do it to partition A, you do it to B, C and all the rest, too.

> Am I dreaming again, or is it possible in the example below to have
> just a few local indexes?
>
> Of course, I understand that I can create the tables as separate
> physical tables and then index just the tables I want....but I was
> rather hoping not to have to do that....One of the things we try and do
> is reduce the number of tables to make maintenance easier...

Wrong. A table should be what it relationally was meant to be: a set of related records. You don't create or not create tables to make your maintenance operations easier. You create as many tables as are needed to break records up into related, normalised, groupings.

Regards
HJR
>
> Thanks
>
> Peter Nolan
>
>
> CREATE UNIQUE INDEX PF_CORE_EVT_LINE_IXR41 ON F_CORE_EVT_LINE
> (OAP_EVT_TYPE_SRC, SOURCE_ID_OAP, SOURCE_TABLE, LINE_TYPE)
> LOCAL
> (
> PARTITION ix01 TABLESPACE TS_CEL_IDX01
> ,PARTITION ix02 TABLESPACE TS_CEL_IDX02
> ,PARTITION ix03 TABLESPACE TS_CEL_IDX03
> ,PARTITION ix04 TABLESPACE TS_CEL_IDX04
> ,PARTITION ix05 TABLESPACE TS_CEL_IDX05
> ,PARTITION ix06 TABLESPACE TS_CEL_IDX06
> ,PARTITION ix07 TABLESPACE TS_CEL_IDX07
> ,PARTITION ix08 TABLESPACE TS_CEL_IDX08
> ,PARTITION ix09 TABLESPACE TS_CEL_IDX09
> ,PARTITION ix10 TABLESPACE TS_CEL_IDX10
> ,PARTITION ix11 TABLESPACE TS_CEL_IDX11
> ,PARTITION ix12 TABLESPACE TS_CEL_IDX12
> ,PARTITION ix14 TABLESPACE TS_CEL_IDX14
> ,PARTITION ix15 TABLESPACE TS_CEL_IDX15
> ,PARTITION ix16 TABLESPACE TS_CEL_IDX16
> )
> NOLOGGING
> NOPARALLEL ;
>
Received on Thu Dec 02 2004 - 10:39:40 CST