The number of partitions in the table is exactly the same as the number of partitions in the index.
In the following example, you will create a local index on the CUST table you created earlier
in the chapter. Here is the SQL statement that retrieves the table partitions for the CUST table:
SQL> select partition_name, tablespace_name, high_value
2 from dba_tab_partitions
3 where table_name = 'CUST';
PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
-------------------- --------------- --------------------
SYS_P1130 PRD01
SYS_P1131 PRD02
SYS_P1132 PRD03
SYS_P1133 PRD04
4 rows selected.
FIGURE 16-1 Local partitioned index on a partitioned table
Chapter 16: Managing Large Databases 573
The command for creating the local index on this table is very straightforward, as you can see
in this example:
SQL> create index oe.cust_ins_dt_ix on oe.cust (ins_dt)
2 local store in (idx_1, idx_2, idx_3, idx_4);
Index created.
The index partitions are stored in four tablespaces??”IDX_1 through IDX_4??”to further improve
the performance of the table, because each index partition is stored in a tablespace separate from
any of the table partitions. You can find out about the partitions for this index by querying DBA_
IND_PARTITIONS, as follows:
SQL> select partition_name, tablespace_name from dba_ind_partitions
2 where index_name = 'CUST_INS_DT_IX';
PARTITION_NAME TABLESPACE_NAME
-------------------- ---------------
SYS_P1130 IDX_1
SYS_P1131 IDX_2
SYS_P1132 IDX_3
SYS_P1133 IDX_4
4 rows selected.
Pages:
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852