Shulou(Shulou.com)05/31 Report--

This article mainly introduces Oracle how to create a partition index, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Summary of the partition index: first, the partition index is divided into two categories:

1. Global, it must be Prefix. There is no non-prefix

2. Local, which is divided into two categories:

Prefix: the first column of the index is equal to the partitioned column of the table.

Non-prefix: the first column of the index is not equal to the partitioned column of the table.

The index of LOCAL can only be partitioned by the table, not by itself. They belong to EQUI-Partition.

GLOBAL indexes can be unpartitioned, which is a normal index at this time. There can be only one index for the same column, which can be an index of GLOBAL or LOCAL. Only a GLOBAL index can be created if the column of the unique index is not a partitioned column of the table.

For example: partition table

Create table test (id number,data varchar2)

Partition by RANGE (id)

(

Partition p1 values less than (10000)

Partition p2 values less than (20000)

Partition p3 values less than (maxvalue)

);

Create an index of LOCAL on the ID column

SQL > create index id_local on test (id) local

Index created.

SQL > select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='ID_LOCAL'

INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS

-

ID_LOCAL P1 10000 USABLE

ID_LOCAL P2 20000 USABLE

ID_LOCAL P3 MAXVALUE USABLE

From the above, we can see that the partition of the index is the same as the table, that is, EQUI-PARTITION

If I add a partition to the table, Oracle will automatically maintain the index of the partition. Note that the partition must be added with split at this time. Adding directly will make an error. For example:

SQL > alter table test add partition p4 values less than (30000)

Alter table test add partition p4 values less than (30000)

*

ERROR at line 1:

ORA-14074: partition bound must collate higher than that of the last partition

SQL > alter table test split partition p3 at (30000) into (partition p3, partition p4)

Table altered.

SQL > select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='ID_LOCAL'

INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS

-

ID_LOCAL P1 10000 USABLE

ID_LOCAL P2 20000 USABLE

ID_LOCAL P3 30000 USABLE

ID_LOCAL P4 MAXVALUE USABLE

SQL > select INDEX_NAME,INDEX_TYPE,TABLE_NAME from dba_indexes where index_name='ID_LOCAL'

INDEX_NAME INDEX_TYPE TABLE_NAME

-

ID_LOCAL NORMAL TEST

-- Delete id_local index

SQL > drop index id_local

Index dropped.

Re-create an index of GLOBAL on the ID column

SQL > create index id_global on test (id) global

Index created.

SQL > select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='ID_GLOBAL'

No rows selected

SQL > select INDEX_NAME,INDEX_TYPE,TABLE_NAME from dba_indexes where index_name='ID_GLOBAL'

INDEX_NAME INDEX_TYPE TABLE_NAME

-

ID_GLOBAL NORMAL TEST

As can be seen from the above, it is a common index at this time. There is no record in dba_ind_partitions.

-Delete the index

SQL > drop index id_global

Index dropped.

Note: do not delete will be reported: ORA-01408: such column list already indexed

-create a global index

SQL > create index i_id_global on test (data) global

Partition by range (id)

(partition p1 values less than (10000)

Partition p2 values less than (MAXVALUE)

);

Partition by range (id)

*

ERROR at line 2:

ORA-14038: GLOBAL partitioned index must be prefixed

This error indicates that the index of the GLOBAL must be prefixed, that is, the column of the index partition, must be the partitioned column of its base table.

SQL > create index id_global on test (id) global

Partition by range (id)

(partition p1 values less than (10000)

Partition p2 values less than (MAXVALUE)

);

Index created.

SQL > select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='ID_GLOBAL'

INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS

-

ID_GLOBAL P1 10000 USABLE

ID_GLOBAL P2 MAXVALUE USABLE

SQL > select INDEX_NAME,INDEX_TYPE,TABLE_NAME from dba_indexes where index_name='ID_GLOBAL'

INDEX_NAME INDEX_TYPE TABLE_NAME

-

ID_GLOBAL NORMAL TEST

As you can see from the above, it is now an index of GLOBAL. There's a record in dba_ind_partitions. Please make a comparison with the above to deepen your impression.

Second, how to determine what kind of partition index to establish (GLOBAL or LOCAL)

I will use the following example to analyze what type of index needs to be created.

Create table TT (id number,createdate date)

Partition by range (createdate)

(

Partition Q1 VALUES LESS THAN (TO_DATE ('2012-03-30)

Partition Q2 VALUES LESS THAN (TO_DATE ('2012-06-30)

Partition Q3 VALUES LESS THAN (TO_DATE ('2012-09-30)

Partition Q4 VALUES LESS THAN (TO_DATE ('2012-12-31)

Partition Q_OTHERS VALUES LESS THAN (MAXVALUE)

);

Note: it can only be to_date, not any other function, maxvalue must be at the end, it can include null value.

The first situation:

If the condition of the query statement is where createdate='2012-10-19 'and id > 100, then you are querying partition 4, assuming that he has 100000 records. When scanning these 100000 records,

You can use the index on the id column. At this point, you can set up a local nonprofiex index on the ID column.

Create index index_tt1_local on TT (id) local

(partition p1

Partition p2

Partition p3

Partition p4

Partition p5

);

Note: the number of index partitions should be the same as the basic number of partitions.

SQL > select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='INDEX_TT1_LOCAL'

INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS

-

INDEX_TT1_LOCAL P1 TO_DATE ('2012-03-30 USABLE

00Rose, 'SYYYY-M.

M-DD HH24:MI:SS', 'n

LS_CALENDAR=GREGORIA

INDEX_TT1_LOCAL P2 TO_DATE ('2012-06-30 USABLE

00Rose, 'SYYYY-M.

M-DD HH24:MI:SS', 'n

LS_CALENDAR=GREGORIA

INDEX_TT1_LOCAL P3 TO_DATE ('2012-09-30 USABLE

INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS

-

00Rose, 'SYYYY-M.

M-DD HH24:MI:SS', 'n

LS_CALENDAR=GREGORIA

INDEX_TT1_LOCAL P4 TO_DATE ('2012-12-31 USABLE

00Rose, 'SYYYY-M.

M-DD HH24:MI:SS', 'n

LS_CALENDAR=GREGORIA

INDEX_TT1_LOCAL P5 MAXVALUE USABLE

The second situation:

If the statement condition of the query has only one createdate, such as where createdate='2010-10-19, then a local profiex index is established on the createdate in this case

SQL > create index index_TT2_local on TT (createdate) local

Index created.

SQL > select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='INDEX_TT2_LOCAL'

INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS

-

INDEX_TT2_LOCAL Q1 TO_DATE ('2012-03-30 USABLE

00Rose, 'SYYYY-M.

M-DD HH24:MI:SS', 'n

LS_CALENDAR=GREGORIA

INDEX_TT2_LOCAL Q2 TO_DATE ('2012-06-30 USABLE

00Rose, 'SYYYY-M.

M-DD HH24:MI:SS', 'n

LS_CALENDAR=GREGORIA

INDEX_TT2_LOCAL Q3 TO_DATE ('2012-09-30 USABLE

INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS

-

00Rose, 'SYYYY-M.

M-DD HH24:MI:SS', 'n

LS_CALENDAR=GREGORIA

INDEX_TT2_LOCAL Q4 TO_DATE ('2012-12-31 USABLE

00Rose, 'SYYYY-M.

M-DD HH24:MI:SS', 'n

LS_CALENDAR=GREGORIA

INDEX_TT2_LOCAL Q_OTHERS MAXVALUE USABLE

From the above query, we can see that he and the table are equi-partitioned.

The third situation:

If the query does not have createdate at all, but has conditions like where id > 100, then the GLOBAL index can only be built on the ID column.

SQL > drop index index_tt1_local

Index dropped.

Note: do not delete ORA-01408: such column list already indexed

SQL > create index index_tt3_global on TT (id)

Global partition by range (id)

(

Partition p1 values less than (100000)

Partition p2 values less than (200000)

Partition p3 values less than (MAXVALUE)

);

As you can see from the above, the number of partitions of GLOBAL's index has nothing to do with its base table. He can even build an index like this, that is, a general index. However, the LOCAL must be the same as its basic number of partitions.

-delete the index index_tt3_global before you create it

SQL > create index index_tt3_global on TT (id) global

Index created.

Thank you for reading this article carefully. I hope the article "how to create a partition index in Oracle" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.