Create and manage partitioned indexes

Go back to OCM 12c Preparation Project 

Hands On Lab – Index

[1] Local Partitioned Indexes
[2] Global Partitioned Indexes
[3] Partial Indexes for Partitioned Tables
[4] Partitioned Indexes on Composite Partitions

Performance in OLTP environments heavily relies on efficient index access, 
thus the choice of the most appropriate index strategy becomes crucial. 
The following section discusses best practices for deciding whether to 
partition indexes in an OLTP environment.

1. Indexes on partitioned tables can either be nonpartitioned or partitioned.
As with partitioned tables, partitioned indexes improve manageability,
availability, performance, and scalability.

2. They can either be partitioned independently (global indexes) or 
automatically linked to a table's partitioning method (local indexes).

3. In general, you should use global indexes for OLTP applications and 
local indexes for data warehousing or decision support systems (DSS) 
applications.

##########-> Deciding on the Type of Partitioned Index to Use

When deciding what kind of partitioned index to use, 
you should consider the following guidelines in this order:

1. If the table partitioning column is a subset of the index keys, 
then use a local index. If this is the case, then you are finished. 
If this is not the case, then continue to guideline 2.

2. If the index is unique and does not include the partitioning key columns, 
then use a global index. If this is the case, then you are finished. 
Otherwise, continue to guideline 3.

3. If your priority is manageability, then consider a local index. 
If this is the case, then you are finished. If this is not the case, 
continue to guideline 4.

4. If the application is an OLTP type and users need quick response times, 
then use a global index. If the application is a DSS type and users are 
more interested in throughput, then use a local index.

[1] Local Partitioned Indexes

-> Local partitioned indexes support more availability when there are 
partition or subpartition maintenance operations on the table. 

A type of index called a local nonprefixed index is very useful for 
historical databases. In this type of index, the partitioning is not on 
the left prefix of the index columns.

-> You cannot explicitly add a partition to a local index. 
Instead, new partitions are added to local indexes
only when you add a partition to the underlying table. 
Likewise, you cannot explicitly drop a partition
from a local index. Instead, local index partitions are dropped only 
when you drop a partition from the underlying table.

-> A local index can be unique. However, in order for a local index to be
unique, the partitioning key of the table must be part of the index's key
columns.

To Create a local index on a partitioned table,add LOCAL clause to the end 
of the CREATE INDEX command. Local Index can be prefixed and non-prefixed.

In a prefixed local index, the partitioning key of the index appears as a 
leading column of the index key.
For example, if a table is partitioned on DDATE column and the local index 
is created on (DDATE,OBJECT_ID) then the index will be prefixed index.

SELECT COUNT(*) FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TBL_RANGE_PART';
COUNT(*)
----------
7

Crete a local partitioned index on the table and get the count of index partitions.

CREATE INDEX IDX_RANGE_PART ON TBL_RANGE_PART(ddate) LOCAL;

SELECT COUNT(*) FROM USER_IND_PARTITIONS WHERE INDEX_NAME='IDX_RANGE_PART';

COUNT(*)
----------
7

The local index that doesn't include the partitioning key as a leading 
column of the index key is known as nonprefixed index.

It can be created on any column. but each partition of the index only 
contains the keys for the corresponding partition of the table.

Below, We crete a local nonprefixed index on table TBL_RANG_PART for the 
column ID.

CREATE INDEX IDX_RANGE_PART2 ON TBL_RANGE_PART(ID) LOCAL;

SELECT COUNT(*) FROM USER_IND_PARTITIONS WHERE INDEX_NAME='IDX_RANGE_PART2';

COUNT(*)
----------
7

[2] Global Partitioned Indexes

The Partitioning key of the global index is different from the partitioned 
key of the table or specifies a different range of values.

They are not maintained by oracle and if the partition is dropped or 
truncated. The global partitioned index need to be rebuilt unless you 
specify UPDATE GLOBAL INDEXES clause when modifying the table.

SQL> CREATE TABLE tbl_part_range_global_idx
2 PARTITION BY RANGE
3 (object_id)
4 ( partition part1 values less than (1000),
5 partition part2 values less than (4000),
6 partition part3 values less than (maxvalue))
7 as
8 select * from dba_objects;

Table created.

SQL> create index idx_part_range_global_1
2 on tbl_part_range_global_idx (object_id)
3 global partition by range (object_id)
4 ( partition part1 values less than (100),
5 partition part2 values less than (200),
6 partition part3 values less than (maxvalue));

Index created.

SQL> ALTER INDEX IDX_PART_RANGE_GLOBAL_1 REBUILD ;
ALTER INDEX IDX_PART_RANGE_GLOBAL_1 REBUILD
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole

SQL> ALTER INDEX IDX_PART_RANGE_GLOBAL_1 REBUILD PARTITION part3;

Index altered.

-> Use the following command to rebuilt all indexes of the specific 
partition with the status UNUSABLE.

SQL> ALTER TABLE tbl_part_range_global_idx
MODIFY PARTITION part1 REBUILD UNUSABLE LOCAL INDEXES;

Table altered.

HOL
---
Example 7-1 Creating a unique index and primary key constraint

CREATE UNIQUE INDEX orders_pk
ON orders_oltp(order_id)
GLOBAL PARTITION BY HASH (order_id)
( PARTITION p1 TABLESPACE tbs1
, PARTITION p2 TABLESPACE tbs2
, PARTITION p3 TABLESPACE tbs3
, PARTITION p4 TABLESPACE tbs4
) NOLOGGING;

ALTER TABLE orders_oltp ADD CONSTRAINT orders_pk
PRIMARY KEY (order_id)
USING INDEX;

Global Range Partitioned Indexes
Global Hash Partitioned Indexes
Maintenance of Global Partitioned Indexes
Global Nonpartitioned Indexes
Miscellaneous Information about Creating Indexes on Partitioned Tables

[3] Partial Indexes for Partitioned Tables

You can create local and global indexes on a subset of the partitions of 
a table,enabling more flexibility in index creation. This feature is 
supported using a default table indexing property.

When a table is created or altered, a default indexing property can be 
specified for the table or its partitions. The table indexing property 
is only considered for partial indexes.

When an index is created as PARTIAL on a table:

Local indexes:
-> An index partition is created usable if indexing is turned on for the 
table partition,and unusable otherwise.

-> You can override this behavior by specifying USABLE/UNUSABLE at the 
index or index partition level.

Global indexes:
-> Includes only those partitions for which indexing is turned on, and 
exclude the others.

This feature is not supported for unique indexes, or for indexes used for 
enforcing unique constraints. FULL is the default if neither FULL nor 
PARTIAL is specified.

CREATE TABLE orders_partial (
order_id NUMBER(12),
order_date DATE CONSTRAINT order_date_nn NOT NULL,
order_mode VARCHAR2(8),
customer_id NUMBER(6) CONSTRAINT order_customer_id_nn NOT NULL,
order_status NUMBER(2),
order_total NUMBER(8,2),
sales_rep_id NUMBER(6),
promotion_id NUMBER(6),
CONSTRAINT order_mode_lov CHECK (order_mode in ('direct','online')),
CONSTRAINT order_total_min CHECK (order_total >= 0))
INDEXING OFF
PARTITION BY RANGE (ORDER_DATE)
(PARTITION ord_p1 VALUES LESS THAN (TO_DATE('01-MAR-1999','DD-MON-YYYY'))
INDEXING ON,
PARTITION ord_p2 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY'))
INDEXING OFF,
PARTITION ord_p3 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY'))
INDEXING ON,
PARTITION ord_p4 VALUES LESS THAN (TO_DATE('01-MAR-2000','DD-MON-YYYY')),
PARTITION ord_p5 VALUES LESS THAN (TO_DATE('01-MAR-2010','DD-MON-YYYY')));

A local or global partial index, can be created to follow the table 
indexing properties of the previous
SQL example by specification of the INDEXING PARTIAL clause.

CREATE INDEX ORDERS_ORDER_TOTAL_GIDX ON ORDERS (ORDER_TOTAL)
GLOBAL INDEXING PARTIAL;

The ORDERS_ORDER_TOTAL_GIDX index is created to index only those partitions 
that have INDEXING ON, and excludes the remaining partitions.

Updates to views include the following:

1. Table Indexing Property - The column INDEXING is added to *_PART_TABLES, 
*_TAB_PARTITIONS,and *_TAB_SUBPARTITIONS views.

2. This column has one of two values ON or OFF, specifying indexing on or 
indexing off.

3. Partial Global Indexes as an Index Level Property - A new column 
INDEXING is added to the USER_INDEXES view. This column can be set to 
FULL or PARTIAL.

4. Partial Global Index Optimization - The column ORPHANED_ENTRIES is added 
to the dictionary views USER_INDEXES and USER_IND_PARTITIONS to represent 
if a global index (partition) contains stale entries owing to deferred 
index maintenance during DROP/TRUNCATE PARTITION, or
MODIFY PARTITION INDEXING OFF. The column can have one of three values:

YES => the index (partition) contains orphaned entries
NO => the index (partition) does not contain any orphaned entries

[4] Partitioned Indexes on Composite Partitions
-> Here are a few points to remember when using partitioned indexes on 
composite partitions:
1. Subpartitioned indexes are always local and stored with the table 
   subpartition by default.
2. Tablespaces can be specified at either index or index subpartition levels.

Database VLDB and Partitioning Guide
3 Partitioning for Availability, Manageability, and Performance
--> Index Partitioning

The rules for partitioning indexes are similar to those for tables:

1. An index can be partitioned unless:
-> The index is a cluster index.
-> The index is defined on a clustered table.

2. You can mix partitioned and nonpartitioned indexes with partitioned 
   and nonpartitioned tables:
-> A partitioned table can have partitioned or nonpartitioned indexes.
-> A nonpartitioned table can have partitioned or nonpartitioned indexes.

3. Bitmap indexes on nonpartitioned tables cannot be partitioned.
4. A bitmap index on a partitioned table must be a local index.

5. However, partitioned indexes are more complicated than partitioned tables 
because there are three types of partitioned indexes:

Local prefixed
Local nonprefixed
Global prefixed

Oracle Database supports all three types. However, there are some restrictions.
For example, a key cannot be an expression when creating a local unique 
index on a partitioned table.

======================
Path to Documentation:
======================
Database VLDB and Partitioning Guide
2 Partitioning Concepts
--> Indexing on Partitioned Tables
3 Partitioning for Availability, Manageability, and Performance
--> Index Partitioning

Thank you for visiting this blog 🙂