advertisements
_____________________________________________________________________________________________________________________
The Global & Local indexes are mainly related to Oracle table partitions. On a partitioned table (it can be partitioned by range, hash, list) you have the authority to create a local or global index. It is up to your choice. Index partitioning is transparent to all the SQLs. The advantage of the indexes is the Oracle query engine will scan only the required index partition to serve the query, thus speeding up the query significantly.
Moreover, the Oracle parallel query feature will automatically takes the index partitions and fires the simultaneous queries to scan the indexes. Here in this topic I would like to explain about the difference, how to create, where it is applicable and features so on.
Moreover, the Oracle parallel query feature will automatically takes the index partitions and fires the simultaneous queries to scan the indexes. Here in this topic I would like to explain about the difference, how to create, where it is applicable and features so on.
How to Create Local Index, Advantages & Disadvantages?
While creating the index on the Oracle table partitions we can use these keyword ‘LOCAL’. Local indexes are partitioned indexes which are easy to manage and each index partition is associated with its respective table partitions. Local indexes are most suited for data warehousing or DSS applications. In a data warehouse system, were typically a large number of rows are returned from a query, this overhead is a smaller proportion of the overall time taken by the query. Local indexes are ideal for any index that is prefixed with the same column used to partition the table.
The major advantage of local indexes is there will not be any index invalidation when you perform alter table exchange, drop, add partitions. When you perform above mention operations on the underlying table, the index remain valid.
Come to the table reorganization part, the local indexes gives the advantage of making individual partitions of a table and indexes offline for the maintenance. So it won’t affect the other partitions in the table or index. The maintenance would be easy for the local indexes.
A local partitioned index creates one-to-one relationship between the table partitions. The key value for the table partition and the index partition must be identical. When you create a local index on a partitioned table, it automatically creates index partitions as many as in the table partitions. Similarly when we add a new partition to the table, Oracle automatically adds one index partition to the index as well.
The index partition gets deleted when the underlying table partition dropped. We can create bitmap on partitioned tables, with the restriction that the bitmap indexes must be local to the partitioned table. Bitmap indexes cannot be with Global indexes.
The disadvantages are like we cannot create any specific index partitions to the index.
In this example I have a table with two partitions
CREATE TABLE "SCOTT"."EXAMPLE"
( "ID" NUMBER(10,0) NOT NULL ENABLE,
"UID" VARCHAR2(40),
"PIX" VARCHAR2(40),
"FNAME" VARCHAR2(100),
"MNAME" VARCHAR2(100),
"LNAME" VARCHAR2(100),
"SFIX" VARCHAR2(40),
"JTITLE" VARCHAR2(100),
"COMNAME" VARCHAR2(40),
"ADD1" VARCHAR2(50),
"ADD2" VARCHAR2(50),
"ADD3" VARCHAR2(50),
"CTY" VARCHAR2(30),
"STAT" VARCHAR2(20),
"POSTCODE" VARCHAR2(10),
"COUNTY" VARCHAR2(40),
"COUNTRY" VARCHAR2(40),
"PH_NUM" VARCHAR2(20),
"FX_NUM" VARCHAR2(15)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
PARTITION BY RANGE ("ID")
(PARTITION "EXAMPLE_P1" VALUES LESS THAN (50000)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS ,
PARTITION "EXAMPLE_P2" VALUES LESS THAN (100000)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" NOCOMPRESS )
SQL> SELECT COUNT(*) FROM EXAMPLE;
COUNT(*)
----------
95120
Local Index:
1 create index example_idx
2 on example (id)
3* local
SQL> /
Index created.
SQL> select INDEX_NAME,INDEX_type ,GLOBAL_STATS from user_indexes;
INDEX_NAME INDEX_TYPE GLO
------------------------------ --------------------------- ---
EXAMPLE_IDX NORMAL NO
1* select index_name, partition_name from user_ind_partitions
SQL> /
INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
EXAMPLE_IDX EXAMPLE_P2
EXAMPLE_IDX EXAMPLE_P1
How to Create Global Index, Advantages & Disadvantages?
Global index partitions are used for OLTP applications where fewer index probes are required than with local partitioned indexes. For Global indexes the DBA has the choice to create the partitions as many as required. In Global partition scheme the index is harder to maintain since the index may span partitions in the base table. If we drop one of the index partition as part of reorganization the entire global index will be affected. Global partition indexes can be range partitioned using a different partition scheme from the underlying table. Which mean table partition can be hash partitioned whereas the global index partition can be range partitioned.
Global indexes can be used to enforce the uniqueness on columns not used to partition the table. These are the following situations you will be forced to use Global indexes.
- When you create a primary key on the table
- When you create a unique key on the table
When you try to create a local index on unique key, you will get ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
There are two types of global indexes. They are Global non-paritioned indexes and global partitioned indexes. Global non-partitioned indexes are similar to the normal non-partitioned indexes.
There is no dependency in Global index partitioned key and Table partition key. The partition maximum bound is defined by the keyword MAXVALUE. You can add partitions to the global partition index by splitting the MAXVALUE partition. Using “alter index drop partition” you can drop a empty global index partition.
Disadvantage: If you try to drop a non-empty global index partion will cause the next highest partition to be marked unusable. I.e. You cannot drop the highest partition in a global index.
Global indexes get invalidated when you perform a partition exchange on the table.
SQL> CREATE INDEX example_Glo_idx
on example (id)
GLOBAL PARTITION BY RANGE (ID)
(PARTITION example_Glo_id_p1 VALUES LESS THAN (5000)),
(PARTITION example_Glo_id_p2 VALUES LESS THAN (10000));
(PARTITION example_Glo_id_p1 VALUES LESS THAN (5000)),
*
ERROR at line 4:
ORA-14021: MAXVALUE must be specified for all columns
For GLOBAL indexes the maxvalue partition must be created.
SQL> CREATE INDEX example_Glo_idx
2 on example (id)
3 GLOBAL PARTITION BY RANGE (ID)
4 (PARTITION example_Glo_id_p1 VALUES LESS THAN (5000),
5 PARTITION example_Glo_id_p2 VALUES LESS THAN (10000),
6 PARTITION example_Glo_id_p3 VALUES LESS THAN (1000000),
7 PARTITION example_Glo_id_p4 VALUES LESS THAN (2000000),
8 PARTITION example_Glo_id_max VALUES LESS THAN (MAXVALUE));
Index created.
SQL> select index_name, partition_name,status from user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
EXAMPLE_GLO_IDX EXAMPLE_GLO_ID_P2 USABLE
EXAMPLE_GLO_IDX EXAMPLE_GLO_ID_P3 USABLE
EXAMPLE_GLO_IDX EXAMPLE_GLO_ID_P4 USABLE
EXAMPLE_GLO_IDX EXAMPLE_GLO_ID_MAX USABLE
EXAMPLE_GLO_IDX EXAMPLE_GLO_ID_P1 USABLE
Here I am trying to drop one index partition which makes the next highest partition to UNUSABLE status.
SQL> alter index EXAMPLE_GLO_IDX drop partition EXAMPLE_GLO_ID_P3;
Index altered.
SQL> select index_name, partition_name,status from user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
EXAMPLE_GLO_IDX EXAMPLE_GLO_ID_P2 USABLE
EXAMPLE_GLO_IDX EXAMPLE_GLO_ID_P4 UNUSABLE
EXAMPLE_GLO_IDX EXAMPLE_GLO_ID_MAX USABLE
EXAMPLE_GLO_IDX EXAMPLE_GLO_ID_P1 USABLE
You cannot rebuild the global index as whole.
SQL> alter index EXAMPLE_GLO_IDX rebuild online;
alter index EXAMPLE_GLO_IDX rebuild online
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole
Rebuild need to be performed partition wise.
SQL> alter index EXAMPLE_GLO_IDX rebuild partition EXAMPLE_GLO_ID_P4;
Index altered.
SQL>
SQL> select index_name, partition_name,status from user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
EXAMPLE_GLO_IDX EXAMPLE_GLO_ID_P2 USABLE
EXAMPLE_GLO_IDX EXAMPLE_GLO_ID_MAX USABLE
EXAMPLE_GLO_IDX EXAMPLE_GLO_ID_P4 USABLE
EXAMPLE_GLO_IDX EXAMPLE_GLO_ID_P1 USABLE
Partition index can be maintained by using UPDATE GLOBAL INDEXES with the tables. Index will be available during the maintenance and it will be online as well. We do not need to rebuild the index after the index maintenance.
SQL>alter table example drop partition employees_p2
update global indexes;
update global indexes;
SQL> select index_name, partition_name,status from user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
EXAMPLE_GLO_IDX EXAMPLE_GLO_ID_P2 USABLE
EXAMPLE_GLO_IDX EXAMPLE_GLO_ID_MAX USABLE
EXAMPLE_GLO_IDX EXAMPLE_GLO_ID_P4 USABLE
EXAMPLE_GLO_IDX EXAMPLE_GLO_ID_P1 USABLE
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment