Search BC Sites

Free Oracle Tips   HTML Text

Free Oracle App Server Tips   HTML Text

Privacy Policy

Redneck

Dress Code

Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

Local Indexes

You can ensure that a table and its associated index are equi-partitioned by specifying the local parameter during index creation. All keys in a local index partition point to rows stored in a single table partition. When creating an index using the local parameter, range specifications and a maximum value do not need to be specified. Oracle automatically partitions the local index on the same partitioning key columns as the table it references, creates the same number of partitions and gives each index partition the same partitioning key boundaries.

Global Indexes

Unlike their local index counterparts, index keys in a global index may point to rows in more than one table partition (see Figure 4). To create a global index, you specify the GLOBAL parameter (default) during index creation. Global indexes can be equi-partitioned with their tables that they reference, but Oracle will not take advantage of equi-partitioning when generating query plans or executing maintenance operations. The entire index will be affected. To take advantage of Oracle’s equi-partitioned performance improvements, the index must be created as local.
 

For more details and scripts, see my new book " Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot.