Introduced in Oracle 12c release, it is possible to create partial indexes on a partitioned table. While a full index includes all partitions in a given table, a partial index just includes only partitions which are mark to be indexed during table creation process. This new 12c feature provides more flexibility with index creation and reduce storage utilization. The INDEXING clause in the following CREATE TABLE statement determines whether or not the partition is to be indexed. It can be set at the table level (default behavior for the table) or at the individual partition level.
SQL> CREATE TABLE cars (
2 units NUMBER,
3 make VARCHAR2(20),
4 model VARCHAR2(30),
5 year NUMBER
6 )
7 INDEXING OFF
8 PARTITION BY RANGE (year)
9 (PARTITION gen1 VALUES LESS THAN (1973) INDEXING ON,
10 PARTITION gen2 VALUES LESS THAN (1978) INDEXING OFF,
11 PARTITION gen3 VALUES LESS THAN (1993) INDEXING ON,
12 PARTITION gen4 VALUES LESS THAN (2004) INDEXING OFF,
13 PARTITION gen5 VALUES LESS THAN (2014) INDEXING OFF,
14 PARTITION gen6 VALUES LESS THAN (MAXVALUE) INDEXING OFF);
Table created.In the above example, just two partitions (gen1 and gen3) are going to be partially indexed.
Now, populate the table with some data as follows:
SQL> /* First Generation */
SQL> INSERT INTO cars
2 SELECT ROUND(dbms_rANDom.value(1,10000)) units,
3 'FORD','MUSTANG',
4 ROUND(dbms_rANDom.value(1964,1973)) year
5 FROM dual
6 CONNECT BY LEVEL < 5000 ;
4999 rows created.
SQL> /* Second Generation */
SQL> INSERT INTO cars
2 SELECT ROUND(dbms_rANDom.value(1,10000)) units,
3 'FORD','MUSTANG',
4 ROUND(dbms_rANDom.value(1974,1978)) year
5 FROM dual
6 CONNECT BY LEVEL < 5000 ;
4999 rows created.
SQL> /* Third Generation */
SQL> INSERT INTO cars
2 SELECT ROUND(dbms_rANDom.value(1,10000)) units,
3 'FORD','MUSTANG',
4 ROUND(dbms_rANDom.value(1979,1993)) year
5 FROM dual
6 CONNECT BY LEVEL < 5000 ;
4999 rows created.
SQL> /* Fourth Generation */
SQL> INSERT INTO cars
2 SELECT ROUND(dbms_rANDom.value(1,10000)) units,
3 'FORD','MUSTANG',
4 ROUND(dbms_rANDom.value(1994,2004)) year
5 FROM dual
6 CONNECT BY LEVEL < 5000 ;
4999 rows created.
SQL> /* Fifth Generation */
SQL> INSERT INTO cars
2 SELECT ROUND(dbms_rANDom.value(1,10000)) units,
3 'FORD','MUSTANG',
4 ROUND(dbms_rANDom.value(2005,2014)) year
5 FROM dual
6 CONNECT BY LEVEL < 5000 ;
4999 rows created.
SQL> /* Sixth Generation */
SQL> INSERT INTO cars
2 SELECT ROUND(dbms_rANDom.value(1,10000)) units,
3 'FORD','MUSTANG',
4 2015
5 FROM dual
6 CONNECT BY LEVEL < 5000 ;
4999 rows created.
SQL> COMMIT;Now let's create an index on the MAKE column. This new index will include all partitions in a table, regardless of the INDEXING table clause setting.
SQL> CREATE INDEX car_make ON cars (make);
Index created.A leaf block stores key values. The leaf blocks contain indexed data value and a corresponding rowid used to locate each given row. Verify leaf blocks corresponding to CAR_MAKE index by querying USER_INDEXES catalog view as follows:
SQL> SELECT index_name,
2 num_rows,
3 leaf_blocks,
4 indexing
5 FROM user_indexes
6 WHERE index_name = 'CAR_MAKE' ;
INDEX_NAME NUM_ROWS LEAF_BLOCKS INDEXING
---------- ---------- ----------- ----------
CAR_MAKE 29994 84 FULLThis new index has 84 leaf blocks and is a FULL (non-Partial) index.
Execute the following query and show its execution plan in order to verify how the new index is used:
SQL> SET AUTOTRACE ON
SQL> SELECT count(*)
2 FROM cars
3 WHERE make = 'FORD'
4 AND year < 1974 ;
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 |
| 1 | SORT AGGREGATE | | 1 | 25 |
|* 2 | TABLE ACCESS BY | CARS | 4731 | 115K |
| | GLOBAL INDEX ROWID BATCHED | | | |
|* 3 | INDEX RANGE SCAN | CAR_MAKE | 29994 | |
------------------------------------------------------------------------Drop the current index and then create a new index as a PARTIAL index. The new INDEXING PARTIAL clause means that only those table partitions with INDEXING ON are to be included within the index.
SQL> DROP INDEX car_make;
Index dropped.
SQL> CREATE INDEX car_make_i ON cars (make) INDEXING PARTIAL;
Index created.Verify statistics for new index by querying again the USER_INDEXES catalog view as follows:
SQL> SELECT index_name,
2 num_rows,
3 leaf_blocks,
4 indexing
5 FROM user_indexes
6 WHERE index_name = 'CAR_MAKE_I';
INDEX_NAME NUM_ROWS LEAF_BLOCKS INDEXING
---------- ---------- ----------- ----------
CAR_MAKE_I 10155 29 PARTIALThis new index has 10155 rows and 29 leaf blocks, instead of 29994 rows and 84 leaf blocks as in the previous index. The INDEXING column also verifies this new index as a PARTIAL index.
Execute again the following query and show its execution plan in order to verify how the new PARTIAL index is used:
SQL> SELECT count(*)
2 FROM cars
3 WHERE make = 'FORD'
4 AND year < 1974 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 |
| 1 | SORT AGGREGATE | | 1 | 25 |
| 2 | VIEW | VW_TE_2 | 43 | |
| 3 | UNION-ALL | | | |
|* 4 | TABLE ACCESS BY | CARS | 41 | 1517 |
| | GLOBAL INDEX ROWID BATCHED | | | |
|* 5 | INDEX RANGE SCAN | CAR_MAKE_I | 329 | |
| 6 | PARTITION RANGE SINGLE | | 2 | 74 |
|* 7 | TABLE ACCESS FULL | CARS | 2 | 74 |
--------------------------------------------------------------------------Note how explain plan has changed and how Rows and Bytes were dramatically reduced in comparison with the previous run.
And what happens if the query is performed using the partitions that are not indexed as PARTIAL?
Execute the above query again, but this time change query predicate to user any of the partitions created with INDEXING OFF clause. For example GEN5 and GEN6 as follows:
SELECT count(*)
2 FROM cars
3 WHERE make = 'FORD'
4 AND year > 2005 ;
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 548 (1)|
| 1 | SORT AGGREGATE | | 1 | 25 | |
| 2 | PARTITION RANGE ITERATOR| | 25208 | 615K| 548 (1)|
|* 3 | TABLE ACCESS FULL | CARS | 25208 | 615K| 548 (1)|
-----------------------------------------------------------------------This time, a TABLE ACCESS FULL operation is being performed to resolve the sample query.
The new Partial Index feature is very useful to save storage and improve performance when you are managing very large tables.