Sometimes it would be useful to create indexes for only some of the partitions of a partitioned table. Oracle 12c supports this possibility with Partial Indexes. This blog post shows how local partial indexes can be created – even on an Oracle 11g database.
There are several use cases where it would be practical to create indexes on only a subset of the partitions of a table. For example, we want to create an index not before all data is loaded into a partition. Let’s assume the following situation: A monthly partitioned table is loaded with daily loads. To improve load performance, we want to defer the index creation until all data is loaded into a partition. When I’m writing this blog post (end of June), most of the data is already loaded into the June 2016 partition. In a few days (beginning of July), a new partition is added for July 2016, and the indexes for the June partition are created.
Another use case needs the opposite: For the current and the previous month, we run many selective queries on our partitioned table and therefore want to have a bunch of indexes on the table for all possible combinations (bitmap indexes are perfect for this, but that’s another story). For all historical partitions, we are interested only in aggregated data for each months and therefore don’t need the indexes anymore. Because we do not want to waste disk space for indexes that are never used, our requirement is a monthly partitioned table with indexes on the newest two partitions only.
These use cases are fictitious, but requirements like these are not unusual in real data warehouse projects. For the following demo example, I will use the second use case (partial index on the newest two partitions).
Prepare Table for Partial Indexes
Before partial indexes can be created, we have to define for the table, which partitions are created or not. For this, the keywords INDEXING ON and INDEXING OFF were introduced in the CREATE TABLE and ALTER TABLE command. They can be defined on table level (as default for all partitions) and overwritten for individual partitions. In our example, we create a SALES table with 6 monthly partitions. The default for the table is not index creation for partial indexes. For the last two partitions, partial indexes have to be created.
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
);
Now, we insert some data (100’000 rows) and gather statistics.
INSERT /*+ append */ INTO sales
SELECT ROWNUM dwh_id
, MOD(ROWNUM, 1000) + 1 prod_id
, MOD(ROWNUM, 5000) + 1 cust_id
, TO_DATE('2015-12-31', 'YYYY-MM-DD')
+ MOD(ROWNUM, TO_NUMBER(TO_CHAR(sysdate, 'DDD'))) time_id
, MOD(ROWNUM, 20) qty_sold
, MOD(ROWNUM, 1000) amt_sold
FROM dual CONNECT BY ROWNUM <= 100000;
COMMIT;
exec dbms_stats.gather_table_stats(USER, 'SALES');
By the way: Table statistics are gathered automatically for a Direcl-Load INSERT in Oracle 12c (see my blog post Online Statistics Gathering in Oracle 12c). But here, we do it explicitly to gather the statistics on partition level.
In the data dictionary, the INDEXING flag shows for each partition, whether partial indexing is enabled (ON) or disabled (OFF).
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
Local Partial Indexes
For a local index, an index partition is created for each table partition. In case of a partial index, only the index partitions defined with INDEXING ON are created.
For the SALES table we created before, let’s now create some local indexes of different types. The first index is a local partial b*tree index, the second one a local partial bitmap index. They both contain the keywords INDEXING PARTIAL. Finally, we create another bitmap index, but without the additional keywords.
CREATE INDEX sales_cust_local_partial_idx
ON sales (cust_id)
LOCAL INDEXING PARTIAL;
CREATE BITMAP INDEX sales_prod_local_partial_bix
ON sales (prod_id)
LOCAL INDEXING PARTIAL;
CREATE BITMAP INDEX sales_time_local_bix
ON sales (time_id)
LOCAL;
A query on USER_IND_PARTITIONS shows some interesting details about the two partial indexes: They contain all partitions, but those with INDEXING OFF are unusable. They are defined in the data dictionary, but no physical segment is created in the database. For the third index (which is not a partial index), al partitions are created and usable.
SELECT index_name, partition_name, status, num_rows
FROM user_ind_partitions
WHERE index_name LIKE 'SALES%'
ORDER BY index_name, partition_name;
INDEX_NAME PARTITION_NAME STATUS NUM_ROWS
--------------------------------------------------------- ------------------------------ ------------- ----------------
SALES_CUST_LOCAL_PARTIAL_IDX P_2016_01 UNUSABLE 0
SALES_CUST_LOCAL_PARTIAL_IDX P_2016_02 UNUSABLE 0
SALES_CUST_LOCAL_PARTIAL_IDX P_2016_03 UNUSABLE 0
SALES_CUST_LOCAL_PARTIAL_IDX P_2016_04 UNUSABLE 0
SALES_CUST_LOCAL_PARTIAL_IDX P_2016_05 USABLE 17205
SALES_CUST_LOCAL_PARTIAL_IDX P_2016_06 USABLE 14985
SALES_PROD_LOCAL_PARTIAL_BIX P_2016_01 UNUSABLE 0
SALES_PROD_LOCAL_PARTIAL_BIX P_2016_02 UNUSABLE 0
SALES_PROD_LOCAL_PARTIAL_BIX P_2016_03 UNUSABLE 0
SALES_PROD_LOCAL_PARTIAL_BIX P_2016_04 UNUSABLE 0
SALES_PROD_LOCAL_PARTIAL_BIX P_2016_05 USABLE 1000
SALES_PROD_LOCAL_PARTIAL_BIX P_2016_06 USABLE 1000
SALES_TIME_LOCAL_BIX P_2016_01 USABLE 32
SALES_TIME_LOCAL_BIX P_2016_02 USABLE 29
SALES_TIME_LOCAL_BIX P_2016_03 USABLE 31
SALES_TIME_LOCAL_BIX P_2016_04 USABLE 30
SALES_TIME_LOCAL_BIX P_2016_05 USABLE 31
SALES_TIME_LOCAL_BIX P_2016_06 USABLE 27
What happens after an Index Rebuild?
If an index (or an index partition) is UNUSABLE, it can be set to USABLE with an index rebuild. Is this also the case for a Partial Index? What happens if a Partial Index is rebuilt? Let’s try this…
A complete rebuild of a local index is not possible (independent whether it is partial or not):
ALTER INDEX sales_prod_local_partial_bix REBUILD
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole
But we can rebuild all unusable partitions:
ALTER INDEX sales_prod_local_partial_bix REBUILD PARTITION p_2016_01;
ALTER INDEX sales_prod_local_partial_bix REBUILD PARTITION p_2016_02;
ALTER INDEX sales_prod_local_partial_bix REBUILD PARTITION p_2016_03;
ALTER INDEX sales_prod_local_partial_bix REBUILD PARTITION p_2016_04;
SELECT index_name, partition_name, status, num_rows
FROM user_ind_partitions
WHERE index_name = 'SALES_PROD_LOCAL_PARTIAL_BIX'
ORDER BY index_name, partition_name;
INDEX_NAME PARTITION_NAME STATUS NUM_ROWS
------------------------------------------------------- --------------------------- ------------ ----------------
SALES_PROD_LOCAL_PARTIAL_BIX P_2016_01 USABLE 1000
SALES_PROD_LOCAL_PARTIAL_BIX P_2016_02 USABLE 1000
SALES_PROD_LOCAL_PARTIAL_BIX P_2016_03 USABLE 1000
SALES_PROD_LOCAL_PARTIAL_BIX P_2016_04 USABLE 1000
SALES_PROD_LOCAL_PARTIAL_BIX P_2016_05 USABLE 1000
SALES_PROD_LOCAL_PARTIAL_BIX P_2016_06 USABLE 1000
It works: All partitions are rebuilt and USABLE. The index is now used as any regular local index, although it is still defined as a partial index. Interesting! If it is possible to “convert” a partial index into a complete local index, this should work the other way round, too. This brings us to an idea…
Do-it-yourself Partial Indexes
Let’s go back in history to the old times when Oracle 11g was used (for some of my customers, this is not yet the past). Oracle 11g did not support Partial Indexes, but we can easily “fake” them, as shown in the following example. The following statements create a “partial” index with the same behavior as described above.
CREATE BITMAP INDEX sales_prod_local_bix
ON sales (prod_id)
LOCAL UNUSABLE;
ALTER INDEX sales_prod_local_bix REBUILD PARTITION p_2016_05;
ALTER INDEX sales_prod_local_bix REBUILD PARTITION p_2016_06;
Like in the Partial Index created before, only the partitions for May and June are physically created. All other partitions are unusable and therefore only available in the data dictionary.
SELECT index_name, partition_name, status, num_rows
FROM user_ind_partitions
WHERE index_name = 'SALES_PROD_LOCAL_BIX'
ORDER BY index_name, partition_name;
INDEX_NAME PARTITION_NAME STATUS NUM_ROWS
------------------------------------------ ---------------------------- ------------------ ----------------
SALES_PROD_LOCAL_BIX P_2016_01 UNUSABLE 0
SALES_PROD_LOCAL_BIX P_2016_02 UNUSABLE 0
SALES_PROD_LOCAL_BIX P_2016_03 UNUSABLE 0
SALES_PROD_LOCAL_BIX P_2016_04 UNUSABLE 0
SALES_PROD_LOCAL_BIX P_2016_05 USABLE 1000
SALES_PROD_LOCAL_BIX P_2016_06 USABLE 1000
This “do-it-yourself” Partial Index can be used in the same matter as the official implementation in Oracle 12c. Queries on the table create the same execution plans (see part 3 of this blog post series). So what is the advantage of Partial Indexes in Oracle 12c? Of course, with the syntax extensions it is simpler to create a partial index and easier to read the DDL statements. But this is not the only reason. Another type of indexes – Global Partial Indexes – are definitely not possible to create in previous releases of Oracle. More about Global Partial Indexes in the next blog post.
Partial Indexes Trilogy
- Partial Indexes – Part 2: Global Partial Indexes explains how to create and modify global partial indexes in detail
- Partial Indexes – Part 3: Queries on Partial Indexes shows how partial indexes are used by the optimizer for SQL queries
Data Warehousing with Oracle 