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
————————————————————————————————-
| 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>
- 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.
- 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.
No comments yet.
-
Archives
- February 2017 (1)
- November 2016 (1)
- October 2016 (1)
- May 2016 (2)
- March 2016 (3)
- December 2014 (2)
- July 2014 (1)
- June 2014 (6)
- May 2014 (5)
- February 2014 (1)
- December 2012 (2)
- November 2012 (8)
-
Categories
-
RSS
Entries RSS
Comments RSS
Leave a Reply