Databases Reference
In-Depth Information
10 partition manager_600 values less than (600),
11 partition manager_700 values less than (700),
12 partition manager_800 values less than (800),
13 partition manager_900 values less than (900),
14* partition manager_max values less than (maxvalue));
partition by range(manager_id)
*
ERROR at line 4:
ORA-14038: GLOBAL partitioned index must be prefixed
The second type of globally partitioned that can be created is the hash partitioned index. This is
typically done for performance reasons and keeps the index more evenly spread between the partitions.
See the following example to create a hash-based globally partitioned index:
SQL> CREATE INDEX employees_ih1
2 ON employees (department_id)
3 GLOBAL
4 PARTITION BY HASH(department_id) partitions 4;
Index created.
■
Note
Globally partitioned indexes must be created on heap-organized tables. Also, you can't create globally
partitioned bitmap indexes.
Choosing the Type of Index for Your Application
Using locally partitioned indexes is the most popular implementation of indexes on partitioned tables,
especially in the data warehouse environment. One of the primary reasons is that locally partitioned
indexes reduce the amount of index partition maintenance necessary overall when you perform table-
level partition operations. Locally partitioned indexes are easy to create and much easier to maintain
than their counterparts, the non-partitioned index and the globally partitioned index. While their use
does reduce the amount of maintenance and time it takes to perform partition maintenance, using
locally partitioned indexes doesn't eliminate maintenance entirely. That said, implementing locally
partitioned indexes offer the strongest advantages over both globally partitioned indexes and non-
partitioned indexes.
One of the key drawbacks of globally partitioned indexes is that performing table-level operations
will generally make the entire globally partitioned index unusable. The same is true for non-partitioned
indexes that exist on partitioned tables—that is, any partition-level operations at the table level will mark
the entire non-partitioned index unusable.
If you have partitioned tables, it is best to implement a guideline to simply use locally partitioned
indexes on all partitioned tables. Of course, there are always exceptions to any rule or guideline. When
these exceptions occur, you simply need to weigh the advantages and disadvantages of implementing a
certain kind of index. This can vary greatly based on the type of application. For instance, the answer for
an OLTP system will greatly vary from a data warehouse system.
It's hard to find reasons not to use locally partitioned indexes, but the following are a couple of
possible reasons to use non-partitioned indexes or globally partitioned indexes:
