分割槽表全域性索引與本地索引的選擇
原文地址：http://www.oraclebuffer.com/general-discussions/which-index-to-choose-global-or-local-index-for-partitioned-table/
Introduction:
We often deal with Indexes while working on Oracle database tables. We do know, there are different type of indexes like B-Tree, Bitmap, Hash-value, etc, etc.. which helps us to improvise the query performance.
However, with respect to Oracle partitioned tables; while creating an Index we have two additional options to choose from namely Local and Global Index.
Local and Global Index type determines the scope or access point of a Oracle Index in a partitioned table.
In today’s post I am trying to cover the areas to look for before deciding on the Local or Global Index. This article is bases on my understanding and might differ with other.
I welcome all the constructive feedback and suggestions to improvise this article.
Overview:
Local Index:
A Local index is a partitioned index just like a partitioned table and maintains a one to one relationship between the index partitions and the table partitions.
Local Index partitioning helps in easier Index management as each index partition is independent of the other partition in the index and they support higher availability during Index maintenance operations . Oracle manages the local index partitions automatically, upon creation or deletion of the partitions from the table.
Trying to manually add or drop a partition from Local partitioned index would lead to errors like below
SQL:labpa> alter index EMP_INFO_L_IDX drop partition EMP_INFO_P0;
alter index EMP_INFO_L_IDX drop partition EMP_INFO_P0
*
ERROR at line 1:
ORA-14076: submitted alter index partition/subpartition operation is not valid for local partitioned index
Local Index partitions are the most commonly used type of index partitions
Global Index:
A Global index has a one to many relationship, where a index or index partition can be mapped to multiple table partitions. A Global index can be either partitioned or non partitioned. By default Oracle creates all the indexes as Global Non partitioned index. In case of partitioned global indexes, each partition in the global index can be mapped to multiple underlying table partitions.
Global index has certain restrictions with respect to index management and maintenance. For instance, dropping a table partition causes the respective global index to become unusable. It also has certain edge over the local index such as we can define as many partitions as we want for a global index. We can also create a global partitioned index for a non partitioned Oracle table.
A simple excercise to help in choosing the index type for a partitioned table
Lets create a partitioned table for our exercise
SQL:labpa> CREATE TABLE emp_info
2 (
3 emp_id NUMBER NOT NULL,
4 join_date DATE NOT NULL,
5 email VARCHAR2(100)
6 )
7 PARTITION BY RANGE (join_date)
8 (
9 PARTITION emp_info_p0 VALUES LESS THAN (TO_DATE('01-JAN-2011', 'DD-MON-YYYY')) TABLESPACE APP_DATA,
10 PARTITION emp_info_p1 VALUES LESS THAN (TO_DATE('01-JAN-2012', 'DD-MON-YYYY')) TABLESPACE APP_DATA,
11 PARTITION emp_info_p2 VALUES LESS THAN (TO_DATE('01-JAN-2013', 'DD-MON-YYYY')) TABLESPACE APP_DATA,
12 PARTITION emp_info_p3 VALUES LESS THAN (TO_DATE('01-JAN-2014', 'DD-MON-YYYY')) TABLESPACE APP_DATA,
13 PARTITION emp_info_p4 VALUES LESS THAN (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')) TABLESPACE APP_DATA
14 );
Table created.
We have created a table EMP_INFO with five partitions to hold records for the year 2010, 2011, 2012, 2013 and 2014 on separate partitions.
Now, Lets load some data into the table
SQL:labpa> declare
2 begin
3 for i in 1..100000
4 loop
5 insert into emp_info values (i,'13-APR-2010','xyz'||i);
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL:labpa> declare
2 begin
3 for i in 100001..200000
4 loop
5 insert into emp_info values (i,'13-APR-2011','xyz'||i);
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL:labpa> declare
2 begin
3 for i in 200001..300000
4 loop
5 insert into emp_info values (i,'13-APR-2012','xyz'||i);
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL:labpa> declare
2 begin
3 for i in 300001..400000
4 loop
5 insert into emp_info values (i,'13-APR-2013','xyz'||i);
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL:labpa> declare
2 begin
3 for i in 400001..500000
4 loop
5 insert into emp_info values (i,'13-APR-2014','xyz'||i);
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL:labpa> commit;
Commit complete.
SQL:labpa> select count(*) from emp_info;
COUNT(*)
----------
500000
SQL:labpa> select TABLE_NAME,PARTITION_NAME,NUM_ROWS from user_tab_partitions where table_name='EMP_INFO';
TABLE_NAME PARTITION_NAME NUM_ROWS
--------------- ---------------- ----------
EMP_INFO EMP_INFO_P0 100000
EMP_INFO EMP_INFO_P1 100000
EMP_INFO EMP_INFO_P2 100000
EMP_INFO EMP_INFO_P3 100000
EMP_INFO EMP_INFO_P4 100000
Now, we have 500000 records distributed evenly across the table partitions. We are now good to start with our analysis
Remember: It is always not necessary to create index for a table. We need to create index based on the query that is operating on the data.
Index is best suited, when we are querying a small section of data out of a large data set.
Okay, now coming back to the exercise. Lets query the table with the partition key (join_date) being as the query predicate
SQL:labpa> explain plan for select * from emp_info where join_date='13-APR-2010';
Explained.
SQL:labpa> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 1181328030
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 187K| 13M| 231 (4)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 187K| 13M| 231 (4)| 00:00:01 | 1 | 1 |
|* 2 | TABLE ACCESS FULL | EMP_INFO | 187K| 13M| 231 (4)| 00:00:01 | 1 | 1 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
2 - filter("JOIN_DATE"=TO_DATE(' 2010-04-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
18 rows selected.
In this example, we are querying all the rows from a single partition and as can bee seen in the explain plan; Oracle performed partition elimination based on the partition key (provided as query predicate). If we are always performing query like this, where it selects a large set of data from a particular partition based on the partition key, we do not need an index at all. Oracle will take advantage of partition elimination by means of partition pruning and will only scan the required partitions for the requested set of records.
However, if we just want to query a small set of a records from a particular partition, lets say the records with EMP_ID less than 100 and with JOIN_DATE as 13-APR-2010 (i.e. from 1st partition)
SQL:labpa> explain plan for select * from emp_info where emp_id <100 and join_date='13-APR-10';
Explained.
SQL:labpa> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 2926039120
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 666 | 267 (17)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 9 | 666 | 267 (17)| 00:00:01 | KEY | KEY |
|* 2 | TABLE ACCESS FULL | EMP_INFO | 9 | 666 | 267 (17)| 00:00:01 | KEY | KEY |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
2 - filter("EMP_ID"<100 AND "JOIN_DATE"='13-APR-10')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
18 rows selected.
An index might be useful here as we are just querying a small section of data from a single partition. We can implement a local index on each partition and then access the small set of records from that partition by the help of the index belonging to that partition.
SQL:labpa> create index EMP_INFO_IDX_LOCAL on emp_info (emp_id) LOCAL;
Index created.
SQL:labpa> explain plan for select * from emp_info where emp_id <100 and join_date='13-APR-10';
Explained.
SQL:labpa> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1798911101
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 7326 | 4 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 99 | 7326 | 4 (0)| 00:00:01 | KEY | KEY |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| EMP_INFO | 99 | 7326 | 4 (0)| 00:00:01 | KEY | KEY |
|* 3 | INDEX RANGE SCAN | EMP_INFO_IDX_LOCAL | 99 | | 3 (0)| 00:00:01 | KEY | KEY |
---------------------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("JOIN_DATE"='13-APR-10' AND "EMP_ID"<100)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
19 rows selected.
So, a Local index would be useful if we are querying a small set of partitions (based on the partition key as query predicate) from the table and then selecting a small set of records from each of those partitions.
However, what if we want to query a set of records and we are not using the partition key (as query predicate) in our query. Something like, query records with EMP_ID between 199910 and 200010.
If we use a Local Index here on the EMP_ID, Oracle is going to scan all the partitions (since we are not using partition key) and then going to use the Local index of those partitions to return the requested set of records.
SQL:labpa> explain plan for select * from emp_info where emp_id between 199910 and 200010;
Explained.
SQL:labpa> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1213506883
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101 | 7474 | 7 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 101 | 7474 | 7 (0)| 00:00:01 | 1 | 5 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| EMP_INFO | 101 | 7474 | 7 (0)| 00:00:01 | 1 | 5 |
|* 3 | INDEX RANGE SCAN | EMP_INFO_IDX_LOCAL | 101 | | 6 (0)| 00:00:01 | 1 | 5 |
---------------------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP_ID">=199910 AND "EMP_ID"<=200010)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
19 rows selected.
So, there is a overhead of visiting all the partitions and then scanning individual partitions based on their Local index for the requested set of records.
What we can do instead is, implement a Global index on EMP_ID. In this way Oracle will directly access the record from the table based on the Global Index, irrespective of the table partitioning.
SQL:labpa> create index EMP_INFO_IDX_GLOBAL on EMP_INFO (EMP_ID) GLOBAL;
Index created.
SQL:labpa> explain plan for select * from emp_info where emp_id between 199910 and 200010;
Explained.
SQL:labpa> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 425037682
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101 | 7474 | 4 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| EMP_INFO | 101 | 7474 | 4 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | EMP_INFO_IDX_GLOBAL | 101 | | 3 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
2 - access("EMP_ID">=199910 AND "EMP_ID"<=200010)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
18 rows selected.
So, if our queries are selecting a small subset of data from the entire table, we can implement a Global index on the query predicate to directly access the set of records from that table
I have used non partitioned Global Index for this example. Just to note that, you also have a option to create partitioned Global Index, where you can partition the Global Index to store the index values on separate index partitions and can also map a single index partition to single or multiple table partition.
For example, if I want to use partitioned Global index for the partitioned table EMP_INFO and want to store index for the 200000 records in a single partition and the remaining records in a different partition. I can use a Global Partitioned Index as below
SQL:labpa> create index EMP_INFO_IDX_GLOBAL on emp_info (emp_id) global
2 PARTITION by range (emp_id)
3 (
4 PARTITION emp_info_idx_pg1 VALUES LESS THAN (200000) TABLESPACE APP_DATA,
5 PARTITION emp_info_idx_pg2 VALUES LESS THAN (MAXVALUE) TABLESPACE APP_DATA
6 );
Index created.
However, Oracle doesn't manage the partitioned Global index automatically and manual intervention is required for adding or dropping partitions.
We can implement partitioned Global Index to avoid scanning the entire Index.
Following is the example, where I am using a global partitioned Index.
SQL:labpa> explain plan for select * from emp_info where emp_id<100 and join_date='13-APR-2010';
Explained.
SQL:labpa> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3498262492
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 2178 | 4 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 99 | 2178 | 4 (0)| 00:00:01 | 1 | 1 |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| EMP_INFO | 99 | 2178 | 4 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | EMP_INFO_IDX_GLOBAL | 20 | | 3 (0)| 00:00:01 | 1 | 1 |
-----------------------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("JOIN_DATE"=TO_DATE(' 2010-04-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
3 - access("EMP_ID"<100)
16 rows selected.
So, when I use a partitioned Global Index, the query optimizer only scans the partition (s) to which the index value belongs to.
A Global Index is particularly more suitable when direct access to the table records are desired without visiting the underlying table partitions.
Conclusion:
Deciding on the Local or Global index is purely based on the Query requirement as well as the on the expected Index management or maintenance.
With respect to Index maintenance, Local Indexes are easy to manage as they are independent of each other and Oracle itself takes care of the partition creation and deletion for Local Indexes at the event of table partition creation or deletion.
On the other hand Global Indexes are bit complex to manage during index maintenance. For instance, dropping a table partition makes the respective Global Index UNUSABLE and we have to rebuild the whole index.
SQL:labpa> alter table EMP_INFO drop partition emp_info_p0;
Table altered.
SQL:labpa> select index_name,partition_name,status from dba_ind_partitions where index_name='EMP_INFO_IDX_GLOBAL';
INDEX_NAME PARTITION_NAME STATUS
------------------------- ---------------- --------
EMP_INFO_IDX_GLOBAL EMP_INFO_IDX_PG1 UNUSABLE
EMP_INFO_IDX_GLOBAL EMP_INFO_IDX_PG2 UNUSABLE
We do have a option to use the "UPDATE GLOBAL INDEXES" clause while dropping a table partition to avoid the Global Index become Unusable. However, it would be a time consuming operation to drop the partition in case of large Global Indexes as it involves rebuilding the Global Index during the process of drop partition operation.
SQL:labpa> alter table EMP_INFO drop partition EMP_INFO_P1 UPDATE GLOBAL INDEXES;
Table altered.
SQL:labpa> select index_name,partition_name,status from dba_ind_partitions where index_name='EMP_INFO_IDX_GLOBAL';
INDEX_NAME PARTITION_NAME STATUS
------------------------- ---------------- --------
EMP_INFO_IDX_GLOBAL EMP_INFO_IDX_PG1 USABLE
EMP_INFO_IDX_GLOBAL EMP_INFO_IDX_PG2 USABLE
Therefore, there are a number of factors to evaluate before deciding on the type of Index (Local or Global) to be used for a partitioned table.
Introduction:
We often deal with Indexes while working on Oracle database tables. We do know, there are different type of indexes like B-Tree, Bitmap, Hash-value, etc, etc.. which helps us to improvise the query performance.
However, with respect to Oracle partitioned tables; while creating an Index we have two additional options to choose from namely Local and Global Index.
Local and Global Index type determines the scope or access point of a Oracle Index in a partitioned table.
In today’s post I am trying to cover the areas to look for before deciding on the Local or Global Index. This article is bases on my understanding and might differ with other.
I welcome all the constructive feedback and suggestions to improvise this article.
Overview:
Local Index:
A Local index is a partitioned index just like a partitioned table and maintains a one to one relationship between the index partitions and the table partitions.
Local Index partitioning helps in easier Index management as each index partition is independent of the other partition in the index and they support higher availability during Index maintenance operations . Oracle manages the local index partitions automatically, upon creation or deletion of the partitions from the table.
Trying to manually add or drop a partition from Local partitioned index would lead to errors like below
SQL:labpa> alter index EMP_INFO_L_IDX drop partition EMP_INFO_P0;
alter index EMP_INFO_L_IDX drop partition EMP_INFO_P0
*
ERROR at line 1:
ORA-14076: submitted alter index partition/subpartition operation is not valid for local partitioned index
Local Index partitions are the most commonly used type of index partitions
Global Index:
A Global index has a one to many relationship, where a index or index partition can be mapped to multiple table partitions. A Global index can be either partitioned or non partitioned. By default Oracle creates all the indexes as Global Non partitioned index. In case of partitioned global indexes, each partition in the global index can be mapped to multiple underlying table partitions.
Global index has certain restrictions with respect to index management and maintenance. For instance, dropping a table partition causes the respective global index to become unusable. It also has certain edge over the local index such as we can define as many partitions as we want for a global index. We can also create a global partitioned index for a non partitioned Oracle table.
A simple excercise to help in choosing the index type for a partitioned table
Lets create a partitioned table for our exercise
SQL:labpa> CREATE TABLE emp_info
2 (
3 emp_id NUMBER NOT NULL,
4 join_date DATE NOT NULL,
5 email VARCHAR2(100)
6 )
7 PARTITION BY RANGE (join_date)
8 (
9 PARTITION emp_info_p0 VALUES LESS THAN (TO_DATE('01-JAN-2011', 'DD-MON-YYYY')) TABLESPACE APP_DATA,
10 PARTITION emp_info_p1 VALUES LESS THAN (TO_DATE('01-JAN-2012', 'DD-MON-YYYY')) TABLESPACE APP_DATA,
11 PARTITION emp_info_p2 VALUES LESS THAN (TO_DATE('01-JAN-2013', 'DD-MON-YYYY')) TABLESPACE APP_DATA,
12 PARTITION emp_info_p3 VALUES LESS THAN (TO_DATE('01-JAN-2014', 'DD-MON-YYYY')) TABLESPACE APP_DATA,
13 PARTITION emp_info_p4 VALUES LESS THAN (TO_DATE('01-JAN-2015', 'DD-MON-YYYY')) TABLESPACE APP_DATA
14 );
Table created.
We have created a table EMP_INFO with five partitions to hold records for the year 2010, 2011, 2012, 2013 and 2014 on separate partitions.
Now, Lets load some data into the table
SQL:labpa> declare
2 begin
3 for i in 1..100000
4 loop
5 insert into emp_info values (i,'13-APR-2010','xyz'||i);
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL:labpa> declare
2 begin
3 for i in 100001..200000
4 loop
5 insert into emp_info values (i,'13-APR-2011','xyz'||i);
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL:labpa> declare
2 begin
3 for i in 200001..300000
4 loop
5 insert into emp_info values (i,'13-APR-2012','xyz'||i);
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL:labpa> declare
2 begin
3 for i in 300001..400000
4 loop
5 insert into emp_info values (i,'13-APR-2013','xyz'||i);
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL:labpa> declare
2 begin
3 for i in 400001..500000
4 loop
5 insert into emp_info values (i,'13-APR-2014','xyz'||i);
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL:labpa> commit;
Commit complete.
SQL:labpa> select count(*) from emp_info;
COUNT(*)
----------
500000
SQL:labpa> select TABLE_NAME,PARTITION_NAME,NUM_ROWS from user_tab_partitions where table_name='EMP_INFO';
TABLE_NAME PARTITION_NAME NUM_ROWS
--------------- ---------------- ----------
EMP_INFO EMP_INFO_P0 100000
EMP_INFO EMP_INFO_P1 100000
EMP_INFO EMP_INFO_P2 100000
EMP_INFO EMP_INFO_P3 100000
EMP_INFO EMP_INFO_P4 100000
Now, we have 500000 records distributed evenly across the table partitions. We are now good to start with our analysis
Remember: It is always not necessary to create index for a table. We need to create index based on the query that is operating on the data.
Index is best suited, when we are querying a small section of data out of a large data set.
Okay, now coming back to the exercise. Lets query the table with the partition key (join_date) being as the query predicate
SQL:labpa> explain plan for select * from emp_info where join_date='13-APR-2010';
Explained.
SQL:labpa> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 1181328030
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 187K| 13M| 231 (4)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 187K| 13M| 231 (4)| 00:00:01 | 1 | 1 |
|* 2 | TABLE ACCESS FULL | EMP_INFO | 187K| 13M| 231 (4)| 00:00:01 | 1 | 1 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
2 - filter("JOIN_DATE"=TO_DATE(' 2010-04-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
18 rows selected.
In this example, we are querying all the rows from a single partition and as can bee seen in the explain plan; Oracle performed partition elimination based on the partition key (provided as query predicate). If we are always performing query like this, where it selects a large set of data from a particular partition based on the partition key, we do not need an index at all. Oracle will take advantage of partition elimination by means of partition pruning and will only scan the required partitions for the requested set of records.
However, if we just want to query a small set of a records from a particular partition, lets say the records with EMP_ID less than 100 and with JOIN_DATE as 13-APR-2010 (i.e. from 1st partition)
SQL:labpa> explain plan for select * from emp_info where emp_id <100 and join_date='13-APR-10';
Explained.
SQL:labpa> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 2926039120
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 666 | 267 (17)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 9 | 666 | 267 (17)| 00:00:01 | KEY | KEY |
|* 2 | TABLE ACCESS FULL | EMP_INFO | 9 | 666 | 267 (17)| 00:00:01 | KEY | KEY |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
2 - filter("EMP_ID"<100 AND "JOIN_DATE"='13-APR-10')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
18 rows selected.
An index might be useful here as we are just querying a small section of data from a single partition. We can implement a local index on each partition and then access the small set of records from that partition by the help of the index belonging to that partition.
SQL:labpa> create index EMP_INFO_IDX_LOCAL on emp_info (emp_id) LOCAL;
Index created.
SQL:labpa> explain plan for select * from emp_info where emp_id <100 and join_date='13-APR-10';
Explained.
SQL:labpa> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1798911101
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 7326 | 4 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 99 | 7326 | 4 (0)| 00:00:01 | KEY | KEY |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| EMP_INFO | 99 | 7326 | 4 (0)| 00:00:01 | KEY | KEY |
|* 3 | INDEX RANGE SCAN | EMP_INFO_IDX_LOCAL | 99 | | 3 (0)| 00:00:01 | KEY | KEY |
---------------------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("JOIN_DATE"='13-APR-10' AND "EMP_ID"<100)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
19 rows selected.
So, a Local index would be useful if we are querying a small set of partitions (based on the partition key as query predicate) from the table and then selecting a small set of records from each of those partitions.
However, what if we want to query a set of records and we are not using the partition key (as query predicate) in our query. Something like, query records with EMP_ID between 199910 and 200010.
If we use a Local Index here on the EMP_ID, Oracle is going to scan all the partitions (since we are not using partition key) and then going to use the Local index of those partitions to return the requested set of records.
SQL:labpa> explain plan for select * from emp_info where emp_id between 199910 and 200010;
Explained.
SQL:labpa> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1213506883
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101 | 7474 | 7 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 101 | 7474 | 7 (0)| 00:00:01 | 1 | 5 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| EMP_INFO | 101 | 7474 | 7 (0)| 00:00:01 | 1 | 5 |
|* 3 | INDEX RANGE SCAN | EMP_INFO_IDX_LOCAL | 101 | | 6 (0)| 00:00:01 | 1 | 5 |
---------------------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP_ID">=199910 AND "EMP_ID"<=200010)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
19 rows selected.
So, there is a overhead of visiting all the partitions and then scanning individual partitions based on their Local index for the requested set of records.
What we can do instead is, implement a Global index on EMP_ID. In this way Oracle will directly access the record from the table based on the Global Index, irrespective of the table partitioning.
SQL:labpa> create index EMP_INFO_IDX_GLOBAL on EMP_INFO (EMP_ID) GLOBAL;
Index created.
SQL:labpa> explain plan for select * from emp_info where emp_id between 199910 and 200010;
Explained.
SQL:labpa> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 425037682
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101 | 7474 | 4 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| EMP_INFO | 101 | 7474 | 4 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | EMP_INFO_IDX_GLOBAL | 101 | | 3 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
2 - access("EMP_ID">=199910 AND "EMP_ID"<=200010)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
18 rows selected.
So, if our queries are selecting a small subset of data from the entire table, we can implement a Global index on the query predicate to directly access the set of records from that table
I have used non partitioned Global Index for this example. Just to note that, you also have a option to create partitioned Global Index, where you can partition the Global Index to store the index values on separate index partitions and can also map a single index partition to single or multiple table partition.
For example, if I want to use partitioned Global index for the partitioned table EMP_INFO and want to store index for the 200000 records in a single partition and the remaining records in a different partition. I can use a Global Partitioned Index as below
SQL:labpa> create index EMP_INFO_IDX_GLOBAL on emp_info (emp_id) global
2 PARTITION by range (emp_id)
3 (
4 PARTITION emp_info_idx_pg1 VALUES LESS THAN (200000) TABLESPACE APP_DATA,
5 PARTITION emp_info_idx_pg2 VALUES LESS THAN (MAXVALUE) TABLESPACE APP_DATA
6 );
Index created.
However, Oracle doesn't manage the partitioned Global index automatically and manual intervention is required for adding or dropping partitions.
We can implement partitioned Global Index to avoid scanning the entire Index.
Following is the example, where I am using a global partitioned Index.
SQL:labpa> explain plan for select * from emp_info where emp_id<100 and join_date='13-APR-2010';
Explained.
SQL:labpa> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3498262492
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 2178 | 4 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 99 | 2178 | 4 (0)| 00:00:01 | 1 | 1 |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| EMP_INFO | 99 | 2178 | 4 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | EMP_INFO_IDX_GLOBAL | 20 | | 3 (0)| 00:00:01 | 1 | 1 |
-----------------------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("JOIN_DATE"=TO_DATE(' 2010-04-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
3 - access("EMP_ID"<100)
16 rows selected.
So, when I use a partitioned Global Index, the query optimizer only scans the partition (s) to which the index value belongs to.
A Global Index is particularly more suitable when direct access to the table records are desired without visiting the underlying table partitions.
Conclusion:
Deciding on the Local or Global index is purely based on the Query requirement as well as the on the expected Index management or maintenance.
With respect to Index maintenance, Local Indexes are easy to manage as they are independent of each other and Oracle itself takes care of the partition creation and deletion for Local Indexes at the event of table partition creation or deletion.
On the other hand Global Indexes are bit complex to manage during index maintenance. For instance, dropping a table partition makes the respective Global Index UNUSABLE and we have to rebuild the whole index.
SQL:labpa> alter table EMP_INFO drop partition emp_info_p0;
Table altered.
SQL:labpa> select index_name,partition_name,status from dba_ind_partitions where index_name='EMP_INFO_IDX_GLOBAL';
INDEX_NAME PARTITION_NAME STATUS
------------------------- ---------------- --------
EMP_INFO_IDX_GLOBAL EMP_INFO_IDX_PG1 UNUSABLE
EMP_INFO_IDX_GLOBAL EMP_INFO_IDX_PG2 UNUSABLE
We do have a option to use the "UPDATE GLOBAL INDEXES" clause while dropping a table partition to avoid the Global Index become Unusable. However, it would be a time consuming operation to drop the partition in case of large Global Indexes as it involves rebuilding the Global Index during the process of drop partition operation.
SQL:labpa> alter table EMP_INFO drop partition EMP_INFO_P1 UPDATE GLOBAL INDEXES;
Table altered.
SQL:labpa> select index_name,partition_name,status from dba_ind_partitions where index_name='EMP_INFO_IDX_GLOBAL';
INDEX_NAME PARTITION_NAME STATUS
------------------------- ---------------- --------
EMP_INFO_IDX_GLOBAL EMP_INFO_IDX_PG1 USABLE
EMP_INFO_IDX_GLOBAL EMP_INFO_IDX_PG2 USABLE
Therefore, there are a number of factors to evaluate before deciding on the type of Index (Local or Global) to be used for a partitioned table.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8520577/viewspace-2093174/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 分割槽表本地索引與全域性索引的區別索引
- 分割槽表 全域性索引與本地索引失效測試索引
- 分割槽表、分割槽索引和全域性索引部分總結索引
- [轉]Oracle分割槽索引--本地索引和全域性索引比較Oracle索引
- Oracle分割槽索引--本地索引和全域性索引比較（轉）Oracle索引
- 全域性分割槽索引和區域性分割槽索引索引
- 分割槽索引之本地(local index)索引和全域性索引(global index)索引Index
- 分割槽表中的區域性分割槽索引及全域性索引與執行計劃索引
- 分割槽索引和全域性索引(轉載)索引
- ddl 導致分割槽表全域性索引unusable索引
- Oracle分割槽表全域性索引新增分割槽時不會失效Oracle索引
- 分割槽表中全域性及本地分割槽索引什麼時候會失效及處理索引
- 淺談索引系列之本地索引與全域性索引索引
- 分割槽索引：區域性 locally & 全域性 global索引
- 刪除分割槽需要更新全域性索引索引
- 分割槽表中全域性及本地分割槽索引什麼時候會失效及處理[final]索引
- Oracle全域性索引和本地索引Oracle索引
- 本地索引、全域性索引、字首索引、非字首索引索引
- 在範圍分割槽表上分割槽維護操作對全域性及本地索引狀態的影響(2)索引
- 刪除分割槽更新全域性索引使用提醒索引
- 全域性索引和本地索引的比較索引
- 記一次Oracle分割槽表全域性索引重建的過程Oracle索引
- rebuild分割槽表分割槽索引的方法Rebuild索引
- Oracle分割槽表及分割槽索引Oracle索引
- 【學習筆記】分割槽表和分割槽索引——管理索引分割槽（四）筆記索引
- 本地索引和全域性索引的適用場景索引
- ORACLE10g新特性——全域性HASH分割槽索引Oracle索引
- 全面學習分割槽表及分割槽索引(17)--其它索引分割槽管理操作索引
- 簡單ORACLE分割槽表、分割槽索引Oracle索引
- 分割槽表及分割槽索引建立示例索引
- 全面學習分割槽表及分割槽索引(15)--修改表分割槽屬性和模板索引
- Oracle帶區域性分割槽索引的分割槽表刪除舊分割槽新增新分割槽Oracle索引
- oracle本地分割槽索引跨分割槽對成本的影響Oracle索引
- 全面學習分割槽表及分割槽索引(16)--增加和刪除索引分割槽索引
- 分割槽表分割槽索引查詢效率探究索引
- 事務、全域性索引、透明分散式，再見，分割槽健！索引分散式
- 分割槽表的分割槽資料刪除、truncate會對全域性和區域性索引產生什麼影響索引
- 全面學習分割槽表及分割槽索引(13)--分隔表分割槽索引