Oracle 12c introduced Partial indexing, which works well for simple partitioned tables with literals. However, it has several significant issues:
For instance, consider the following simple partitioned table:
create table t2 (
pkey int not null,
val int,
padding varchar2(100)
)
partition by range(pkey) (
partition p1_on values less than (2),
partition p2_on values less than (3),
partition p3_off values less than (4) indexing off,
partition p4_off values less than (5) indexing off,
partition p5_on values less than (6),
partition p6_off values less than (7) indexing off
);
insert into t2
with pkeys as (select level pkey from dual connect by level<=6)
,gen as (select level n from dual connect by level<=1000)
select pkey,n,rpad('x',100,'x')
from pkeys,gen;
create index ix_t2 on t2(pkey,val) local indexing partial;
select partition_name as pname,indexing
from user_tab_partitions p
where table_name='T2';
PNAME INDEXING
---------- --------
P1_ON ON
P2_ON ON
P3_OFF OFF
P4_OFF OFF
P5_ON ON
P6_OFF OFF
6 rows selected.
This table has 6 partitions (pkey from 1 to 6), each containing 1000 rows with different val (1…1000). And CBO works fine with this simple query:
SQL> select count(*) from t2 where pkey in (1,2,6) and val=5;
COUNT(*)
----------
3
SQL> @last ""
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID awkucugb6kwdy, child number 0
-------------------------------------
select count(*) from t2 where pkey in (1,2,6) and val=5
Plan hash value: 3293077569
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 276 (100)| | | | 1 |00:00:00.01 | 88 |
| 1 | SORT AGGREGATE | | 1 | 1 | 7 | | | | | 1 |00:00:00.01 | 88 |
| 2 | VIEW | VW_TE_2 | 1 | 3 | | 276 (0)| 00:00:01 | | | 3 |00:00:00.01 | 88 |
| 3 | UNION-ALL | | 1 | | | | | | | 3 |00:00:00.01 | 88 |
| 4 | INLIST ITERATOR | | 1 | | | | | | | 2 |00:00:00.01 | 4 |
| 5 | PARTITION RANGE ITERATOR| | 2 | 2 | 14 | 2 (0)| 00:00:01 |KEY(I) |KEY(I) | 2 |00:00:00.01 | 4 |
|* 6 | INDEX RANGE SCAN | IX_T2 | 2 | 2 | 14 | 2 (0)| 00:00:01 |KEY(I) |KEY(I) | 2 |00:00:00.01 | 4 |
| 7 | PARTITION RANGE SINGLE | | 1 | 1 | 7 | 274 (0)| 00:00:01 | 6 | 6 | 1 |00:00:00.01 | 84 |
|* 8 | TABLE ACCESS FULL | T2 | 1 | 1 | 7 | 274 (0)| 00:00:01 | 6 | 6 | 1 |00:00:00.01 | 84 |
--------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$AE9E49E8
2 - SET$A73639E0 / VW_TE_2@SEL$AE9E49E8
3 - SET$A73639E0
4 - SET$A73639E0_1
6 - SET$A73639E0_1 / T2@SEL$1
7 - SET$A73639E0_2
8 - SET$A73639E0_2 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access((("PKEY"=1 OR "PKEY"=2)) AND "VAL"=5)
8 - filter(("VAL"=5 AND "PKEY"=6))
As seen above, the CBO opts for the “TABLE EXPANSION” transformation and selects IRS (Index Range Scan) for partitions P1 and P2, and FTS for partition P6. However, when we modify pkey in (1,2,6) to pkey in (1,2), the result is:
select count(*) from t2 where pkey in (1,2) and val=5
Plan hash value: 37798356
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 547 (100)| | | | 1 |00:00:00.01 | 168 |
| 1 | SORT AGGREGATE | | 1 | 1 | 7 | | | | | 1 |00:00:00.01 | 168 |
| 2 | PARTITION RANGE INLIST| | 1 | 2 | 14 | 547 (0)| 00:00:01 |KEY(I) |KEY(I) | 2 |00:00:00.01 | 168 |
|* 3 | TABLE ACCESS FULL | T2 | 2 | 2 | 14 | 547 (0)| 00:00:01 |KEY(I) |KEY(I) | 2 |00:00:00.01 | 168 |
------------------------------------------------------------------------------------------------------------------------------------------
This query produces a less efficient plan with 2 FTS, even though it’s simpler and scans one fewer partition than the initial query. Comparing the CBO traces reveals that it skips TABLE EXPANSION transformation in such straightforward cases:
Full test case if you want to play with this example: https://gist.github.com/xtender/5992a9fbf7c088c3bfd68735f75ffdb1
Partial indexing and Composite partitioning
Another issue arises when dealing with partial indexing in conjunction with composite partitioning. The CBO is not yet fully equipped to handle these scenarios.
Consider the following table:
create table t_partial (
dt date,
spkey varchar2(1),
val int,
padding varchar2(100)
)
partition by range (dt)
subpartition by list (spkey)
subpartition template (
subpartition p_a values ('A')
, subpartition p_b values ('B')
, subpartition p_c values ('C')
)
(
partition p_1_on values less than (date'2022-11-02')
,partition p_2_on values less than (date'2022-11-03')
,partition p_3_off values less than (date'2022-11-04')
,partition p_4_off values less than (date'2022-11-05')
,partition p_5_on values less than (date'2022-11-06')
)
/
insert into t_partial(dt,spkey,val,padding)
with dates(dt) as (select date'2022-11-01'+level-1 from dual connect by level<6)
,gen as (select n from xmltable('0 to 10000' columns n int path '.'))
select
dt,
case when n<5000 then 'A'
when n<10000 then 'C'
else 'B'
end as spkey,
n,
rpad('x',100,'x') as padding
from dates,gen
/
The data distribution is as follows:
select dt,spkey,count(*) from t_partial group by dt,spkey order by 1,2
/
DT S COUNT(*)
------------------- - ----------
2022-11-01 00:00:00 A 10000
2022-11-01 00:00:00 B 10000
2022-11-01 00:00:00 C 10000
2022-11-02 00:00:00 A 10000
2022-11-02 00:00:00 B 10000
2022-11-02 00:00:00 C 10000
2022-11-03 00:00:00 A 10000
2022-11-03 00:00:00 B 10000
2022-11-03 00:00:00 C 10000
2022-11-04 00:00:00 A 10000
2022-11-04 00:00:00 B 10000
2022-11-04 00:00:00 C 10000
2022-11-05 00:00:00 A 10000
2022-11-05 00:00:00 B 10000
2022-11-05 00:00:00 C 10000
All subpartitions contain 10000 rows (val from 1 to 10000), and they are currently set to indexing=on:
SQL> col p_name for a10;
SQL> col subp_name for a12;
SQL> col indexing for a8;
SQL> select
2 partition_position as p_n
3 ,partition_name as p_name
4 ,subpartition_position as subp_n
5 ,subpartition_name as subp_name
6 ,indexing
7 from user_tab_subpartitions
8 where table_name='T_PARTIAL'
9 /
P_N P_NAME SUBP_N SUBP_NAME INDEXING
---------- ---------- ---------- ------------ --------
1 P_1_ON 1 P_1_ON_P_A ON
1 P_1_ON 2 P_1_ON_P_B ON
1 P_1_ON 3 P_1_ON_P_C ON
2 P_2_ON 1 P_2_ON_P_A ON
2 P_2_ON 2 P_2_ON_P_B ON
2 P_2_ON 3 P_2_ON_P_C ON
3 P_3_OFF 1 P_3_OFF_P_A ON
3 P_3_OFF 2 P_3_OFF_P_B ON
3 P_3_OFF 3 P_3_OFF_P_C ON
4 P_4_OFF 1 P_4_OFF_P_A ON
4 P_4_OFF 2 P_4_OFF_P_B ON
4 P_4_OFF 3 P_4_OFF_P_C ON
5 P_5_ON 1 P_5_ON_P_A ON
5 P_5_ON 2 P_5_ON_P_B ON
5 P_5_ON 3 P_5_ON_P_C ON
Disabling indexing on the partition level is not possible:
SQL> alter table t_partial modify partition P_1_ON indexing on
2 /
alter table t_partial modify partition P_1_ON indexing on
*
ERROR at line 1:
ORA-14229: cannot modify the indexing attribute of a composite partition
So let’s disable indexing for subpartitions ‘A’ and ‘C’ in partitions P3 and P4 and create a partial index:
SQL> begin
2 for r in (
3 select subpartition_name sname
4 from user_tab_subpartitions
5 where table_name='T_PARTIAL'
6 and partition_name like '%OFF'
7 and subpartition_name not like '%B'
8 ) loop
9 execute immediate
10 'alter table t_partial modify subpartition '||r.sname||' indexing off';
11 end loop;
12 end;
13 /
PL/SQL procedure successfully completed.
SQL> select
2 partition_position as p_n
3 ,partition_name as p_name
4 ,subpartition_position as subp_n
5 ,subpartition_name as subp_name
6 ,indexing
7 from user_tab_subpartitions
8 where table_name='T_PARTIAL'
9 /
P_N P_NAME SUBP_N SUBP_NAME INDEXING
---------- ---------- ---------- ------------ --------
1 P_1_ON 1 P_1_ON_P_A ON
1 P_1_ON 2 P_1_ON_P_B ON
1 P_1_ON 3 P_1_ON_P_C ON
2 P_2_ON 1 P_2_ON_P_A ON
2 P_2_ON 2 P_2_ON_P_B ON
2 P_2_ON 3 P_2_ON_P_C ON
3 P_3_OFF 1 P_3_OFF_P_A OFF
3 P_3_OFF 2 P_3_OFF_P_B ON
3 P_3_OFF 3 P_3_OFF_P_C OFF
4 P_4_OFF 1 P_4_OFF_P_A OFF
4 P_4_OFF 2 P_4_OFF_P_B ON
4 P_4_OFF 3 P_4_OFF_P_C OFF
5 P_5_ON 1 P_5_ON_P_A ON
5 P_5_ON 2 P_5_ON_P_B ON
5 P_5_ON 3 P_5_ON_P_C ON
SQL> create index ix_partial on t_partial(spkey,val,dt) local indexing partial
2 /
Index created.
SQL> exec dbms_stats.gather_table_stats(ownname => user,tabname => 'T_PARTIAL', cascade=>true);
PL/SQL procedure successfully completed.
Now, examine these 2 simple queries, each requesting just 1 day – 2022-11-03 and 2022-11-04:
select count(val)
from t_partial t
where spkey='B' and val=3
and t.dt >= date'2022-11-03' and t.dt < date'2022-11-04';
select count(val)
from t_partial t
where spkey='B' and val=3
and t.dt >= date'2022-11-04' and t.dt < date'2022-11-05';
Both of them produce the same efficient execution plan with IRS:
---------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
---------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 2 | | | |
| 1 | SORT AGGREGATE | | 1 | 14 | | | | |
| 2 | PARTITION RANGE SINGLE | | 1 | 14 | 2 | 00:00:01 | 3 | 3 |
| 3 | PARTITION LIST SINGLE | | 1 | 14 | 2 | 00:00:01 | 2 | 2 |
| 4 | INDEX RANGE SCAN | IX_PARTIAL| 1 | 14 | 2 | 00:00:01 | 8 | 8 |
---------------------------------------------+-----------------------------------+---------------+
Query Block Name / Object Alias (identified by operation id):
------------------------------------------------------------
1 - SEL$1
4 - SEL$1 / "T"@"SEL$1"
------------------------------------------------------------
Predicate Information:
----------------------
4 - access("SPKEY"='B' AND "VAL"=3 AND "T"."DT">=TO_DATE(' 2022-11-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."DT"<TO_DATE(' 2022-11-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
---------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
---------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 2 | | | |
| 1 | SORT AGGREGATE | | 1 | 14 | | | | |
| 2 | PARTITION RANGE SINGLE | | 1 | 14 | 2 | 00:00:01 | 4 | 4 |
| 3 | PARTITION LIST SINGLE | | 1 | 14 | 2 | 00:00:01 | 2 | 2 |
| 4 | INDEX RANGE SCAN | IX_PARTIAL| 1 | 14 | 2 | 00:00:01 | 11 | 11 |
---------------------------------------------+-----------------------------------+---------------+
Query Block Name / Object Alias (identified by operation id):
------------------------------------------------------------
1 - SEL$1
4 - SEL$1 / "T"@"SEL$1"
------------------------------------------------------------
Predicate Information:
----------------------
4 - access("SPKEY"='B' AND "VAL"=3 AND "T"."DT">=TO_DATE(' 2022-11-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."DT"<TO_DATE(' 2022-11-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
However, attempting to select both days in a single query results in FTS:
select count(val)
from t_partial t
where spkey='B' and val=3
and t.dt >= date'2022-11-03' and t.dt < date'2022-11-05';
----------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
----------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 548 | | | |
| 1 | SORT AGGREGATE | | 1 | 14 | | | | |
| 2 | PARTITION RANGE ITERATOR | | 2 | 28 | 548 | 00:00:07 | 3 | 4 |
| 3 | PARTITION LIST SINGLE | | 2 | 28 | 548 | 00:00:07 | 2 | 2 |
| 4 | TABLE ACCESS FULL | T_PARTIAL| 2 | 28 | 548 | 00:00:07 | KEY | KEY |
----------------------------------------------+-----------------------------------+---------------+
Query Block Name / Object Alias (identified by operation id):
------------------------------------------------------------
1 - SEL$1
4 - SEL$1 / "T"@"SEL$1"
------------------------------------------------------------
Predicate Information:
----------------------
4 - filter("VAL"=3)
Comparing their optimizer traces reveals that that CBO disregards the partial index in case of partition range iterator, if at least 1 subpartition in that range is “unusable”:
Additionally, the same issue occurs with composite partitioning when using bind variables: CBO ignores partial indexes when bind variables are used, even if there is only 1 partition, as in the first example with literals:
declare
dt_1 date:=date'2022-11-03';
dt_2 date:=date'2022-11-04';
cnt int;
begin
select count(val)
into cnt
from t_partial t
where spkey='B' and val=3 and t.dt >= dt_1 and t.dt < dt_2;
end;
/
Index Stats::
Index: IX_PARTIAL Col#: 2 3 1 PARTITION [2]
LVLS: 1 #LB: 35 #DK: 10000 LB/K: 1.00 DB/K: 1.00 CLUF: 170.00 NRW: 10000.00 SSZ: 10000.00 LGR: 0.00 CBK: 0.00 GQL: 100.00 CHR: 0.00 KQDFLG: 0 BSZ: 1
KKEISFLG: 1
LVLS: 1 #LB: 35 #DK: 10000 LB/K: 1.00 DB/K: 1.00 CLUF: 170.00 NRW: 10000.00 SSZ: 10000.00 LGR: 0.00 CBK: 0.00 GQL: 100.00 CHR: 0.00 KQDFLG: 0 BSZ: 1
KKEISFLG: 1
UNUSABLE
----- Current SQL Statement for this session (sql_id=3xj8t99km1zkz) -----
SELECT COUNT(VAL) FROM T_PARTIAL T WHERE SPKEY='B' AND VAL=3 AND T.DT >= :B2 AND T.DT < :B1
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x75dd2cc0 6 anonymous block
sql_text_length=93
sql=SELECT COUNT(VAL) FROM T_PARTIAL T WHERE SPKEY='B' AND VAL=3 AND T.DT >= :B2 AND T.DT < :B1
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
-----------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
-----------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 1365 | | | |
| 1 | SORT AGGREGATE | | 1 | 14 | | | | |
| 2 | FILTER | | | | | | | |
| 3 | PARTITION RANGE ITERATOR | | 1 | 14 | 1365 | 00:00:17 | KEY | KEY |
| 4 | PARTITION LIST SINGLE | | 1 | 14 | 1365 | 00:00:17 | 2 | 2 |
| 5 | TABLE ACCESS FULL | T_PARTIAL| 1 | 14 | 1365 | 00:00:17 | KEY | KEY |
-----------------------------------------------+-----------------------------------+---------------+
Query Block Name / Object Alias (identified by operation id):
------------------------------------------------------------
1 - SEL$1
5 - SEL$1 / "T"@"SEL$1"
------------------------------------------------------------
Predicate Information:
----------------------
2 - filter(:B1>:B2)
5 - filter(("VAL"=3 AND "T"."DT">=:B2 AND "T"."DT"<:B1))
Full test case with traces: https://gist.github.com/xtender/e453510c1a23ef6e5ca3beb7af4d30d7
To address these problems, there are two workarounds:
- Use the parameter
skip_unusable_indexes - Use the hint
index
However, both workarounds come with their drawbacks:
- it’s not possible to use a hint
opt_param('skip_unusable_indexes' 'false'), so you will need to change it on the session level. - In the case of
indexhint, you may encounter “ORA-01502: index '...' or partition of such index is in unusable state“, if your bind variables switch to partitions with “indexing off“.
So before implementing partial indexing, make sure to test it thoroughly to avoid potential issues with composite partitioning and certain queries, ensuring your database runs smoothly.