Local index vs global index on partitioned table free
Global vs. local index performance on partitioned table All of my queries except for 1 leverage a finite number of partitions so I know that the local indexes will benefit them (as well as make the tables insanely easier to maintain since I won't have applciation downtime to rebuild my global indexes).
Global Partitioned Indexes. You can create global partitioned indexes on nonpartitioned and partitioned tables. In a global partitioned index, the keys in a particular index partition may refer to rows stored in multiple underlying table partitions or subpartitions. A global index can be range or hash partitioned, though it can be defined on
Global Index: A global index is a onetomany relationship, allowing one index partition to map to many table partitions. The docs says that a global index can be partitioned by the range or hash method, and it can be defined on any type of partitioned, or nonpartitioned, table .
Sep 17, 2009 Regarding global vs local partitions, I have seen timings indicate that global indexes can improve crosspartition queries slighly. I have not worked with partitions for a while but remember that global partition maintenance is difficult; anything the partition structure changes the global index had to be rebuild.
The partitioning key of a global partitioned index is different or specifies a different range of values from the partitioned table. The creator of the global partitioned index is responsible for defining the ranges and values for the partitioning key. Global indexes can only be btree indexes.
Global index partitions are used for OLTP applications where fewer index probes are required than with local partitioned indexes. For Global indexes the DBA has the choice to create the partitions as many as required. In Global partition scheme the index is harder to maintain since the index may span partitions in the base table.
Global partitioned indexes. A global partitioned index is used for all other indexes except for the one that is used as the table partition key. Global indexes partition OLTP (online transaction processing) applications where fewer index probes are required than with local partitioned indexes.
Rating: 4.81 / Views: 559