Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

New Index enhancement in Oracle 12c Part 2 – Partial indexes on partitioned tables

 

In the article, you will have a look at the partial indexes available since Oracle 12c. The partial indexes enable us to index only certain partitions of a partitioned able and avoid indexing of the other partitions of the same table. The partial indexes can be either global or local indexes.

I will illustrate with examples the use of partial indexes. First, I will create full indexes and after that I will create partial indexes and we will compare the results.

  1. Test environment

 

We will create and populate the following table as follows:

 

CREATE TABLE sales

( prod_id NUMBER(10)

, cust_id NUMBER(10)

, time_id DATE

, channel_id number(10)

, promo_id NUMBER(10)

, quantity_sold NUMBER(10)

, amount_sold NUMBER(10,2)

) INDEXING OFF

PARTITION BY RANGE (time_id)

( PARTITION sales_2011 VALUES LESS THAN (TO_DATE(’01-JAN-2012′,’dd-MON-yyyy’))   INDEXING OFF

, PARTITION sales_2012 VALUES LESS THAN (TO_DATE(’01-JAN-2013′,’dd-MON-yyyy’))  INDEXING OFF

, PARTITION sales_2013 VALUES LESS THAN (TO_DATE(’01-JAN-2014′,’dd-MON-yyyy’))  INDEXING OFF

, PARTITION sales_1014 VALUES LESS THAN (TO_DATE(’01-JAN-2015′,’dd-MON-yyyy’))  INDEXING OFF

, PARTITION sales_2015 VALUES LESS THAN (TO_DATE(’01-JAN-2016′,’dd-MON-yyyy’))  INDEXING OFF

, PARTITION sales_1016 VALUES LESS THAN (TO_DATE(’01-JAN-2017′,’dd-MON-yyyy’))  INDEXING OFF

, PARTITION sales_future VALUES LESS THAN (maxvalue)  INDEXING ON

);

 

begin

for lvl in 1..3000000 loop

insert into sales values( lvl, mod(lvl,100000), sysdate-6*366+mod(lvl,8*366), mod(lvl,10), mod(lvl,1000000), mod(lvl,100),mod(lvl,100)) ;

commit;

end loop;

end;

/

 

 

  1. Full indexes

 

We will create the following instances and will get the explain plan.

 

create index sales_idx_part on sales(time_id) indexing full;

 

select  sum(amount_sold) from sales where time_id > TO_DATE(’01-JAN-2017′,’dd-MON-yyyy’) ;

 

 select * from table(dbms_xplan.display_cursor());

 

SQL> select  sum(amount_sold) from sales where time_id > TO_DATE(’01-JAN-2017′,’dd-MON-yyyy’) ;

 

 select * from table(dbms_xplan.display_cursor());

 

SUM(AMOUNT_SOLD)

—————-

       39689536

 

SQL> SQL>

PLAN_TABLE_OUTPUT

————————————————————————————————————————————————————————————————————————————————————————————————————

SQL_ID 5kbg4z2zgktqm, child number 0

————————————-

select sum(amount_sold) from sales where time_id >

TO_DATE(’01-JAN-2017′,’dd-MON-yyyy’)

 

Plan hash value: 642363238

 

————————————————————————————————-

| Id  | Operation            | Name | Rows | Bytes | Cost (%CPU)| Time   | Pstart| Pstop |

————————————————————————————————-

|   0 | SELECT STATEMENT     |      |      |      |  1100 (100)|        |      |      |

|   1 |  SORT AGGREGATE      |      |     1 |    11 |          |        |      |      |

|   2 |   PARTITION RANGE SINGLE|    |   801K|  8613K|  1100   (1)| 00:00:01 |     7 |     7 |

|*  3 |    TABLE ACCESS FULL  | SALES |   801K|  8613K|  1100   (1)| 00:00:01 |     7 |     7 |

————————————————————————————————-

 

Predicate Information (identified by operation id):

—————————————————

 

   3 – filter(“TIME_ID”>TO_DATE(‘ 2017-01-01 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’))

 

 

21 rows selected.

 

SQL>

 

create index promo_id_idx on sales(promo_id)  indexing full;

 

select  sum(amount_sold) from sales where promo_id=9 ;

 

 select * from table(dbms_xplan.display_cursor());

 

SQL>

select  sum(amount_sold) from sales where promo_id=9 ;

 

 select * from table(dbms_xplan.display_cursor());

 

SQL>

SUM(AMOUNT_SOLD)

—————-

             27

 

SQL> SQL>

PLAN_TABLE_OUTPUT

————————————————————————————————————————————————————————————————————————————————————————————————————

SQL_ID 33gsxqw7359y8, child number 0

————————————-

select sum(amount_sold) from sales where promo_id=9

 

Plan hash value: 2044900305

 

—————————————————————————————————————————-

| Id  | Operation                              | Name        | Rows  | Bytes | Cost (%CPU)| Time          | Pstart| Pstop |

—————————————————————————————————————————-

|   0 | SELECT STATEMENT                       |            |      |      |   6 (100)|        |          |      |

|   1 |  SORT AGGREGATE                        |            |   1 |   8 |          |         |          |      |

|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SALES    |   3 |   24 |   6   (0)| 00:00:01 | ROWID | ROWID |

|*  3 |    INDEX RANGE SCAN                    | PROMO_ID_IDX |  3 |      |   3   (0)| 00:00:01 |          |      |

—————————————————————————————————————————-

 

Predicate Information (identified by operation id):

—————————————————

 

   3 – access(“PROMO_ID”=9)

 

 

20 rows selected.

 

SQL> SQL>

 

 

create index cust_id_idx on sales(cust_id)  indexing full;

 

select  sum(amount_sold) from sales where cust_id=9 ;

 

 select * from table(dbms_xplan.display_cursor());

 

SQL> select  sum(amount_sold) from sales where cust_id=9 ;

 

 select * from table(dbms_xplan.display_cursor());

 

SUM(AMOUNT_SOLD)

—————-

            270

 

SQL> SQL>

PLAN_TABLE_OUTPUT

————————————————————————————————————————————————————————————————————————————————————————————————————

SQL_ID 0q4bsj3b9w919, child number 0

————————————-

select sum(amount_sold) from sales where cust_id=9

 

Plan hash value: 1297472345

 

—————————————————————————————————————————

| Id  | Operation                              | Name       | Rows  | Bytes | Cost (%CPU)| Time   | Pstart| Pstop |

—————————————————————————————————————————

|   0 | SELECT STATEMENT                       |           |      |      |    33 (100)|        |         |      |

|   1 |  SORT AGGREGATE                        |           |    1 |    8 |           |        |         |      |

|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SALES   |    30 |   240 |    33   (0)| 00:00:01 | ROWID | ROWID |

|*  3 |    INDEX RANGE SCAN                    | CUST_ID_IDX |    30 |     |    3   (0)| 00:00:01 |         |      |

—————————————————————————————————————————

 

Predicate Information (identified by operation id):

—————————————————

 

   3 – access(“CUST_ID”=9)

 

 

20 rows selected.

 

SQL>

 

select sum(amount_sold) from sales where time_id between TO_DATE(’01-JAN-2012′,’dd-MON-yyyy’) and TO_DATE(’01-JAN-2016′,’dd-MON-yyyy’);

 

 select * from table(dbms_xplan.display_cursor());

 

SQL> select sum(amount_sold) from sales where time_id between TO_DATE(’01-JAN-2012′,’dd-MON-yyyy’) and TO_DATE(’01-JAN-2016′,’dd-MON-yyyy’);

 

 select * from table(dbms_xplan.display_cursor());

 

SUM(AMOUNT_SOLD)

—————-

       74126725

 

SQL> SQL>

PLAN_TABLE_OUTPUT

————————————————————————————————————————————————————————————————————————————————————————————————————

SQL_ID 6vw3t5hgnrp3d, child number 0

————————————-

select sum(amount_sold) from sales where time_id between

TO_DATE(’01-JAN-2012′,’dd-MON-yyyy’) and

TO_DATE(’01-JAN-2016′,’dd-MON-yyyy’)

 

Plan hash value: 1500327972

 

—————————————————————————————————

| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time         | Pstart| Pstop |

—————————————————————————————————

|   0 | SELECT STATEMENT       |      |      |      |  2747 (100)|        |      |      |

|   1 |  SORT AGGREGATE        |      |    1 |    11 |           |        |      |      |

|   2 |   PARTITION RANGE ITERATOR|    |  1497K|    15M|  2747   (1)| 00:00:01 |  2 |    6 |

|*  3 |    TABLE ACCESS FULL    | SALES |  1497K|    15M|  2747   (1)| 00:00:01 | 2 |    6 |

—————————————————————————————————

 

Predicate Information (identified by operation id):

—————————————————

 

   3 – filter(“TIME_ID”<=TO_DATE(‘ 2016-01-01 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’))

 

 

22 rows selected.

 

SQL>

 

create index cust_id_idx on sales(cust_id)  indexing full;

 

select  sum(amount_sold) from sales where cust_id in (7,8,9,10) ;

 

 select * from table(dbms_xplan.display_cursor());

 

SQL> select  sum(amount_sold) from sales where cust_id in (7,8,9,10) ;

 

 select * from table(dbms_xplan.display_cursor());

 

SUM(AMOUNT_SOLD)

—————-

           1020

 

SQL> SQL>

PLAN_TABLE_OUTPUT

————————————————————————————————————————————————————————————————————————————————————————————————————

SQL_ID 76s2z4ud5qk6s, child number 0

————————————-

select sum(amount_sold) from sales where cust_id in (7,8,9,10)

 

Plan hash value: 1051011554

 

—————————————————————————————————————————-

| Id  | Operation                               | Name       | Rows  | Bytes | Cost (%CPU)| Time          | Pstart| Pstop |

—————————————————————————————————————————-

|   0 | SELECT STATEMENT                        |           |      |      |   125 (100)|        |          |      |

|   1 |  SORT AGGREGATE                         |           |   1 |   8 |          |         |          |      |

|   2 |   INLIST ITERATOR                       |           |      |      |          |         |          |      |

|   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SALES          |   119 |   952 |   125   (0)| 00:00:01 | ROWID | ROWID |

|*  4 |     INDEX RANGE SCAN                     | CUST_ID_IDX |   119 |     |   6   (0)| 00:00:01 |          |      |

—————————————————————————————————————————-

 

Predicate Information (identified by operation id):

—————————————————

 

   4 – access((“CUST_ID”=7 OR “CUST_ID”=8 OR “CUST_ID”=9 OR “CUST_ID”=10))

 

 

21 rows selected.

 

SQL>

 

  1. Partial indexes

 

We will create the same indexes as partial indexes.

 

create index sales_idx_part on sales(time_id) indexing partial;

 

 

select  sum(amount_sold) from sales where time_id > TO_DATE(’01-JAN-2017′,’dd-MON-yyyy’) ;

 

 select * from table(dbms_xplan.display_cursor());

 

SQL> select  sum(amount_sold) from sales where time_id > TO_DATE(’01-JAN-2017′,’dd-MON-yyyy’) ;

 

 select * from table(dbms_xplan.display_cursor());

 

SUM(AMOUNT_SOLD)

—————-

       39689536

 

SQL> SQL>

PLAN_TABLE_OUTPUT

————————————————————————————————————————————————————————————————————————————————————————————————————

SQL_ID 5kbg4z2zgktqm, child number 0

————————————-

select sum(amount_sold) from sales where time_id >

TO_DATE(’01-JAN-2017′,’dd-MON-yyyy’)

 

Plan hash value: 1697128165

 

—————————————————————————————————–

| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

—————————————————————————————————–

|   0 | SELECT STATEMENT       |        |      |      |  1373 (100)|        |      |      |

|   1 |  SORT AGGREGATE        |        |    1 |  11 |         |         |      |      |

|   2 |   VIEW                 | VW_TE_2 |  801K|    9M|  1373   (1)| 00:00:01 |     |      |

|   3 |    UNION-ALL           |        |      |      |         |         |      |      |

|   4 |     PARTITION RANGE SINGLE|      |  801K|  8613K|  1373   (1)| 00:00:01 |   7 |    7 |

|*  5 |      TABLE ACCESS FULL         | SALES   |  801K|  8613K|  1373   (1)| 00:00:01 |   7 |    7 |

|*  6 |     FILTER             |        |      |      |         |         |      |      |

|   7 |      PARTITION RANGE ALL  |      |  3000K|  31M|  4662   (1)| 00:00:01 |   1 |    7 |

|   8 |       TABLE ACCESS FULL   | SALES   |  3000K|      31M|  4662   (1)| 00:00:01 |   1 |    7 |

—————————————————————————————————–

 

Predicate Information (identified by operation id):

 

PLAN_TABLE_OUTPUT

————————————————————————————————————————————————————————————————————————————————————————————————————

—————————————————

 

   5 – filter(“TIME_ID”>TO_DATE(‘ 2017-01-01 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’))

   6 – filter((NULL IS NOT NULL AND NULL IS NOT NULL))

 

 

27 rows selected.

 

SQL>

 

select  sum(amount_sold) from sales where time_id > TO_DATE(’01-JAN-2017′,’dd-MON-yyyy’) ;

 

 select * from table(dbms_xplan.display_cursor());

 

SQL>

select  sum(amount_sold) from sales where time_id > TO_DATE(’01-JAN-2017′,’dd-MON-yyyy’) ;

 

 select * from table(dbms_xplan.display_cursor());

SQL>

SUM(AMOUNT_SOLD)

—————-

       39689536

 

SQL> SQL>

PLAN_TABLE_OUTPUT

————————————————————————————————————————————————————————————————————————————————————————————————————

SQL_ID 5kbg4z2zgktqm, child number 0

————————————-

select sum(amount_sold) from sales where time_id >

TO_DATE(’01-JAN-2017′,’dd-MON-yyyy’)

 

Plan hash value: 1697128165

 

—————————————————————————————————–

| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

—————————————————————————————————–

|   0 | SELECT STATEMENT       |        |      |      |  1373 (100)|        |      |      |

|   1 |  SORT AGGREGATE        |        |    1 |  11 |         |         |      |      |

|   2 |   VIEW                 | VW_TE_2 |  801K|    9M|  1373   (1)| 00:00:01 |     |      |

|   3 |    UNION-ALL           |        |      |      |         |         |      |      |

|   4 |     PARTITION RANGE SINGLE|      |  801K|  8613K|  1373   (1)| 00:00:01 |   7 |    7 |

|*  5 |      TABLE ACCESS FULL         | SALES   |  801K|  8613K|  1373   (1)| 00:00:01 |   7 |    7 |

|*  6 |     FILTER             |        |      |      |         |         |      |      |

|   7 |      PARTITION RANGE ALL  |      |  3000K|  31M|  4662   (1)| 00:00:01 |   1 |    7 |

|   8 |       TABLE ACCESS FULL   | SALES   |  3000K|      31M|  4662   (1)| 00:00:01 |   1 |    7 |

—————————————————————————————————–

 

Predicate Information (identified by operation id):

 

PLAN_TABLE_OUTPUT

————————————————————————————————————————————————————————————————————————————————————————————————————

—————————————————

 

   5 – filter(“TIME_ID”>TO_DATE(‘ 2017-01-01 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’))

   6 – filter((NULL IS NOT NULL AND NULL IS NOT NULL))

 

 

27 rows selected.

 

SQL>

 

 

create index promo_id_idx on sales(promo_id)  indexing partial;

 

select  sum(amount_sold) from sales where promo_id=9 ;

 

 select * from table(dbms_xplan.display_cursor());

 

SQL>

select  sum(amount_sold) from sales where promo_id=9 ;

 

 select * from table(dbms_xplan.display_cursor());

SQL>

SUM(AMOUNT_SOLD)

—————-

             27

 

SQL> SQL>

PLAN_TABLE_OUTPUT

————————————————————————————————————————————————————————————————————————————————————————————————————

SQL_ID 33gsxqw7359y8, child number 0

————————————-

select sum(amount_sold) from sales where promo_id=9

 

Plan hash value: 2657317088

 

——————————————————————————————————————————

| Id  | Operation                                | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

——————————————————————————————————————————

|   0 | SELECT STATEMENT                         |            |      |      | 3297 (100)|         |           |      |

|   1 |  SORT AGGREGATE                          |            |    1 |    8 |          |         |           |      |

|   2 |   VIEW                                  | VW_TE_2      |    3 |   39 | 3297   (1)| 00:00:01 |           |      |

|   3 |    UNION-ALL                             |            |      |      |          |         |           |      |

|*  4 |     TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SALES           |    1 |   16 |    5   (0)| 00:00:01 | ROWID | ROWID |

|*  5 |      INDEX RANGE SCAN                     | PROMO_ID_IDX |    3 |      |    3   (0)| 00:00:01 |           |      |

|   6 |     PARTITION RANGE ITERATOR              |            |    2 |   32 | 3292   (1)| 00:00:01 |         1 |    6 |

|*  7 |      TABLE ACCESS FULL                           | SALES      |    2 |   32 | 3292   (1)| 00:00:01 |        1 |    6 |

——————————————————————————————————————————

 

Predicate Information (identified by operation id):

—————————————————

 

 

PLAN_TABLE_OUTPUT

————————————————————————————————————————————————————————————————————————————————————————————————————

   4 – filter((“SALES”.”TIME_ID”>=TO_DATE(‘ 2017-01-01 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’) OR “SALES”.”TIME_ID” IS

             NULL))

   5 – access(“PROMO_ID”=9)

   7 – filter(“PROMO_ID”=9)

 

 

27 rows selected.

 

SQL>

 

create index cust_id_idx on sales(cust_id)  indexing partial;

 

select  sum(amount_sold) from sales where cust_id=9 ;

 

 select * from table(dbms_xplan.display_cursor());

 

 

SQL> select  sum(amount_sold) from sales where cust_id=9 ;

 

 select * from table(dbms_xplan.display_cursor());

 

 

SUM(AMOUNT_SOLD)

—————-

            270

 

SQL> SQL>

PLAN_TABLE_OUTPUT

————————————————————————————————————————————————————————————————————————————————————————————————————

SQL_ID 0q4bsj3b9w919, child number 0

————————————-

select sum(amount_sold) from sales where cust_id=9

 

Plan hash value: 2147999346

 

—————————————————————————————————————————–

| Id  | Operation                                | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

—————————————————————————————————————————–

|   0 | SELECT STATEMENT                         |           |      |      |  3323 (100)|        |           |      |

|   1 |  SORT AGGREGATE                          |           |    1 |    8 |         |         |           |      |

|   2 |   VIEW                                  | VW_TE_2     | 30 |  390 |  3323   (1)| 00:00:01 |          |      |

|   3 |    UNION-ALL                             |           |      |      |         |         |           |      |

|*  4 |     TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SALES          |    8 |  128 |  33   (0)| 00:00:01 | ROWID | ROWID |

|*  5 |      INDEX RANGE SCAN                     | CUST_ID_IDX | 30 |      |    3   (0)| 00:00:01 |          |      |

|   6 |     PARTITION RANGE ITERATOR              |           |  22 |  352 |  3290   (1)| 00:00:01 |        1 |    6 |

|*  7 |      TABLE ACCESS FULL                           | SALES      |  22 |  352 |  3290   (1)| 00:00:01 |       1 |    6 |

—————————————————————————————————————————–

 

Predicate Information (identified by operation id):

—————————————————

 

 

PLAN_TABLE_OUTPUT

————————————————————————————————————————————————————————————————————————————————————————————————————

   4 – filter((“SALES”.”TIME_ID”>=TO_DATE(‘ 2017-01-01 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’) OR “SALES”.”TIME_ID” IS

             NULL))

   5 – access(“CUST_ID”=9)

   7 – filter(“CUST_ID”=9)

 

 

27 rows selected.

 

  1. Summary

The partial index feature enables us to be more flexible when using indexes. We can index only the column with the less popular values so that to be benefit from the index.

 

 

February 27, 2017 - Posted by | oracle

No comments yet.

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

%d bloggers like this: