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

This article is about how to parse the local index index and global index in the partition index, the editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article.

Partitioned indexes are divided into local (local index) indexes and global indexes (global index).

Local indexes can be divided into indexes with prefixes (prefix) and indexes without prefixes (nonprefix). Global indexes currently support only indexes with prefixes. Both B-tree and bitmap indexes can be partitioned, but HASH indexes cannot be partitioned. The bitmap index must be local. The following describes the characteristics of local indexes and global indexes to illustrate the differences

1. Characteristics of local index:

Partition index is to create an index separately on all areas, it can be automatically maintained, in drop or truncate a partition does not affect the use of other partition indexes of the index, that is, the index will not fail, it is more convenient to maintain, but it has a slight impact on query performance.

Create index idx_ta_c2 on ta (c2) local (partition p1 Magnum partition p2 Partition p3 Magi partition p4) or create index idx_ta_c2 on ta (c2) local

1. The local index must be a partitioned index, the partitioning key is equal to the partitioning key of the table, and the number of partitions is equal to the partitioning of the table. In a word, the partitioning mechanism of the local index is the same as that of the table.

two。 If the index column of a local index begins with a partitioning key, it is called a prefix local index.

3. If the column of the local index does not begin with a partition key, or does not contain a partition key column, it is called a non-prefix index.

4. Both prefix and non-prefix indexes can support index partition elimination as long as the condition of the query contains the index partition key.

5. The local index only supports the uniqueness within the partition, but not on the table, so if you want to use the local index to constrain the uniqueness of the table, the partition key column must be included in the constraint.

6. The local partition index is for a single partition, each partition index only points to one table partition, while the global index is not, a partition index can point to n table partitions, at the same time, a table partition may also point to n index partitions, doing truncate or move,shrink to a partition in the partition table, etc., may affect n global index partitions, because of this, the local partition index has higher availability.

7. Bitmap indexes can only be locally partitioned indexes.

8. Local indexes are mostly used in data warehouse environments.

Local index: after creating a partitioned table, if you need to create an index on the table, and the partitioning mechanism of the index is the same as that of the table, then such an index is called a local partitioned index. Local indexes are automatically managed by ORACLE, which is divided into prefixed local indexes and unprefixed local indexes. What is a local index with a prefix? A local index with a prefix contains the partition key and uses it as the index of the boot column. What is a local index without a prefix? A local index without a prefix is an index that does not have the leading column of the partitioning key as the leading column of the index. The following examples are given:

Create table test (id number,data varchar2)

Partition by RANGE (id)

(

Partition p1 values less than (1000) tablespace p1

Partition p2 values less than (2000) tablespace p2

Partition p3 values less than (maxvalue) tablespace p3

);

Create index i_id on test (id) local; creates a prefixed local index because id is a partitioning key.

SQL > select dbms_metadata.get_ddl ('INDEX','I_ID','ROBINSON') index_name FROM DUAL;- removed some useless information

INDEX_NAME

CREATE INDEX "ROBINSON". "I_ID" ON "ROBINSON". "TEST" ("ID") LOCAL

(PARTITION "P1" TABLESPACE "P1", PARTITION "P2" TABLESPACE "P2", PARTITION "P3" TABLESPACE "P3")

-- you can also create:

SQL > drop index i_id

Index dropped

SQL > CREATE INDEX "ROBINSON". "I_ID" ON "ROBINSON". "TEST" ("ID") LOCAL

2 (PARTITION "P1" TABLESPACE "P1", PARTITION "P2" TABLESPACE "P2", PARTITION "P3" TABLESPACE "P3")

Index created

Create index i_data on test (data) local; creates a local index without a prefix because data is not a partitioning key.

SQL > select dbms_metadata.get_ddl ('INDEX','I_DATA','ROBINSON') index_name FROM DUAL;--- deleted some useless information

INDEX_NAME

CREATE INDEX "ROBINSON". "I_DATA" ON "ROBINSON". "TEST" ("DATA") LOCAL

(PARTITION "P1" TABLESPACE "P1", PARTITION "P2" TABLESPACE "P2", PARTITION "P3" TABLESPACE "P3")

You can also prove from the user_part_indexes view that the index you just created is prefixed and unprefixed.

SQL > select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes

INDEX_NAME TABLE_NAME PARTITIONING_TYPE LOCALITY ALIGNMENT

-

I_DATA TEST RANGE LOCALNON_PREFIXED

I_ID TEST RANGE LOCALPREFIXED

Second, the characteristics of the global index:

A global index is to create an index on a whole table. It can create its own partition, which can be different from the partition of a partitioned table, that is, it is an independent index. You need to create an index alter index idx_xx rebuild when you drop or truncate a partition, or you can do it with alter table table_name drop partition partition_name update global indexes;, but it takes a long time to rebuild the index. You can query the user_indexes, user_part_indexes, and user_ind_partitions views to see if the index is valid

Create index idx_ta_c3 on ta (c3)

Or divide the global index into multiple extents (note that it is different from the partitioning of the partitioned table):

Create index idx_ta_c4 on ta (c4) global partition by range (c4) (partition ip1 values less than (10000), partition ip2 values less than (20000), partition ip3 values less than (maxvalue))

Note that the bootstrap column on the index should be the same as the column after range, otherwise there will be ORA-14038 errors.

Oracle automatically creates a global index on the primary key

If you want to create a partitioned index on the column of the primary key, unless the primary key includes the partitioning key, and the primary key is built on two or more columns.

When the partition of the table is deleted frequently and the data is updated frequently, the global index is avoided for maintenance convenience.

1. The partition key and number of partitions of the global index and the partition keys and number of partitions of the table may be different. The partitioning mechanism of the table and the global index is different, which is divided into the global index and the global partition index. Take the following example (global partition index).

two。 The global index can be partitioned or unpartitioned, and the global index must be a prefix index, that is, the index column of the global index must be preceded by the index partitioning key.

3. The index entry of a global partitioned index may point to several partitions, so for a global partitioned index, even truncating data in one partition requires rebulid several partitions or even the entire index.

4. Global index is mostly used in oltp system.

5. Global partitioning indexes are only partitioned by range or hash hash, and hash partitions are only supported after 10g.

When 6.oracle9i does move or truncate to the partition table in the future, it can use the update global indexes statement to update the global partition index synchronously and consume certain resources in exchange for a high degree of availability.

7. The table uses column an as the partition, and refers to b as the local partition index. if b is used in the where condition, then oracle will scan the partitions of all tables and indexes, and the cost will be higher than the partition. At this time, you can consider using b as the global partition index.

Global index: unlike the local partitioned index, the partitioning mechanism of the global partitioned index is different from that of the table. Global partition index global partition index can only be a B-tree index, so far (10gR2), oracle only supports prefixed global indexes. In addition, oracle does not automatically maintain the global partitioned index, when we make changes to the partition of the table, if the modified statement is not added with update global indexes, then the index will not be available.

Take the partition table test created above as an example to explain the global partition index:

SQL > drop index i_id

Index dropped

SQL > create index i_id_global on test (id) global

2 partition by range (id)

3 (partition p1 values less than (2000) tablespace p1

4 partition p2 values less than (maxvalue) tablespace p2

5)

Index created

SQL > alter table test drop partition p3

Table altered

ORACLE does not automatically maintain global partitioned indexes by default. Look at the status column.

SQL > select INDEX_NAME,PARTITION_NAME,STATUS from user_ind_partitions where index_name='I_ID_GLOBAL'

INDEX_NAME PARTITION_NAME STATUS

-

I_ID_GLOBAL P1 USABLE

I_ID_GLOBAL P2 USABLE

SQL > create index i_id_global on test (data) global

2 partition by range (id)

3 (partition p1 values less than (2000) tablespace p1

4 partition p2 values less than (maxvalue) tablespace p2

5)

Create index i_id_global on test (data) global

Partition by range (id)

(partition p1 values less than (2000) tablespace p1

Partition p2 values less than (maxvalue) tablespace p2

)

ORA-14038: GLOBAL partitioned indexes must be prefixed

SQL > create bitmap index i_id_global on test (id) global

2 partition by range (id)

3 (partition p1 values less than (2000) tablespace p1

4 partition p2 values less than (maxvalue) tablespace p2

5)

Create bitmap index i_id_global on test (id) global

Partition by range (id)

(partition p1 values less than (2000) tablespace p1

Partition p2 values less than (maxvalue) tablespace p2

)

ORA-25113: GLOBAL may not work with bitmap indexes

Third, the partition index cannot be rebuilt as a whole, but must be rebuilt for each partition.

SQL > alter index i_id_global rebuild online nologging

Alter index i_id_global rebuild online nologging

ORA-14086: partitioned indexes cannot be rebuilt as a whole

At this point, you can query dba_ind_partitions or user_ind_partitions, find partition_name, and then rebuild each partition.

SQL > select index_name,partition_name from user_ind_partitions where index_name='I_ID_GLOBAL'

INDEX_NAME PARTITION_NAME

I_ID_GLOBAL P1

I_ID_GLOBAL P2

SQL > alter index i_id_global rebuild partition p1 online nologging

Index altered

SQL > alter index i_id_global rebuild partition p2 online nologging

Index altered

4. Several views on the partitioned index

Dba_ind_partitions describes the partitioning of each partition index, as well as statistics

Summary statistics of dba_part_indexes partitioned indexes, you can know which partitioned indexes are on each table and the type of partitioned index (local/global)

Dba_indexes minus dba_part_indexes (minus operation) can find out which non-partitioned indexes are on each table.

5. Experiment

SQL > create table T48_TRANSACTION_MODEL

(

Trandate DATE

Orgid VARCHAR2 (11)

Stan NUMBER

Subjectno VARCHAR2 (10)

Subjectname VARCHAR2 (50)

Acctid NUMBER

)

Partition by range (TRANDATE)

(

Partition XYZ_20100000 values less than (TO_DATE ('2011-01-0100: 00 SYYYY-MM-DD HH24:MI:SS'))

Partition XYZ_20110101 values less than (TO_DATE ('2011-01-02 00 SYYYY-MM-DD HH24:MI:SS'))

Partition XYZ_20110102 values less than (TO_DATE ('2011-01-03 00 SYYYY-MM-DD HH24:MI:SS'))

Partition XYZ_20110103 values less than (TO_DATE ('2011-01-04 00 SYYYY-MM-DD HH24:MI:SS'))

Partition XYZ_20110104 values less than (TO_DATE ('2011-01-05 00 partition XYZ_20110104 values less than,' SYYYY-MM-DD HH24:MI:SS'))

Partition XYZ_20110105 values less than (TO_DATE ('2011-01-06 00 SYYYY-MM-DD HH24:MI:SS'))

Partition XYZ_20110106 values less than (TO_DATE ('2011-01-07 00 SYYYY-MM-DD HH24:MI:SS'))

Partition XYZ_20110107 values less than (TO_DATE ('2011-01-08 00 SYYYY-MM-DD HH24:MI:SS'))

);

Table created.

SQL > create index T48_TRANSACTION_MODEL_IDX1 on T48_TRANSACTION_MODEL (stan) local

Index created.

SQL > create index T48_TRANSACTION_MODEL_IDX2 on T48_TRANSACTION_MODEL (acctid)

Index created.

SQL > select index_owner,index_name,partition_name from dba_ind_partitions where index_name in ('T48TRANSACTION MODELLING IDX1' and 'T48TRANSACTION MODELLYIDX2')

INDEX_OWNER INDEX_NAME PARTITION_NAME

-

SYS T48_TRANSACTION_MODEL_IDX1 XYZ_20100000

SYS T48_TRANSACTION_MODEL_IDX1 XYZ_20110101

SYS T48_TRANSACTION_MODEL_IDX1 XYZ_20110102

SYS T48_TRANSACTION_MODEL_IDX1 XYZ_20110103

SYS T48_TRANSACTION_MODEL_IDX1 XYZ_20110104

SYS T48_TRANSACTION_MODEL_IDX1 XYZ_20110105

SYS T48_TRANSACTION_MODEL_IDX1 XYZ_20110106

SYS T48_TRANSACTION_MODEL_IDX1 XYZ_20110107

8 rows selected.

The query found that the global index is not in the view dba_ind_partitions.

-- check whether it is a partitioned index:

SQL > select owner,index_name,index_type,table_owner,table_name,table_type,partitioned from dba_indexes where index_name in ('T48TRANSACTION MODELLING IDX1' and 'T48TRANSACTION MODELLYIDX2')

OWNER INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE PAR

-

SYS T48_TRANSACTION_MODEL_IDX2 NORMAL SYS T48_TRANSACTION_MODEL TABLE NO

SYS T48_TRANSACTION_MODEL_IDX1 NORMAL SYS T48_TRANSACTION_MODEL TABLE YES

The queried index T48_TRANSACTION_MODEL_IDX2 is not a partitioned index, while T48_TRANSACTION_MODEL_IDX1 is a partitioned index.

SQL > select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where index_name in ('T48TRANSACTION MODELLING IDX1' and 'T48TRANSACTION MODELLYIDX2')

INDEX_NAME TABLE_NAME PARTITION LOCALI ALIGNMENT

-

T48_TRANSACTION_MODEL_IDX1 T48_TRANSACTION_MODEL RANGE LOCAL NON_PREFIXED

Summary:

Global Index:

Advantages: through index retrieval, there are no predicates that limit partitions, or when cross-partitions, the performance is better.

Disadvantages: trouble in partition maintenance, failure of drop partition maintenance, high cost of index maintenance in dml, and difficulty in rebuild when the data is large

Local Index:

Advantages: through index retrieval, there are predicates with limited partitions, when not across partitions, the performance is good, partition maintenance is easy, dml index maintenance bottom, rebuild is also convenient.

Disadvantages: when retrieved by index without partitioning predicates, or across partitions, the performance is not as good as the global index

If there is partition cropping, then the other columns will be partitioned.

If there is no partition cropping, then the column builds the global index

The above is how to parse the local index index and global index in the partition index. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.