Partitioned Indexes 2

[daddymack]

I am creating a table at the moment

(..
dl_field varchar2(10)
fl_field varchar2(10)
period varchar2(4)
..)

if i partition the table on the period field, and I then create indexes on dl_field and fl_field, should these indexes be partitioned also.

thanks in advance

Niall

 

[karluk]

You should at least think about partitioning these indexes. In fact, you have two viable partitioning strategies - local nonprefixed and global prefixed.

Let's take the index on dl_field as an example. If you create it as a local partitioned index, the partitions will be on the period column. Each index partition can have any value of dl_field, but only a single range of period, corresponding to the table partition. You can get performance benefits on certain types of queries (although other queries will take longer), and you will greatly simplify partition maintenance. For example, dropping a table partition will affect only a single index partition.

The other partitioning option is to create the dl_field index as a global index. That strategy bases the partitions on dl_field rather than period. You no longer have a correspondence between index and table partitions, but of course you expect to gain in performance on certain types of queries. A search for a single value of dl_field needs to check only one index partition. If the index is local nonprefixed, the same query needs to check all of the index partitions.

 

[BilliaUK]

Until 9i (when you can automatically rebuild global partitioned indexes following DDL on the parent partitioned table), I would strongly, no read, STRONGLY, advise you to use local partitioned indexes, UNLESS you are never going to either add,drop,merge,split partitions. If you do, you will render your ENTIRE global partitioned index as unusable and it will all need to be rebuilt. If the table is partitioned it is usually because a heap version is too unwieldy. Therefore, partitioning renders huge admin benefits, so having to rebuild global partitioned indexes is a pointless exercise.

Until 9i, use these rules of thumb (in fact in 9i I would still stick to them).

Partitioned table with ANY DDL that might render indexes unusable = local index

Partitioned table with NO DDL = possible use for global partitioned index

Very large non-partitioned table = might have a use for global partitioned index for performance gains

Other non-partitioned tables = stick to global non-partitioned indexes as usual.

Hope this helps. These recommendations come from a lot of experience and experimenting in this area.

Regards

Adrian

 

[daddymack]

Thanks guys. Very helpful posts. I should have given a little more info on this.

1. Queries will be more focussed on aggregates on individual periods. (i.e will not span period to period).
2. No DDL, except for the addition of new partitions and the dropping of old ones.

I am reckoning that local non-prefixed partitioning option is the optimal choice.

Thanks again,

Niall

 

[BilliaUK]

Niall

"No DDL except the dropping and adding of partitions" ? Well then that IS DDL. You MUST use local partitioned indexes else you will be spending a considerable amount of time rebuilding indexes every day/week/month (partition add and drop frequency).

Even in 9i, when you get the UPDATE GLOBAL INDEX clause to assist with this sort of stuff, local indexes are better - they are designed to do exactly what you want.

Regards

Adrian