Oracle Partition Index

A partitioned index (or index partition) is primarily for a partitioned table. As the amount of data grows, the normal heap table needs to be converted to the partition table, and its index is converted to the partition index. The benefits of partitioned indexes are obvious. is simply to divide an index into multiple fragments, and when you get the data you want, you only need to access smaller index fragments (blocks) to achieve. Partitioning the partitions in different tablespaces can improve the availability and reliability of the partitions. This article mainly describes the relevant characteristics of partitioned indexes and gives a demonstration example.

1. Related Concepts of partitioned indexes
A, the partition index several ways: The table is partitioned and the index is not partitioned, the table is not partitioned, the index is partitioned, and the tables and indexes are partitioned
b, the partition index can be divided into local partition index and global partition index
Local Partition index:
The storage of local partition index information depends on the parent table partition. This means that the local index must be created from a partitioned table.
By default, when you create a local index, if you do not specify an index to hold the tablespace, the local index is automatically placed in the tablespace that is defined by the partition where the data resides.
The local index can be either a B-tree index or a bitmap index, as is the partitioning mechanism of the table.
The local index is for a single partition, and each partition index points to only one table partition, which is the peer partition.
Local indexes support partition independence, so for these separate partitions, the addition, interception, deletion, splitting, and offline processing do not need to be deleted or rebuilt at the same time.
Local indexes are more applied to a data warehouse environment.

Global Partition Index:
The partitioning mechanism of a partitioned table and a global index is different when the global Partition index is created, and the range and value of the partition key must be defined at creation time.
The global partition index should be created with the Global keyword and the global partition index only a B-tree index.
The global index can be partitioned, or it can be a non-partitioned index, and the global index must be a prefix index, that is, the index column must contain a partition key.
In a global index partition, a partitioned index can point to n table partitions, while a table partition may also point to n index partitions.
By default, global indexes must be rebuilt or modified when specifying update global INDEXS for partition additions, interception, deletion, and partitioning.
The global Partition index is partitioned only by range or hash hash.
Global partitioned indexes are more widely used in OLTP systems.

C, prefixed index and no prefix index
The local and global partition indexes are divided into two sub-types, namely prefix index and no prefix index.
Both the prefix and the non-prefixed index can support index partition elimination, provided that the criteria for the query contains the index partition key.
There is a prefix index:
A prefix index contains a partition key, that is, the partition key column is included in the index.
There is a prefix index that supports both the local partition index and the global Partition index.

No prefix index:
A prefix index does not have the leading column of the partition key as the leading column of the index.
The prefix index only supports local partition indexes.

2. Local Partition Index Demo

--Environment sql> SELECT * from V$version where rownum<2; BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-64bisql> Create user Leshami identified by xxx; Sql> Grant DBA to leshami;--create a table space for the demo sql> create tablespace tbs_tmp datafile '/u02/database/sybo2/oradata/tbs_ tmp.dbf ' size 10m autoextend on; sql> alter user Leshami default Tablespace tbs_tmp; sql> Create tablespace tbs1 datafile '/u02/database/sybo2/oradata/tbs1.dbf ' size 10m autoextend on; sql> Create tablespace tbs2 datafile '/u02/database/sybo2/oradata/tbs2.dbf ' size 10m autoextend on; sql> Create tablespace tbs3 datafile '/u02/database/sybo2/oradata/tbs3.dbf ' size 10m autoextend on; sql> Create tablespace idx1 datafile '/u02/database/sybo2/oradata/idx1.dbf ' size 10m autoextend on; sql> Create tablespace idx2 datafile '/u02/database/sybo2/oradata/idx2.dbf ' size 10m autoextend on; sql> Create tablespace idx3 datafile '/u02/database/sybo2/oradata/idx3.dbf ' size 10m autoextend on; Sql> Conn leshami/xxx--Creating a Lookup Table CREATE table lookup (ID number, description VARCHAR2 (50));--Add primary key Constraint alter TABLE lookup ADD (CONSTRAINT lookup_pk PRIMARY KEY (ID));--insert data inserts into lookup (ID, description) VALUES (1, ' One '); INSERT into lookup (ID, description) VALUES (2, ' both '); INSERT into lookup (ID, description) VALUES (3, ' three '); COMM IT; CREATE TABLE big_table (ID number), created_date date, lookup_id number, data VARCHAR 2 ()) PARTITION by RANGE (created_date) (PARTITION big_table_2012 VALUES less THAN (to_date (' 01/01/2013 ', ' dd/mm/yyyy ')) Tablespace tbs1, PARTITION big_table_2013 VALUES less THAN (to_date (' 01/01/2014 ', ' dd/mm/yyyy ')) tablespace TBS2, Partiti On big_table_2014 VALUES less THAN (MAXVALUE) tablespace tbs3);  --Fill data to partition table declare l_lookup_id lookup.id%type; L_create_date date; BEGIN for I in 1.. 10000 LOOP IF MOD (i, 3) = 0 Then l_create_date: = ADD_months (Sysdate,-24);    L_LOOKUP_ID: = 2;      Elsif MOD (i, 2) = 0 Then l_create_date: = Add_months (Sysdate,-12);    l_lookup_id: = 1;      ELSE l_create_date: = sysdate;    L_LOOKUP_ID: = 3;        END IF;  INSERT into big_table (ID, created_date, lookup_id, data) VALUES (i, L_create_date, l_lookup_id, ' This is some data for ' | |  i);  END LOOP; COMMIT; end;/--Create an index without specifying an index partition and storage tablespace sql> Create, Index bita_created_date_i on big_table (created_date) Local;index created. Sql> Select Index_name, Partitioning_type, partition_count from User_part_indexes;index_name PARTIT                 I partition_count----------------------------------------------------bita_created_date_i RANGE 3--author:leshami--blog:http://blog.csdn.net/leshami--from the following query, the index is stored directly in the table space corresponding to the table sql> select Partition_name, hig H_value, tablespace_name from User_ind_partitions; Partition_name High_value Tablespace_namE----------------------------------------------------------------------------------------------------big_table _2014 MAXVALUE tbs3big_table_2013 to_date (' 2014-01-01 00:                 00:00 ', ' syyyy-m TBS2 m-dd HH24:MI:SS ', ' nls_calendar=gregoriabig_table_2012 To_date (' 2013-01-01 00:00:00 ', ' syyyy-m TBS1 m-dd HH24:MI:SS ', ' nls_calendar=gregoria--delete cable sql> DROP Index bita_created_date_i;--Specify an index partition name table space name Create an index sql> creating an index Bita_created_date_i 2 on big_table (CRE        Ated_date) 3 LOCAL (4 PARTITION idx_2012 tablespace idx1, 5 PARTITION idx_2013 tablespace idx2, 6 PARTITION idx_2014 tablespace idx3) 7 PARALLEL 3;index created. Sql> Select Partition_name, High_value, tablespace_name from User_ind_partitions; Partition_name high_value tablespace_name----------------------------------------------------------------------------------------------------idx_2014 MAXVALUE                               idx3idx_2013 to_date (' 2014-01-01 00:00:00 ', ' syyyy-m IDX2 M-dd HH24:MI:SS ', ' nls_calendar=gregoriaidx_2012 to_date (' 2013-01-01 00:00:00 ', ' syyyy-m ID X1 m-dd HH24:MI:SS ', ' nls_calendar=gregoriasql> select * from big_table where rownum<2        ; ID created_ lookup_id DATA------------------------------------------------------------------------------1413 201 20625 2 This is some the data for 1413--to see if the local index is being used, from the following execution plan, the index is used to support partition elimination sql> set Autot trace exp; Sql> SELECT * from big_table where created_date=to_date (' 20120625 ', ' yyyymmdd '); Execution Plan----------------------------------------------------------plan hash value:2556877094--------------- -----------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |---------------------------------------------------------------------------------------------------------   -----------------| 0 |                     SELECT STATEMENT |     |    1 |     41 | 2 (0) |       00:00:01 |       |   ||  1 |                     PARTITION RANGE Single |     |    1 |     41 | 2 (0) |     00:00:01 |     1 |   1 | |   2 | TABLE ACCESS by LOCAL INDEX rowid|     big_table |    1 |     41 | 2 (0) |     00:00:01 |     1 | 1 | |    * 3 | INDEX RANGE SCAN |     bita_created_date_i |       1 |     | 1 (0) |     00:00:01 |     1 | 1 |------------------------------------------------------------------------------------------------------------- -------------

--Add primary key for table sql> ALTER table big_table Add (2 CONSTRAINT big_table_pk PRIMARY key (ID) 3);       Table altered. Sql> Select index_name,index_type,tablespace_name,global_stats,partitioned 2 from user_indexes where Index_name= ' BIG_TABLE_PK '; index_name index_type tablespace_name GLO PAR----------- ----------------------------------------------------------------------------------big_table_pk NORMA                                                                                          L tbs_tmp YES nosql> set Autot trace exp;                                                                                                                                            Sql> SELECT * from big_table where id=1412;                                                                                               Execution Plan      ----------------------------------------------------------P                                                                                                                                                                                                           LAN Hash value:2662411593 --------------- ----------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |---------------------------------------------------------------------------------------------------------   ----------| 0 |              SELECT STATEMENT |     |    1 |     62 | 2 (0) |       00:00:01 |       |   ||  1 | TABLE ACCESS by GLOBAL INDEX rowid|     big_table |    1 |     62 | 2 (0) | 00:00:01 | ROWID | ROWID | |   * 2 | INDEX UNIQUE SCAN |     BIG_TABLE_PK |       1 || 1 (0) |       00:00:01 |       | |-------------------------------------------------------------------------------------------------------------- -------as above, in its execution plan, Pstart and Pstop both have global index ROWID for rowid--, we do not specify global when we add the primary key, but its execution plan indicates that global index access is performed-this place is to be confirmed, for the partition table , whether the primary key or unique index on a non-partition key must be a global index sql> DROP INDEX bita_created_date_i;--creates a global index under which you need to specify the range and value of the partition key sql> CREATE INDEX Bita_            Created_date_i on Big_table (created_date) GLOBAL PARTITION by RANGE (created_date) (PARTITION Idx_1 VALUES Less THAN (to_date (' 01/01/2013 ', ' dd/mm/yyyy ')) tablespace idx1, PARTITION i Dx_2 values less THAN (to_date (' 01/01/2014 ', ' dd/mm/yyyy ') tablespace idx2, PARTITION idx_3 values LE SS THAN (MaxValue) tablespace idx3);                     Sql> Select Index_name, Partitioning_type, partition_count,locality from User_part_indexes;index_name Partiti partition_count locali----------------------------------------------------------Bita_Created_date_i_g RANGE 3 globalsql> Select Partition_name, High_value, tablespace_name from us Er_ind_partitions; Partition_name high_value tablespace_name--------------------------------------------------- ------------------------------idx_1 to_date (' 2013-01-01 idx1idx_2 to_d ATE (' 2014-01-01 idx2idx_3 MAXVALUE IDX3--Below is its execution plan, can be seen to support partition elimination sql> set Autot TR ACE exp; Sql> SELECT * from big_table where created_date=to_date (' 20130625 ', ' yyyymmdd '); Execution Plan----------------------------------------------------------plan hash value:1378264218--------------- ------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |---------------------------------------------------------------------------------------------------------------------------| 0 |                     SELECT STATEMENT |     |    1 |     41 | 2 (0) |       00:00:01 |       |   ||  1 |                     PARTITION RANGE Single |     |    1 |     41 | 2 (0) |     00:00:01 |     2 |   2 | |   2 | TABLE ACCESS by GLOBAL INDEX rowid|     big_table |    1 |     41 | 2 (0) |     00:00:01 |     2 | 2 | |    * 3 | INDEX RANGE SCAN |     bita_created_date_i |       1 |     | 1 (0) |     00:00:01 |     2 | 2 |-------------------------------------------------------------------------------------------------------------                                                                 ----------------The following is a range query, Pstart is 1,pstop 2, and also supports partition elimination sql> select * from big_table 2 where Created_date>=to_date (' 20120625 ', ' YYYYMMDD ') and Created_date<=to_date (' 20130625 ', ' Y Yyymmdd '); Execution Plan----------------------------------------------------------Plan hash value:213633793----------------------------------------------------------------------------------------------- -------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart|   Pstop |------------------------------------------------------------------------------------------------------| 0 |           SELECT STATEMENT |  |   3334 |    133k| 14 (0) |       00:00:01 |       |   ||  1 |           PARTITION RANGE iterator|  |   3334 |    133k| 14 (0) |     00:00:01 |     1 | 2 | |   * 2 | TABLE ACCESS Full |  big_table |   3334 |    133k| 14 (0) |     00:00:01 |     1 | 2 |------------------------------------------------------------------------------------------------------

Related references:

Switch normal tables to partitioned tables online using dbms_redefinition
Switch normal table to partitioned table using Exchange Mode
Oracle partition Table
Import and export Oracle partition table data

To switch a normal table to a partitioned table using export Import (DataPump)