A partitioned index in Oracle 11g is simply an index
broken into multiple pieces. By breaking an index into multiple physical
pieces, you are accessing much smaller pieces (faster), and you may separate
the pieces onto different disk drives (reducing I/O contention).
Both b-tree
and bitmap indexes can be partitioned. Hash indexes cannot be partitioned
There are two types of partitioned indexes: local and
global. Each type has two subsets, prefixed and non-prefixed. A table can have
any number or combination of the different types of indexes built on its
columns.
If bitmap indexes are used, they must be local indexes. The main
reason to partition the indexes is to reduce the size of the index that needs
to be read and to enable placing the partitions in separate tablespaces to
improve reliability and availability.
Oracle also supports parallel query and parallel DML when
using partitioned tables and indexes, adding the extra benefit of multiple
processes helping to process the statement faster.
- Local indexes are indexes that are partitioned using the same partition key and same range boundaries as the partitioned table.
- Each partition of local indexes are associated with that partition
- Each partition of a local index will only contain keys and ROWIDs from its corresponding table partition.
- Local indexes can be b-tree or bitmap indexes. If they are b-tree indexes, they can be unique or non-unique.
- Local indexes support partition independence, meaning that individual partitions can be added, truncated, dropped, split, taken offline, etc., without dropping or rebuilding the indexes. When we take any action (MERGE, SPLIT, EXCHANGE etc) on local partition, it impacts only that partition and other partition will be available.
- Oracle maintains the local indexes automatically. Local index partitions can also be rebuilt individually while the rest of the partition is unaffected.
- We cannot explicitly add local index to new partition. Local index will be added implicitly to new partition when we add new partition on table. Likewise, we cannot drop the local index on specific partition. It can be dropped automatically when we drop the partition from underlying table.
SQL> CREATE TABLE employees
(employee_id NUMBER(4) NOT NULL,
last_name VARCHAR2(10),
department_id NUMBER(2))
PARTITION BY RANGE (department_id)
(PARTITION employees_part1 VALUES LESS THAN (10) TABLESPACE ODS_STAGE_DATA,
PARTITION employees_part2 VALUES LESS THAN (20) TABLESPACE ODS_STAGE_DATA,
PARTITION employees_part3 VALUES LESS THAN (30) TABLESPACE ODS_STAGE_DATA);
Table created.
SQL>
SQL> create index idx_local on employees(last_name) local;
Index created.
SQL>select INDEX_NAME,INDEX_type ,GLOBAL_STATS from user_indexes;
SQL>select index_name, partition_name from user_ind_partitions
For example, . Say the table was created and range-partitioned using the SURVEY_ID and SURVEY_DATE columns and a local prefixed index is created on the SURVEY_ID column. The partitions of the index are
equipartitioned, meaning the partitions of the index are created with the same range boundaries as those of the table (see Figure 1).
Local prefixed indexes allow Oracle to prune unneeded partitions quickly. The partitions that do not contain any of the values appearing in the WHERE clause will not need to be accessed, thus improving the statement’s performance
Non-Prefixed: For a non-prefixed index to be unique, it must contain a
subset of the partitioning key.
In this example, you would need a combination
of columns, including the SURVEY_DATE and/or the SURVEY_ID columns (as long as
the SURVEY_ID column was not the leading edge of the index, in which case it
would be a prefixed index).
TIP
For a non-prefixed index to be unique, it must contain a
subset of the partitioning key.
Global Index:
Global partitioned indexes contain keys from multiple table
partitions in a single index partition. The partitioning key of a global
partitioned index is different or specifies a different range of values from
the partitioned table. The creator of the global partitioned index is
responsible for defining the ranges and values for the partitioning key. Global
indexes can only be b-tree indexes. Global partitioned indexes are not
maintained by Oracle by default.
If a partition is truncated, added, split,
dropped, etc., the global partitioned indexes need to be rebuilt unless you
specify the UPDATE GLOBAL INDEXES clause of the ALTER TABLE command when
modifying the table.
Global
partitioned index partition key is independent of Table partition key.
The
highest partition of a global index must have a partition bound, all whose
values are MAXVALUE.
You
cannot drop the highest partition in a global index
If
you want to add new partition, always, you need to split the MAX partition
- Prefixed Normally, global prefixed indexes are not equipartitioned with the underlying table. Nothing prevents the index from being equipartitioned, but Oracle does not take advantage of the equipartitioning when generating query plans or executing partition maintenance operations. If the index is going to be equipartitioned, it should be created as a local index to allow Oracle to maintain the index and use it to help prune partitions that will not be needed (see Figure 3). As shown in the figure, the three index partitions each contain index entries that point to rows in multiple table partitions.
Figure 3. Partitioned, global
prefixed index
TIP
If a global index is going to be equipartitioned, it should
be created as a local index to allow Oracle to maintain the index and use it to
help prune partitions, or exclude those partitions that are not needed by the
query.
- Non-prefixed Global non-prefixed indexes should not be used as Oracle does not support them. They do not provide any benefits over normal B-tree indexes on the same columns, so they have no value.
SQL> CREATE INDEX employees_global_idx ON employees(employee_id);
Index created.
SQL>
Example of Global Partitioned index .
SQL> CREATE INDEX employees_global_part_idx ON employees(employee_id)
GLOBAL PARTITION BY RANGE(employee_id)
(PARTITION p1 VALUES LESS THAN(3),
PARTITION p2 VALUES LESS THAN(6),
PARTITION p3 VALUES LESS THAN(9));
PARTITION p3 VALUES LESS THAN(9))
*
ERROR at line 5:
ORA-14021: MAXVALUE must be specified for all columns
SQL> CREATE INDEX employees_global_part_idx ON employees(employee_id)
GLOBAL PARTITION BY RANGE(employee_id)
(PARTITION p1 VALUES LESS THAN(3),
PARTITION p2 VALUES LESS THAN(6),
PARTITION p3 VALUES LESS THAN(11),
PARTITION p4 VALUES LESS THAN(20),
PARTITION p5 VALUES LESS THAN(MAXVALUE));
Index created.
Now the partition p4 is empty. Let us drop the empty partition and see the status.
SQL> select count(*) from employees where
2 employee_id between 12 and 20;
COUNT(*)
----------
0
SQL> ALTER index employees_global_part_idx drop partition p4;
Index altered.
SQL> SELECT partition_name,status from user_ind_partitions where
index_name='EMPLOYEES_GLOBAL_PART_IDX';
PARTITION_NAME STATUS
------------------------------ --------
P1 USABLE
P2 USABLE
P3 USABLE
P5 USABLE
Now we will drop the partition P3 and see status. When we drop this partition, it should invalidate the next highest partition. Here, P5 is next highest partition.
When I drop one index partition which makes the next highest partition to UNUSABLE status.
SQL> alter index employees_global_part_idx drop partition p3;
Index altered.
SQL> SELECT partition_name,status from user_ind_partitions where
index_name='EMPLOYEES_GLOBAL_PART_IDX';
PARTITION_NAME STATUS
------------------------------ --------
P1 USABLE
P2 USABLE
P5 UNUSABLE
SQL> alter index employees_global_part_idx rebuild;
alter index employees_global_part_idx rebuild
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole
n.b: You cannot rebuild the global index as whole.Rebuild need to be performed partition wise.
SQL> alter index employees_global_part_idx rebuild partition p5;
Index altered.
SQL> SELECT partition_name,status from user_ind_partitions where
2 index_name='EMPLOYEES_GLOBAL_PART_IDX';
PARTITION_NAME STATUS
------------------------------ --------
P1 USABLE
P2 USABLE
P5 USABLE
Partition index can be maintained by using UPDATE GLOBAL INDEXES. Index will be available during the maintenance and it is available online. We do not need to rebuild the index after the index maintenance.
For example,
SQL> alter table employees drop partition employees_part3
update global indexes;
Table altered.
SQL> SELECT partition_name,status from user_ind_partitions where
2 index_name='EMPLOYEES_GLOBAL_PART_IDX';
PARTITION_NAME STATUS
------------------------------ --------
P1 USABLE
P2 USABLE
P5 USABLE
SQL>
When would you force to create Global index on Partition table?
When you create a Primary key, you will be forced to create Global index. When you create unique index, you are forced to create global index. Enforcing uniqueness is most common reason for global indexes. If you try to create local index on unique key, you would get the below error.
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
So, we can create local index on unique key when we add the partition key as part of composite key in the index. Let me demonstrate this.
The below example, unique index failed since, partition key is not part of index composite key.
SQL> create unique index idx_emp_id on employees(employee_id) local;
create unique index idx_emp_id on employees(employee_id) local
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
The below example, it allowed to create global index.
SQL> create unique index idx_emp_id on employees(employee_id);
Index created.
SQL> drop index idx_emp_id;
Index dropped.
The below case, unique index is successfully created, since partition key department_id is part of composite keys.
SQL> create unique index idx_emp_id on employees(employee_id,department_id) local;
``
Index created.
SQL>
When would you recommend creating global index versus local index?
We can use Global index if Query that return a SMALL number of rows from a potentially LARGE number of partitions.
No comments:
Post a Comment