Creating Nonpartitioned Global Indexes Creating a nonpartitioned global index is the same
as creating a regular index on a nonpartitioned table; the syntax is identical. Figure 16-3 shows
the relationship between a nonpartitioned global index and a partitioned table.
Using Key Compression on Partitioned Indexes If your index is nonunique and has a large
number of repeating values for the index key or keys, you can use key compression on the index
just as you can with a traditional nonpartitioned index. When only the first instance of the index
key is stored, both disk space and I/O are reduced. In the following example, you can see how
easy it is to create a compressed partitioned index:
create index oe.cust_ins_dt_ix on oe.cust (ins_dt)
compress local
store in (idx_1, idx_2, idx_3, idx_4);
You can specify that a more active index partition not be compressed by using
NOCOMPRESS, which may save a noticeable amount of CPU for recent index entries that are
more frequently accessed than the others in the index.
Partitioned Index-Organized Tables
Index-organized tables (IOTs) can be partitioned using either the range, list, or hash partitioning
method; creating partitioned index-organized tables is syntactically similar to creating partitioned
heap organized tables. In this section, we??™ll cover some of the notable differences in how partitioned
IOTs are created and used.
FIGURE 16-3 Global nonpartitioned index on a partitioned table
576 Oracle Database 11g DBA Handbook
For a partitioned IOT, the organization index, including, and overflow clauses are used as
they are for standard IOTs.
Pages:
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855