One of the new capabilities of Oracle 12c database is the
ability to create both local and global index on only a subset of partitions
within the partitioned table. This give us the flexibility to say only create
partitions with data that would make sense to index, to not index current
partitions where data insert performance is important. Additionally and importantly the CBO is aware
of the indexing characteristics of individual partitions and can access
partitions in different manners.
To illustrate this I’ll create a partitioned table with
INDEXING clause.
rajesh@PDB1> create
table t
2
INDEXING OFF
3
partition by list(x)
4 (
5
partition p1 values (1) ,
6
partition p2 values (2) INDEXING OFF,
7
partition p3 values (3) INDEXING ON
8 )
9 as
10
select object_id,object_name,object_type,
11
owner,status,1 as x from all_objects a
12
union all
13
select object_id,object_name,object_type,
14
owner,status,2 as x from all_objects a
15
union all
16
select object_id,object_name,object_type,owner,
17
CASE when rownum <=100 then 'OPEN' else 'CLOSED' end status,
18 3
as x from all_objects a ;
Table created.
The INDEXING clause determines whether or not the partitioned to be
Indexed. It can be set at the table
level and so set the default behavior for the table or at the individual partitions
/ sub partition level
In the above example, I’ve set the INDEXING OFF at the table level so
indexing by default is not enabled for the table partitions. Therefore P1 is not indexed by default, and
P2 is explicitly set to not be indexed, but P3 is explicitly set (INDEXING ON)
to enable indexing and so override the table level default.
Most of the data in status column value is ‘CLOSED’ but few rows in
last partition are set to ‘OPEN’ status. Now let’s create index on the status
column and collect table statistics.
rajesh@PDB1> create
index t_ind on t(status) ;
Index created.
rajesh@PDB1> begin
2
dbms_stats.gather_table_stats
3
(user,'T',
4
method_opt=>'for all indexed columns size 254');
5 end;
6 /
PL/SQL procedure
successfully completed.
rajesh@PDB1> select
table_name,partition_name,num_rows
2 from
user_tab_partitions
3
where table_name ='T';
TABLE_NAME PARTITION_NAME NUM_ROWS
--------------------
-------------------- ----------
T P1 89721
T P2 89721
T P3 89721
3 rows selected.
rajesh@PDB1> select
index_name,num_rows,leaf_blocks,indexing
2 from
user_indexes
3
where index_name ='T_IND';
INDEX_NAME NUM_ROWS LEAF_BLOCKS INDEXIN
--------------------
---------- ----------- -------
T_IND 269163 800 FULL
1 row selected.
rajesh@PDB1>
By default the index will include all rows from the table regardless of
the indexing clause settings. So this index will cover up all rows in the table
and currently has 800 leaf blocks, the new INDEXING clause in user_indexes
shows this index is FULL (non-partial) index.
Ofcourse we can get the data of interest via this index now.
rajesh@PDB1> select *
from t where status='OPEN' ;
100 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 540421662
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Pstart| Pstop |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
100 | | |
| 1 |
TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T |
100 | ROWID | ROWID |
|* 2 |
INDEX RANGE SCAN
| T_IND | 100 | |
|
------------------------------------------------------------------------------------
Predicate Information
(identified by operation id):
---------------------------------------------------
2 - access("STATUS"='OPEN')
Statistics
----------------------------------------------------------
0
recursive calls
0
db block gets
7
consistent gets
0
physical reads
0
redo size
4221
bytes sent via SQL*Net to client
366
bytes received via SQL*Net from client
2
SQL*Net roundtrips to/from client
0
sorts (memory)
0
sorts (disk)
100
rows processed
rajesh@PDB1>
We can potentially run the query based on just the last partition,
since all OPEN status resides only on the last partition.
rajesh@PDB1> select *
from t where status='OPEN' and x = 3;
100 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value:
1582437356
------------------------------------------------------------------------------------
| Id | Operation | Name
| Rows | Pstart| Pstop |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | | |
|* 1 |
TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T |
1 | 3 | 3 |
|* 2 |
INDEX RANGE SCAN
| T_IND | 100 | |
|
------------------------------------------------------------------------------------
Predicate Information
(identified by operation id):
---------------------------------------------------
1 - filter("X"=3)
2 - access("STATUS"='OPEN')
Statistics
----------------------------------------------------------
0
recursive calls
0
db block gets
7
consistent gets
0
physical reads
0
redo size
2809
bytes sent via SQL*Net to client
366
bytes received via SQL*Net from client
2
SQL*Net roundtrips to/from client
0
sorts (memory)
0
sorts (disk)
100
rows processed
Currently the index includes data from all table partitions even
though we are only interested in using
the index to retrieve the less common OPEN status that resides in last
partition. With 12c there is now the capability to index only the partition
that are of interest to us, with which proper design can also be implemented
such that only those columns values of interest are included in the index.
Now lets drop and recreate the index as Partial index.
rajesh@PDB1>
rajesh@PDB1> drop index
t_ind;
Index dropped.
rajesh@PDB1> create
index t_ind on t(status) indexing partial;
Index created.
rajesh@PDB1> select
index_name,num_rows,leaf_blocks,indexing
2 from
user_indexes
3
where index_name ='T_IND';
INDEX_NAME NUM_ROWS LEAF_BLOCKS INDEXIN
--------------------
---------- ----------- -------
T_IND 89721 276 PARTIAL
1 row selected.
rajesh@PDB1>
The new INDEXING PARTIAL clause means only those table partitions with
INDEXING ON are included in the index. Notice
how the index, which is global non-partitioned has only 89K rows and with 276 leaf blocks which is only 1/3 of what it was previously. The indexing
column now denotes this as a Partial index.
If we run a query against the only explicitly references the last
active partitions,
rajesh@PDB1> select *
from t where status='OPEN' and x = 3;
100 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value:
1582437356
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Pstart| Pstop |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | | |
|* 1 |
TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T |
1 | 3 | 3 |
|* 2 |
INDEX RANGE SCAN
| T_IND | 33 | |
|
-------------------------------------------------------------------------------------
Predicate Information
(identified by operation id):
---------------------------------------------------
1 - filter("X"=3)
2 - access("STATUS"='OPEN')
Statistics
----------------------------------------------------------
0
recursive calls
0
db block gets
6
consistent gets
0
physical reads
0
redo size
2809
bytes sent via SQL*Net to client
366
bytes received via SQL*Net from client
2
SQL*Net roundtrips to/from client
0
sorts (memory)
0
sorts (disk)
100
rows processed
rajesh@PDB1>
we see that index is used as it was previously, by stating with x=3
predicate we are only interested in data that resides in last partition. The CBO
know that index can be used to retrieve all the rows of interest. However if its possible within the
application to search for OPEN status across all partitions that have INDEXING
OFF.
rajesh@PDB1> select *
from t where status='OPEN' ;
100 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value:
3530799610
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Pstart| Pstop |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
100 | | |
| 1 |
VIEW |
VW_TE_2 | 68 | |
|
| 2 |
UNION-ALL | |
| | |
|* 3 |
TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T |
1 | 3 | 3 |
|* 4 |
INDEX RANGE SCAN
| T_IND | 33 |
| |
| 5 |
PARTITION LIST INLIST
| | 67 | KEY(I) |KEY(I) |
|* 6 |
TABLE ACCESS FULL
| T | 67 | KEY(I) |KEY(I) |
-----------------------------------------------------------------------------------------
Predicate Information
(identified by operation id):
---------------------------------------------------
3 - filter("T"."X"=3)
4 - access("STATUS"='OPEN')
6 - filter("STATUS"='OPEN')
Statistics
----------------------------------------------------------
0
recursive calls
0
db block gets
1460
consistent gets
0
physical reads
0
redo size
2809
bytes sent via SQL*Net to client
366
bytes received via SQL*Net from client
2
SQL*Net roundtrips to/from client
0
sorts (memory)
0
sorts (disk)
100
rows processed
rajesh@PDB1>
We notice the index is still used to efficiently find those rows of
interest from the last partition, but a Full Table (Partition) Scan is
performed to search for data from the other two partitions, for which with
INDEXING OFF means the index does not contain entries that reference these
partitions. As a result, this query is now much more expensive than it was
previously as the index cannot be used to exclusively find the rows of interest.
The CBO within the one execution plan uses the index where it can and a full
scan of the other partitions where it can’t use the index.
If however we were a little cleverer in how we designed our table and
also created table subpartitions based on the STATUS column so that rows of
interest resided in their own subpartitions and only set INDEXING ON for these
subpartitions:
rajesh@PDB1> create
table t
2
INDEXING OFF
3
partition by list(x)
4
subpartition by list(status)
5
subpartition template
6 (
7
subpartition sp_open values ('OPEN') INDEXING ON,
8
subpartition sp_others values (DEFAULT)
9 )
10 (
11
partition p1 values (1) ,
12
partition p2 values (2) ,
13
partition p3 values (3)
14 )
15
enable row movement
16 as
17
select object_id,object_name,object_type,
18
owner,status,1 as x from all_objects a
19
union all
20
select object_id,object_name,object_type,
21
owner,status,2 as x from all_objects a
22
union all
23
select object_id,object_name,object_type,owner,
24
CASE when rownum <=100 then 'OPEN' else 'CLOSED' end status,
25 3
as x from all_objects a ;
Table created.
rajesh@PDB1>
Notice how only the sub partitions with status of OPEN are now be
indexed.
rajesh@PDB1> create
index t_ind on t(status) indexing partial;
Index created.
rajesh@PDB1> begin
2
dbms_stats.gather_table_stats
3
(user,'T',
4
method_opt=>'for all indexed columns size 254');
5 end;
6 /
PL/SQL procedure
successfully completed.
rajesh@PDB1> column
subpartition_name format a20
rajesh@PDB1> select
table_name,partition_name,subpartition_name,indexing
2 from
user_tab_subpartitions
3
where table_name ='T';
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME IND
--------------------
-------------------- -------------------- ---
T P3 P3_SP_OPEN ON
T P3 P3_SP_OTHERS OFF
T P2 P2_SP_OPEN ON
T P2 P2_SP_OTHERS OFF
T P1 P1_SP_OPEN ON
T P1 P1_SP_OTHERS OFF
6 rows selected.
rajesh@PDB1>
rajesh@PDB1> select
index_name,num_rows,leaf_blocks,indexing
2 from
user_indexes
3
where index_name ='T_IND';
INDEX_NAME NUM_ROWS LEAF_BLOCKS INDEXIN
--------------------
---------- ----------- -------
T_IND 100 1 PARTIAL
1 row selected.
rajesh@PDB1>
We can see that only the sub partitions with data of interest are now
indexed. The resultant partial global non-partitioned index is tiny now. With just
100 index entries of interest residing in Single index leaf block.
Just as importantly, a query searching for this data across the whole
table is now extremely efficient and can be fully serviced by this tiny Partial
index:
rajesh@PDB1> select *
from t where status=‘OPEN’;
100 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 540421662
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Pstart| Pstop |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
100 | | |
| 1 |
TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T |
100 | ROWID | ROWID |
|* 2 |
INDEX RANGE SCAN
| T_IND | 100 | |
|
------------------------------------------------------------------------------------
Predicate Information
(identified by operation id):
---------------------------------------------------
2 - access("STATUS"='OPEN')
Statistics
----------------------------------------------------------
0
recursive calls
0
db block gets
4
consistent gets
0
physical reads
0
redo size
4221
bytes sent via SQL*Net to client
366
bytes received via SQL*Net from client
2
SQL*Net roundtrips to/from client
0
sorts (memory)
0
sorts (disk)
100
rows processed
rajesh@PDB1>
The query can now be fully serviced by the Partial index as all
subpartitions that could contain data of interest are indexed and because the
data of interest is all neatly clustered within the table subpartitions, can be
retrieved with far fewer consistent gets than previously.
