Partial Indexes Trilogy – Part 2: Global Partial Indexes

Posted on by

Partial Indexes, introduced with Oracle 12c, are available for local and global indexes. In part 2 of this blog post trilogy, let’s focus on global partial indexes on a partitioned table.

Whenever possible, I try to avoid global indexes in Oracle Data Warehouse environment. Perhaps I revise my opinion when I can use partial indexes? At least it is worth to look at this feature in detail.

As already mentioned in Partial Indexes Trilogy – Part 1: Local Partial Indexes, there are several good reasons to create partial indexes. A partial index is an index on a partitioned table that is created only on some of the partitions. The examples in the previous blog post were based on the following use case: We want to create partial indexes on the newest two partitions, but not on all previous created history partitions.

CREATE TABLE sales
(dwh_id NUMBER NOT NULL
,prod_id NUMBER NOT NULL
,cust_id NUMBER NOT NULL
,time_id DATE NOT NULL
,qty_sold NUMBER NOT NULL
,amt_sold NUMBER NOT NULL)
INDEXING OFF
PARTITION BY RANGE (time_id)
(PARTITION p_2016_01 VALUES LESS THAN (TO_DATE('2016-02-01', 'YYYY-MM-DD'))
,PARTITION p_2016_02 VALUES LESS THAN (TO_DATE('2016-03-01', 'YYYY-MM-DD'))
,PARTITION p_2016_03 VALUES LESS THAN (TO_DATE('2016-04-01', 'YYYY-MM-DD'))
,PARTITION p_2016_04 VALUES LESS THAN (TO_DATE('2016-05-01', 'YYYY-MM-DD'))
,PARTITION p_2016_05 VALUES LESS THAN (TO_DATE('2016-06-01', 'YYYY-MM-DD')) INDEXING ON
,PARTITION p_2016_06 VALUES LESS THAN (TO_DATE('2016-07-01', 'YYYY-MM-DD')) INDEXING ON
);

For local indexes, only the required index partitions are physically created. For all other partitions, the corresponding partitions are set to UNUSABLE. But how does Oracle handle global indexes when they are created as a partial index?

Global Partial Indexes

A global index is an index on a partitioned table that is either non-partitioned or – rather rarely used – partitioned by another partition key or method than the underlying table. If the table is defined for partial indexes as in the example above, it is feasible to create Global Partial Indexes on this table. For a Global Partial Index, only the ROWIDs referring partitions with INDEXING ON are stored in the index. For all other partitions, nothing is stored in the global index.

Global Partial Index

Unique Partial Index?

The most common reason of a global index is a unique index on a partitioned table that does not contain the partition key (e.g. for a primary key or a unique constraint). In this case, a local index cannot be used. But how about a Global Partial Index?

CREATE UNIQUE INDEX sales_pk_global_partial_idx
ON sales (dwh_id) INDEXING PARTIAL;
   *
ERROR at line 2:
ORA-14226: unique index may not be PARTIAL

The error message is clear: A unique index may not be defined as partial index. Because the uniqueness must be guaranteed on all rows of all existing partitions, it is not allowed to skip some of the partitions in the index. Therefore, it is obvious that a unique partial index is not supported. So, we have to create our unique index as a regular global index.

CREATE UNIQUE INDEX sales_pk_global_idx
ON sales (dwh_id);

Index created.

Non-Unique Partial Index

Non-unique B-tree indexes can be created with the option INDEXING PARTIAL, even when they are global. For our demo case, we create a global partial index on column CUST_ID:

CREATE INDEX sales_cust_global_partial_idx
ON sales (cust_id) INDEXING PARTIAL;

Index created.

Because only the ROWIDs for the partitions with INDEXING ON are stored in the index, the index contains only a subset of the rows. Our example table contains 100000 rows, but only 32568 of them are stored in the partitions P_2016_05 and P_2016_06. Therefore, the partial index SALES_CUST_GLOBAL_PARTIAL_IDX contains only references to these rows. However, the unique index SALES_PK_GLOBAL_IDX consists of all 100000 rows.

SELECT index_name, index_type, status, num_rows, orphaned_entries
FROM user_indexes
WHERE table_name = 'SALES';
 
INDEX_NAME                    INDEX_TYPE STATUS  NUM_ROWS ORPHANED_ENTRIES
————————————————————————————— —————————— ——————— ———————— --------------------------------
SALES_PK_GLOBAL_IDX           NORMAL     VALID     100000 NO
SALES_CUST_GLOBAL_PARTIAL_IDX NORMAL     VALID      32568 NO

Since my last blog post in end of June, a new month has started. So, we want to be able to store new sales data for the current month and add a new partition P_2016_07 to our SALES table. July is very successful until now, so we are already able to load another 10000 sales transactions (at least in this fictitious example case).

ALTER TABLE sales
ADD PARTITION p_2016_07
VALUES LESS THAN (TO_DATE('2016-08-01', 'YYYY-MM-DD’));
 
INSERT /*+ append */ INTO sales
SELECT ROWNUM + 100000 dwh_id
     , MOD(ROWNUM, 1000) + 1 prod_id
     , MOD(ROWNUM, 5000) + 1 cust_id
     , TO_DATE('2016-07-01', 'YYYY-MM-DD') + MOD(ROWNUM, 30) time_id
     , MOD(ROWNUM, 20) qty_sold
     , MOD(ROWNUM, 1000) amt_sold
  FROM dual CONNECT BY ROWNUM <= 10000;
 
COMMIT;

After gathering new table and index statistics, let’s have a look at the statistics of the two global indexes.

exec dbms_stats.gather_table_stats(USER, 'SALES', cascade => TRUE); 
SELECT index_name, index_type, status, num_rows, orphaned_entries
FROM user_indexes
WHERE table_name = 'SALES';
 
INDEX_NAME                    INDEX_TYPE STATUS  NUM_ROWS ORPHANED_ENTRIES
————————————————————————————— —————————— ——————— ———————— --------------------------------
SALES_PK_GLOBAL_IDX           NORMAL     VALID     110000 NO
SALES_CUST_GLOBAL_PARTIAL_IDX NORMAL     VALID      32568 NO

The unique index SALES_PK_GLOBAL_IDX now contains 110000 rows (100000 rows for January to June, 10000 new rows for July). But the partial index SALES_CUST_GLOBAL_PARTIAL_IDX has still the same number of rows: 32568.

The default for partial index on table level is defined with INDEXING OFF in our example (see above). So, all new partitions are created with this setting, unless an explicit INDEXING ON is defined. Currently, our partial index still contains the ROWIDs for partitions P_2016_05 and P_2016_06 – and nothing else.

SELECT table_name, partition_name, num_rows, indexing
FROM user_tab_partitions
WHERE table_name = 'SALES'
ORDER BY partition_name;
TABLE_NAME  PARTITION_NAME NUM_ROWS INDEXING
---------------------- ---------------------------- ---------------- ——--------——
SALES       P_2016_01         17887 OFF
SALES       P_2016_02         16211 OFF
SALES       P_2016_03         17329 OFF
SALES       P_2016_04         16767 OFF
SALES       P_2016_05         17298 ON
SALES       P_2016_06         14508 ON
SALES       P_2016_07         10000 OFF 

Turn Indexing On and Off

In the original use case, our requirement was a partial index on the newest two partitions. After adding a new partition, we have to move this time frame from May/June to June/July. For this, we set partition P_2016_07 to INDEXING ON and partition P_2016_05 to INDEXING OFF. Here, we do it step by step to look in detail what the impact is on a partial index.

As a first step, we set the new partition to INDEXING ON. A query on USER_SEGMENTS before and after this DDL statement shows the size of the two indexes. While the index SALES_PK_GLOBAL_IDX is not changed (of course, it is not a partial index), the index SALES_CUST_GLOBAL_PARTIAL_IDX grows because new rows for partition P_2016_07 are inserted.

SELECT segment_name, bytes
  FROM user_segments
 WHERE segment_name LIKE 'SALES%IDX';
 
SEGMENT_NAME                   BYTES
------------------------------------------------------------ --------------------
SALES_CUST_GLOBAL_PARTIAL_IDX      786432
SALES_PK_GLOBAL_IDX               3145728
ALTER TABLE sales
MODIFY PARTITION p_2016_07 INDEXING ON;
SELECT segment_name, bytes
  FROM user_segments
 WHERE segment_name LIKE 'SALES%IDX';
 
SEGMENT_NAME                   BYTES
------------------------------------------------------------ --------------------
SALES_CUST_GLOBAL_PARTIAL_IDX     2097152
SALES_PK_GLOBAL_IDX               3145728

Take care with using ALTER TABLE … INDEXING ON. For a table with large partitions and many partial indexes (global or local), this may need some time to build or update all the partial indexes.

Back to our use case: After gathering the statistics again, we can see that the number of rows in the partial index increased by 10000 compared to the the query above.

exec dbms_stats.gather_table_stats(USER, 'SALES', cascade => TRUE); 
SELECT index_name, index_type, status, num_rows, orphaned_entries
FROM user_indexes
WHERE table_name = 'SALES';
 
INDEX_NAME                    INDEX_TYPE STATUS  NUM_ROWS ORPHANED_ENTRIES
————————————————————————————— —————————— ——————— ———————— --------------------------------
SALES_PK_GLOBAL_IDX           NORMAL     VALID     110000 NO
SALES_CUST_GLOBAL_PARTIAL_IDX NORMAL     VALID      42568 NO

As a second step, we turn INDEXING OFF for partition P_2016_05 and run the same queries again. The number of rows in the index is smaller after re-gathering the statistics, but the size of the partial index does not decrease. Although, we can see another interesting detail: the value of column ORPHAN_ENTRIES switched from NO to YES. This has to do with another interesting Oracle 12c feature: Asynchronous Global Index Maintenance.

ALTER TABLE sales
MODIFY PARTITION p_2016_05 INDEXING OFF;
SELECT segment_name, bytes
  FROM user_segments
 WHERE segment_name LIKE 'SALES%IDX';
 
SEGMENT_NAME                   BYTES
------------------------------------------------------------ --------------------
SALES_CUST_GLOBAL_PARTIAL_IDX     2097152
SALES_PK_GLOBAL_IDX               3145728
exec dbms_stats.gather_table_stats(USER, 'SALES', cascade => TRUE); 
SELECT index_name, index_type, status, num_rows, orphaned_entries
FROM user_indexes
WHERE table_name = 'SALES';
 
INDEX_NAME                    INDEX_TYPE STATUS  NUM_ROWS ORPHANED_ENTRIES
————————————————————————————— —————————— ——————— ———————— --------------------------------
SALES_PK_GLOBAL_IDX           NORMAL     VALID     110000 NO
SALES_CUST_GLOBAL_PARTIAL_IDX NORMAL     VALID      25456 YES

Asynchronous Global Index Maintenance and Partial Indexes

Asynchronous Global Index Maintenance was introduced with Oracle 12c to avoid an index rebuild of a global index when a partition is dropped or truncated. With the optional clause UPDATE INDEXES, the global index is not set to USUSABLE, but is still VALID. All ROWIDs referring to the non-existing partition remain in the index, but are marked as orphans. With an asynchronously executed cleanup job, these orphaned entries are purged from the index. A separate blog post for this feature would be interesting, too. But instead of writing another long post, I better recommend the posts about 12c Asynchronous Global Index Maintenance on Richard Foote’s Oracle Blog:

The interesting fact for our example here is that Oracle uses this feature for Global Partial Indexes, too. When a partition is changed to INDEXING OFF and a global partial index exists on the table, the index stays VALID, and the orphaned entries are purged with the scheduler job SYS.PMO_DEFERRED_GIDX_MAINT_JOB or the procedure call of DBMS_PART.CLEANUP_GIDX.

Just for interest: What happens with the global partial index if we drop a partition that is set to INDEXING OFF? Let’s try this by dropping the January partition of the SALES table:

ALTER TABLE sales
DROP PARTITION p_2016_01;
SELECT index_name, index_type, status, orphaned_entries
FROM user_indexes
WHERE table_name = 'SALES';

INDEX_NAME                    INDEX_TYPE STATUS   ORPHANED_ENTRIES
————————————————————————————— —————————— ———————- --------------------------------
SALES_PK_GLOBAL_IDX           NORMAL     UNUSABLE NO
SALES_CUST_GLOBAL_PARTIAL_IDX NORMAL     UNUSABLE NO

Both global indexes are UNUSABLE. For the unique index (which is not partial), this is clear. But for the partial index, Oracle knows that no rows of the dropped partition are stored in the index, so there is actually no need to change the index. However, the index must be rebuilt.

Fortunately, this is not a real issue. When the partition is dropped with the UPDATE INDEXES clause, it works perfectly:

ALTER TABLE sales
DROP PARTITION p_2016_01
UPDATE INDEXES; 
SELECT index_name, index_type, status, orphaned_entries
FROM user_indexes
WHERE table_name = 'SALES';

INDEX_NAME                    INDEX_TYPE STATUS   ORPHANED_ENTRIES
————————————————————————————— —————————— ———————- --------------------------------
SALES_PK_GLOBAL_IDX           NORMAL     VALID  YES
SALES_CUST_GLOBAL_PARTIAL_IDX NORMAL     VALID  NO

Both global indexes are VALID. The unique index contains orphaned entries that are purged with the next cleanup job. For the partial index, no orphaned entries are marked because the partition is set to INDEXING OFF. So, no cleanup is required in this case.

Partial Indexes Trilogy

Leave a comment