What is Local Index and Global Index in Partition Table? What is Prefix Index and Nonprefix Index in table?

 A:

When you create a partitioned table you should create an index on the table. The index may be partitioned according to the same range values that were used to partition the table.Local keyword in the index partitiong tells oracle to create a separate index for each partition of the table.The Global clause in create index command allows you to create a non-partitioned index or to specify ranges for the index values that are different from the ranges for the table paratitions.Local indexes may be easier to manage than global indexes however global indexes may perfrom uniqueness checks faster than local(partioned) indexes perform them

 

B:

it is about data partitioning. When you have partitioned table you may build
local or global index on it. Local index is partitioned in such a way that
have the same quantity of partitions (1:1) and indexes for rows of one table
partition stored in one and only one index partition. In this case operation
on particular table partition will invalidate only one partition of index
leaving others pretty useful. On the opposite, global index on partitioned
table is one segment and any physical transformation on any table partition
will invalidate the whole index, making index/table unusable for queries
accessing the index. Generally speaking global indexing almost kills
administration benefits from table partitioning. Local indexing also helps
in parallel query/execution.

From my point of view global indexing should be avoided if you are going

fully benefit from data partitioning.