Partial Indexes Trilogy – Part 3: Queries on Partial Indexes

After creating local and global partial indexes, the last part of this blog post trilogy shows how partial indexes are used by the optimizer for SQL queries – especially for queries in Data Warehouse environments.

As we have seen before, Oracle 12c allows to create partial indexes on a partitioned table , i.e. indexes that are created only on a subset of partitions. This is possible for local indexes (part 1) as well as global indexes (part 2). In this blog post I will do some tests with queries on partial indexes. For this, we use the demo use case that was introduced in the previous posts. Our SALES table currently contains 7 monthly partitions (for January to July 2016). Partial indexes are created on the newest two partitions only. For the older partitions, no partial indexes are available.

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 16255 OFF
SALES P_2016_02 14732 OFF
SALES P_2016_03 15748 OFF
SALES P_2016_04 15240 OFF
SALES P_2016_05 15717 OFF
SALES P_2016_06 15210 ON
SALES P_2016_07 17098 ON

Currently, the INDEXING flag is set on partitions for June and July 2016, but not for partitions January to May. The table contains two partial indexes: A local partial bitmap index on column PROD_ID, and a global partial b-tree index on column CUST_ID. These are the preconditions, now let’s do some queries. 

Queries with Local Partial Index

In the first query, we select the total amount sold for a particular product (PROD_ID = 123) without a date restriction. Oracle has to read all partitions of the SALES table. Because of the partial index on PROD_ID, it is possible to use this index for partitions 6 and 7 (green part of execution plan). For partitions 1 to 5, a full table scan is performed. That’s exactly the behavior we want to have with a partial index. (Note: The partitions that are scanned are displayed in columns Pstart/Pstop on the right border of the execution plan. Eventually, you have to scroll to the right in the code examples of this blog)

SELECT SUM(amt_sold)
  FROM sales
 WHERE prod_id = 123; 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name                         | Starts | E-Rows | A-Rows | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                              |      1 |        |      1 |       |       |
|   1 |  SORT AGGREGATE                               |                              |      1 |      1 |      1 |       |       |
|   2 |   VIEW                                        | VW_TE_2                      |      1 |    110 |    109 |       |       |
|   3 |    UNION-ALL                                  |                              |      1 |        |    109 |       |       |
|   4 |     PARTITION RANGE ITERATOR                  |                              |      1 |     31 |     33 |     6 |     7 |
|   5 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES                        |      2 |     31 |     33 |     6 |     7 |
|   6 |       BITMAP CONVERSION TO ROWIDS             |                              |      2 |        |     33 |       |       |
|*  7 |        BITMAP INDEX SINGLE VALUE              | SALES_PROD_LOCAL_PARTIAL_BIX |      2 |        |      2 |     6 |     7 |

|   8 |     PARTITION RANGE ITERATOR                  |                              |      1 |     79 |     76 |     1 |     5 |
|*  9 |      TABLE ACCESS FULL                        | SALES                        |      5 |     79 |     76 |     1 |     5 |

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
------------------------------------------------------------------------------------------------------
 
   7 - access("PROD_ID"=123)
   9 - filter("PROD_ID"=123)

Now, we change the query to select only the total amount for March 2016. Because no index exists on this partition, a full table scan is performed on partition 3 (red part in execution plan). Although no index access will be used here, the index is still visible in the execution plan, but not executed due to the filter “NULL IS NOT NULL” in line 4. The grey part in the execution plan (lines 4 to 8) is not performed at runtime.

SELECT SUM(amt_sold)
  FROM sales
 WHERE prod_id = 123
   AND time_id BETWEEN TO_DATE('2016-03-01', 'YYYY-MM-DD')
                   AND TO_DATE('2016-03-31', 'YYYY-MM-DD');
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                         | Starts | E-Rows | A-Rows | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                              |      1 |        |      1 |       |       |
|   1 |  SORT AGGREGATE                                |                              |      1 |      1 |      1 |       |       |
|   2 |   VIEW                                         | VW_TE_2                      |      1 |     17 |     14 |       |       |
|   3 |    UNION-ALL                                   |                              |      1 |        |     14 |       |       |
|*  4 |     FILTER                                     |                              |      1 |        |      0 |       |       |
|   5 |      PARTITION RANGE EMPTY                     |                              |      0 |      1 |      0 |INVALID|INVALID|
|*  6 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES                        |      0 |      1 |      0 |INVALID|INVALID|
|   7 |        BITMAP CONVERSION TO ROWIDS             |                              |      0 |        |      0 |       |       |
|*  8 |         BITMAP INDEX SINGLE VALUE              | SALES_PROD_LOCAL_PARTIAL_BIX |      0 |        |      0 |INVALID|INVALID|

|   9 |     PARTITION RANGE SINGLE                     |                              |      1 |     16 |     14 |     3 |     3 |
|* 10 |      TABLE ACCESS FULL                         | SALES                        |      1 |     16 |     14 |     3 |     3 |

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
------------------------------------------------------------------------------------------------------
 
   4 - filter(NULL IS NOT NULL)
   6 - filter(("SALES"."TIME_ID">=TO_DATE(' 2016-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TIME_ID"<=TO_DATE(' 2016-03-31
              00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
   8 - access("PROD_ID"=123)
  10 - filter(("PROD_ID"=123 AND "TIME_ID"<=TO_DATE(' 2016-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

When we change the query to July 2016, the execution plan looks slightly different. In this case, the first part of the UNION ALL is executed and performs an index access on the local index partition 7 (green part, lines 4 to 7). The second part (grey part, lines 8 to 12) contains another index scan (on non-existing index partitions), but is never executed because of the filter in line 8.

SELECT SUM(amt_sold)
  FROM sales
 WHERE prod_id = 123
   AND time_id BETWEEN TO_DATE('2016-07-01', 'YYYY-MM-DD')
                   AND TO_DATE('2016-07-31', 'YYYY-MM-DD'); 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                         | Starts | E-Rows | A-Rows | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                              |      1 |        |      1 |       |       |
|   1 |  SORT AGGREGATE                                |                              |      1 |      1 |      1 |       |       |
|   2 |   VIEW                                         | VW_TE_2                      |      1 |     18 |     17 |       |       |
|   3 |    UNION-ALL                                   |                              |      1 |        |     17 |       |       |
|   4 |     PARTITION RANGE SINGLE                     |                              |      1 |     17 |     17 |     7 |     7 |
|*  5 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | SALES                        |      1 |     17 |     17 |     7 |     7 |
|   6 |       BITMAP CONVERSION TO ROWIDS              |                              |      1 |        |     17 |       |     |
|*  7 |        BITMAP INDEX SINGLE VALUE               | SALES_PROD_LOCAL_PARTIAL_BIX |      1 |        |      1 |     7 |     7 |

|*  8 |     FILTER                                     |                              |      1 |        |      0 |       |       |
|   9 |      PARTITION RANGE EMPTY                     |                              |      0 |      1 |      0 |INVALID|INVALID|
|* 10 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES                        |      0 |      1 |      0 |INVALID|INVALID|
|  11 |        BITMAP CONVERSION TO ROWIDS             |                              |      0 |        |      0 |       |       |
|* 12 |         BITMAP INDEX SINGLE VALUE              | SALES_PROD_LOCAL_PARTIAL_BIX |      0 |        |      0 |INVALID|INVALID|

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
------------------------------------------------------------------------------------------------------
 
   5 - filter("TIME_ID"<=TO_DATE(' 2016-07-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   7 - access("PROD_ID"=123)
   8 - filter(NULL IS NOT NULL)
  10 - filter(("TIME_ID">=TO_DATE(' 2016-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SALES"."TIME_ID"<TO_DATE(' 2016-06-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
  12 - access("PROD_ID"=123)

So, everything looks fine till now. The index partitions of the local partial index are used whenever possible, otherwise a full table scan is performed. To understand the execution plans, it is important to check which parts are really executed. This can be derived from the operations FILTER (NULL IS NOT NULL) or PARTITION RANGE EMPTY, but also from the number of executions (column “Starts”) and from the partition identifiers (columns “Pstart” and “Pstop”). 

Queries with Global Partial Index

To show the differences and similarities between local and global partial indexes, I created an additional index on column CUST_ID of the SALES table, but as a global (b-tree) index. The behavior is similar to the one with local indexes, as we see in the following three queries.

The first query selects the total amount of one particular customer. For the ROWIDs found in the partial index, an index range scan is performed. The rows for partitions 1 to 5 are not stored in the index and are therefore read with a full table scan. Like in the first example with the local index, a combination of index scan and full table scan is used to read all partitions.

SELECT SUM(amt_sold)
  FROM sales
 WHERE cust_id = 4711; 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name                          | Starts | E-Rows | A-Rows | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                               |      1 |        |      1 |       |       |
|   1 |  SORT AGGREGATE                               |                               |      1 |      1 |      1 |       |       |
|   2 |   VIEW                                        | VW_TE_2                       |      1 |     22 |     21 |       |       |
|   3 |    UNION-ALL                                  |                               |      1 |        |     21 |       |       |
|*  4 |     TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SALES                         |      1 |      6 |      6 | ROWID | ROWID |
|*  5 |      INDEX RANGE SCAN                         | SALES_CUST_GLOBAL_PARTIAL_IDX |      1 |      6 |      6 |       |       |

|   6 |     PARTITION RANGE ITERATOR                  |                               |      1 |     16 |     15 |     1 |     5 |
|*  7 |      TABLE ACCESS FULL                        | SALES                         |      5 |     16 |     15 |     1 |     5 |

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
------------------------------------------------------------------------------------------------------
 
   4 - filter(("SALES"."TIME_ID">=TO_DATE(' 2016-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SALES"."TIME_ID"<TO_DATE('
              2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
   5 - access("CUST_ID"=4711)
   7 - filter("CUST_ID"=4711)

If we add a restriction on the query to read only the total amount for March 2016, only the second (red) part of the execution plan is executed. It reads partition 3 (March 2016) of the SALES table. Interesting detail: Both parts contain a full table scan, although the grey part in the execution plan is never executed because of the FILTER.

SELECT SUM(amt_sold)
  FROM sales
 WHERE cust_id = 4711
   AND time_id BETWEEN TO_DATE('2016-03-01', 'YYYY-MM-DD')
                   AND TO_DATE('2016-03-31', 'YYYY-MM-DD'); 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name    | Starts | E-Rows | A-Rows | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |         |      1 |        |      1 |       |       |
|   1 |  SORT AGGREGATE           |         |      1 |      1 |      1 |       |       |
|   2 |   VIEW                    | VW_TE_2 |      1 |      4 |      3 |       |       |
|   3 |    UNION-ALL              |         |      1 |        |      3 |       |       |
|*  4 |     FILTER                |         |      1 |        |      0 |       |       |
|   5 |      PARTITION RANGE EMPTY|         |      0 |      1 |      0 |INVALID|INVALID|
|*  6 |       TABLE ACCESS FULL   | SALES   |      0 |      1 |      0 |INVALID|INVALID|

|   7 |     PARTITION RANGE SINGLE|         |      1 |      3 |      3 |     3 |     3 |
|*  8 |      TABLE ACCESS FULL    | SALES   |      1 |      3 |      3 |     3 |     3 |

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter(NULL IS NOT NULL)
   6 - filter(("CUST_ID"=4711 AND "SALES"."TIME_ID">=TO_DATE(' 2016-06-01 00:00:00',
              'syyyy-mm-dd hh24:mi:ss') AND "TIME_ID"<=TO_DATE(' 2016-03-31 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss')))
   8 - filter(("CUST_ID"=4711 AND "TIME_ID"<=TO_DATE(' 2016-03-31 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'))) 

If the query selects July 2016, all relevant ROWIDs can be found with an index range scan on the global partial index (green part). The grey part that would execute a full table scan on all partitions without indexing is never executed.

SELECT SUM(amt_sold)
  FROM sales
 WHERE cust_id = 4711
   AND time_id BETWEEN TO_DATE('2016-07-01', 'YYYY-MM-DD')
                   AND TO_DATE('2016-07-31', 'YYYY-MM-DD');
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name                          | Starts | E-Rows | A-Rows | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                               |      1 |        |      1 |       |       |
|   1 |  SORT AGGREGATE                               |                               |      1 |      1 |      1 |       |       |
|   2 |   VIEW                                        | VW_TE_2                       |      1 |      4 |      3 |       |       |
|   3 |    UNION-ALL                                  |                               |      1 |        |      3 |       |       |
|*  4 |     TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SALES                         |      1 |      3 |      3 |     7 |     7 |
|*  5 |      INDEX RANGE SCAN                         | SALES_CUST_GLOBAL_PARTIAL_IDX |      1 |      3 |      6 |       |       |

|*  6 |     FILTER                                    |                               |      1 |        |      0 |       |       |
|   7 |      PARTITION RANGE EMPTY                    |                               |      0 |      1 |      0 |INVALID|INVALID|
|*  8 |       TABLE ACCESS FULL                       | SALES                         |      0 |      1 |      0 |INVALID|INVALID|

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
------------------------------------------------------------------------------------------------------
 
   4 - filter(("TIME_ID"<=TO_DATE(' 2016-07-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SALES"."TIME_ID">=TO_DATE(' 2016-07-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
   5 - access("CUST_ID"=4711)
   6 - filter(NULL IS NOT NULL)
   8 - filter(("CUST_ID"=4711 AND "TIME_ID">=TO_DATE(' 2016-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "SALES"."TIME_ID"<TO_DATE(' 2016-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

Although the execution plans look slightly different for a global index, the principle is the same as with local indexes. The optimizer tries to use the index if possible (of course only when the selectivity is strong enough for an index scan). Partitions with INDEXING OFF are read with a full table scan.

Queries with Partial Indexes on Star Schema

All the queries we have seen so far are good to show the principle of partial index scans, but reality is often more complex. In Data Warehouse environments, we typically work with Star Schemas, using facts and dimension tables. Instead of a WHERE condition on the technical keys TIME_ID, PROD_ID or CUST_ID, we join these keys with the corresponding dimension tables and add some filters on the descriptive attributes of the dimensions. How are partial indexes used in such a situation?

If the SALES table of our demo case is a fact table of a Star Schema, we replace the BETWEEN condition with a join on the TIMES dimension table and add a filter on the column MONTH_DESC. The following example selects the total amount for July 2016:

SELECT SUM(s.amt_sold)
  FROM sales s
JOIN times t ON (t.time_id = s.time_id)
WHERE t.month_desc = '2016-07';
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | A-Rows | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |      1 |       |       |
|   1 |  SORT AGGREGATE               |         |      1 |      1 |      1 |       |       |
|*  2 |   HASH JOIN                   |         |      1 |  16011 |  17098 |       |       |
|   3 |    PART JOIN FILTER CREATE    | :BF0000 |      1 |     31 |     31 |       |       |
|*  4 |     TABLE ACCESS FULL         | TIMES   |      1 |     31 |     31 |       |       |
|   5 |    PARTITION RANGE JOIN-FILTER|         |      1 |    109K|  17098 |:BF0000|:BF0000|
|   6 |     TABLE ACCESS FULL         | SALES   |      1 |    109K|  17098 |:BF0000|:BF0000|

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
------------------------------------------------------------------------------------------------------
 
   2 - access("T"."TIME_ID"="S"."TIME_ID")
   4 - filter("T"."MONTH_DESC"='2016-07')

Although a partial index is available for the July partition, it is not used. But this has nothing to do with the partial index. The execution plan would be exactly the same if we create a regular bitmap index on TIME_ID for all partitions. The optimizer decides to do a join filter pruning with a bloom filter (:BF0000), so the full table scan is performed only on the partition for July 2016. Because of the weak selectivity (100% of the rows in the July partition are selected), a full table scan is perfect. Interesting, but we still have to answer to the question how partial indexes work in a Star Schema. For this, we need an additional filter criteria on another dimension. We join the SALES fact table with the TIMES and the PRODUCTS dimension, with a WHERE condition on each dimension. For each of the dimension keys, we created a local partial index on the SALES table. In this case, a Star Transformation should be performed. Let’s have a look whether this works with partial indexes.

SELECT t.time_id, SUM(s.amt_sold)
  FROM sales s
  JOIN times t ON (t.time_id = s.time_id)
  JOIN products p ON (p.prod_id = s.prod_id)
 WHERE p.prod_name = 'Macallan'
   AND t.month_desc = '2016-07'
GROUP BY t.time_id;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                         | Starts | E-Rows | A-Rows | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                              |      1 |        |     30 |       |       |
|   1 |  HASH GROUP BY                           |                              |      1 |     18 |     30 |       |       |
|   2 |   VIEW                                   | VW_TE_24                     |      1 |    292 |    542 |       |       |
|   3 |    UNION-ALL                             |                              |      1 |        |    542 |       |       |
|*  4 |     HASH JOIN                            |                              |      1 |    146 |    542 |       |       |
|*  5 |      TABLE ACCESS FULL                   | TIMES                        |      1 |     31 |     31 |       |       |
|   6 |      VIEW                                | VW_ST_6C8E4D9C               |      1 |    146 |    542 |       |       |
|   7 |       NESTED LOOPS                       |                              |      1 |    146 |    542 |       |       |
|   8 |        PARTITION RANGE SUBQUERY          |                              |      1 |    146 |    542 |KEY(SQ)|KEY(SQ)|
|   9 |         BITMAP CONVERSION TO ROWIDS      |                              |      1 |    146 |    542 |       |       |
|  10 |          BITMAP AND                      |                              |      1 |        |      1 |       |       |
|  11 |           BITMAP MERGE                   |                              |      1 |        |      1 |       |       |
|  12 |            BITMAP KEY ITERATION          |                              |      1 |        |     30 |       |       |
|  13 |             BUFFER SORT                  |                              |      1 |        |     31 |       |       |
|* 14 |              TABLE ACCESS FULL           | TIMES                        |      1 |     31 |     31 |       |       |
|* 15 |             BITMAP INDEX RANGE SCAN      | SALES_TIME_LOCAL_PARTIAL_BIX |     31 |        |     30 |KEY(SQ)|KEY(SQ)|
|  16 |           BITMAP MERGE                   |                              |      1 |        |      1 |       |       |
|  17 |            BITMAP KEY ITERATION          |                              |      1 |        |     31 |       |       |
|  18 |             BUFFER SORT                  |                              |      1 |        |     31 |       |       |
|* 19 |              TABLE ACCESS FULL           | PRODUCTS                     |      1 |     31 |     31 |       |       |
|* 20 |             BITMAP INDEX RANGE SCAN      | SALES_PROD_LOCAL_PARTIAL_BIX |     31 |        |     31 |KEY(SQ)|KEY(SQ)|
|  21 |        TABLE ACCESS BY USER ROWID        | SALES                        |    542 |      1 |    542 | ROWID | ROWID |

|* 22 |     HASH JOIN                            |                              |      1 |    146 |      0 |       |       |
|  23 |      PART JOIN FILTER CREATE             | :BF0000                      |      1 |     13 |      0 |       |       |
|* 24 |       TABLE ACCESS BY INDEX ROWID BATCHED| TIMES                        |      1 |     13 |      0 |       |       |
|* 25 |        INDEX RANGE SCAN                  | TIMES_PK                     |      1 |    152 |    152 |       |       |
|* 26 |      HASH JOIN                           |                              |      0 |   2393 |      0 |       |       |
|* 27 |       TABLE ACCESS FULL                  | PRODUCTS                     |      0 |     31 |      0 |       |       |
|  28 |       PARTITION RANGE AND                |                              |      0 |  77186 |      0 |KEY(AP)|KEY(AP)|
|  29 |        TABLE ACCESS FULL                 | SALES                        |      0 |  77186 |      0 |KEY(AP)|KEY(AP)|

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
------------------------------------------------------------------------------------------------------
 
   4 - access("T"."TIME_ID"="ITEM_1")
   5 - filter("T"."MONTH_DESC"='2016-07')
  14 - filter("T"."MONTH_DESC"='2016-07')
  15 - access("S"."TIME_ID"="T"."TIME_ID")
       filter(("S"."TIME_ID"<TO_DATE(' 2016-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "S"."TIME_ID">=TO_DATE('
              2016-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
  19 - filter("P"."PROD_NAME"='Macallan')
  20 - access("S"."PROD_ID"="P"."PROD_ID")
  22 - access("T"."TIME_ID"="S"."TIME_ID")
  24 - filter("T"."MONTH_DESC"='2016-07')
  25 - access("T"."TIME_ID"<TO_DATE(' 2016-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  26 - access("P"."PROD_ID"="S"."PROD_ID")
  27 - filter("P"."PROD_NAME"='Macallan')
 
Note
--------
   - star transformation used for this statement

Lines 4 to 21 (the green part of the execution plan) are the typical operations for a Star Transformation. The bitmap indexes for all dimensions with a filter criteria (here for TIMES and PRODUCTS) are combined to derive the rows to be selected from the fact table SALES. This works for partial indexes in exactly the same manner as usual, but only for the partitions with INDEXING ON. For our test query this would be sufficient, but the execution plan contains a second part: Lines 22 to 29 (marked red in the execution plan) are required to read the partitions without partial indexes (i.e. INDEXING OFF). As in the previous example, a join filter pruning between the TIMES dimension and the SALES fact table is performed. The term “KEY(AP)” is a replacement for “:BF0000” and is used since Oracle 11.2 for combinations of filters (AF = “And-Pruning”).

Although, this second part of the execution plan performs some additional steps because of the partial indexes, this is a very small overhead in our case. As you can see in the “A-Rows” column, 152 rows are selected from the TIMES dimension (all dates between 01-JAN and 31-MAY). The tables PRODUCTS and SALES are not accessed at all in the red part of the execution plan. This is not required because we need only the rows for July 2016.

Conclusion

Partial Indexes are a very useful extension of partitioning introduced with Oracle 12c. They are available for local and global indexes on partitioned tables. The typical use case in Data Warehouses will be local bitmap indexes, either on partitioned Core tables or on fact tables of Star Schemas. Partial Indexes can be used in the same way as any other local or global index, even Star Transformation is supported.

Partial Indexes Trilogy

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s