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.

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;

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

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

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

| 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)

—————-

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

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

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

| 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

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

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

| 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

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

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

| 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

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

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

| 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>

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

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

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

| 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

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

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

| 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)

—————-

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

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

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

| 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

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

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

| 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.

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 gjilevski | oracle

No comments yet.

About

Guenadi Jilevski is a Senior Oracle Professional (OCP 7, 8, 8i, 9i, 10g, 11g, 12c OCE 10gR2 RAC, OCE Oracle RAC 11gR2 and Grid Infrastructure Administrator, Oracle Exadata 11g Certified Implementation Specialist,Oracle GoldenGate 10g/11g Certified Implementation Specialist and OCM 9i, OCM 11g) with more than 20 years of industrial experience in high end Oracle technology starting with Oracle RDBMS version 6 up to 11g. He has been involved with multi platform High Availability Solutions starting with OPS and Veritas and Sun cluster as well as complex multiple node RAC 9i, 10g and 11g implementations on AIX, Linux, Solaris and HP. He has experience in deploying disaster recovery solutions with standby databases compliant to Oracle MAA and third party solutions such as Shareplex and Goldengate. As a former employee of Oracle Corporation Advanced Technology Solutions Consulting Practice in Americas, Guenadi has been involved in multiple visible high end projects for RAC enabling EBS, Peoplesoft, Siebel ERP applications, end to end performance tuning, architectured High Availability and Disaster Recovery solutions to name a few. Presently he is working as A Senior Oracle Consultant in EMEA.

Guenadi Jilevski is Oracle OCP 7, 8, 8i, 9i, 10g, 11g, 12c OCE 10gR2 RAC, OCE Oracle RAC 11gR2 and Grid Infrastructure Administrator, Oracle Exadata 11g Certified Implementation Specialist,Oracle GoldenGate 10g/11g Certified Implementation Specialist and Oracle OCM9i , OCM 11g, Teradata Certified DBA, Informix Certified DBA.

The views expressed on this blog are my own and do not necessarily reflect the views of Current/Past/Future Employers. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect mine.

Archives
Archives
Blog Stats
- 1,111,840 hits
Top Rated
Blogroll
- WordPress.com 0
- WordPress.org 0
Calendar

February 2017

M T W T F S S

« Nov

1 2 3 4 5

6 7 8 9 10 11 12

13 14 15 16 17 18 19

20 21 22 23 24 25 26

27 28
Search

Search for:
Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 1,113 other followers
OraNA

Guenadi N Jilevski's Oracle BLOG

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