Oracle 12c release introduces the brand new and impressive feature – partial indexes on partitioned tables. Richard Foote spent extremely useful articles to this aspect of 12c:
12c Partial Indexes For Partitioned Tables Part I (Ignoreland)
12c Partial Indexes For Partitioned Tables Part II (Vanishing Act)
And one of the most attractive features is CBO ability to use index access where possible and partition full scan there index can not be used in one statement. But does this always work ?
Let’s make few experiments for LIST partitioned tables.
SQL> create table t (x int, y int)
2 partition by list (y) (
3 partition p_1 values(1)
4 )
5 /
SQL> begin
2 for i in 2..8 loop
3 execute immediate
4 'alter table t add partition p_'||i||' values('||i||')';
5 end loop;
6 end;
7 /
SQL> alter table t modify partition p_2 indexing off
2 /
SQL> create sequence t_seq
2 /
SQL> insert into t select t_seq.nextval, mod(rownum, 8)+1
2 from dual connect by level commit
2 /
SQL> create index t_i on t(x) indexing partial
2 /
SQL> begin
2 dbms_stats.gather_table_stats(ownname => user,tabname => 'T',cascade => true);
3 end;
4 /
SQL> select * from t where x = 10
2 /
X Y
---------- ----------
10 3
SQL> select * from table(dbms_xplan.display_cursor)
2 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 61c22yvcwkaut, child number 0
-------------------------------------
select * from t where x = 10
Plan hash value: 1177543047
--------------------------------------------------------------------------------
----------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time | Pstart| Pstop |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
| 0 | SELECT STATEMENT | | | |
71 (100)| | | |
| 1 | VIEW | VW_TE_2 | 2 | 52 |
71 (2)| 00:00:01 | | |
| 2 | UNION-ALL | | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| | | |
|* 3 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T | 1 | 8 |
4 (0)| 00:00:01 | ROWID | ROWID |
|* 4 | INDEX RANGE SCAN | T_I | 1 | |
3 (0)| 00:00:01 | | |
| 5 | PARTITION LIST SINGLE | | 1 | 8 |
67 (2)| 00:00:01 | 2 | 2 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 6 | TABLE ACCESS FULL | T | 1 | 8 |
67 (2)| 00:00:01 | 2 | 2 |
--------------------------------------------------------------------------------
----------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("T"."Y"=1 OR "T"."Y"=3 OR "T"."Y"=4 OR "T"."Y"=5 OR "T"."Y"=6 OR
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"T"."Y"=7 OR "T"."Y"=8))
4 - access("X"=10)
6 - filter("X"=10)
As expected CBO uses the combination of the index access and partition full scan. But we should take into special account the FILTER clause in EXPLAIN PLAN – it consists of 7 comparison predicates with OR between them.
Now let’s increase the number of partitions but still leave just one INDEXING OFF. Get the result for 1001 partitions (I omit most of statements – they are similar with above scenario):
SQL> create table t (x int, y int)
2 partition by list (y) (
3 partition p_1 values(1)
4 )
5 /
SQL> begin
2 for i in 2..1001 loop
3 execute immediate
4 'alter table t add partition p_'||i||' values('||i||')';
5 end loop;
6 end;
7 /
SQL> alter table t modify partition p_2 indexing off
2 /
...
SQL> select * from t where x = 10
2 /
X Y
---------- ----------
10 11
SQL>
SQL> select * from table(dbms_xplan.display_cursor)
2 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 61c22yvcwkaut, child number 0
-------------------------------------
select * from t where x = 10
Plan hash value: 1177543047
--------------------------------------------------------------------------------
----------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time | Pstart| Pstop |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
| 0 | SELECT STATEMENT | | | |
18 (100)| | | |
| 1 | VIEW | VW_TE_2 | 2 | 52 |
18 (0)| 00:00:01 | | |
| 2 | UNION-ALL | | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| | | |
|* 3 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T | 1 | 9 |
4 (0)| 00:00:01 | ROWID | ROWID |
|* 4 | INDEX RANGE SCAN | T_I | 1 | |
3 (0)| 00:00:01 | | |
| 5 | PARTITION LIST SINGLE | | 1 | 8 |
14 (0)| 00:00:01 | 2 | 2 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 6 | TABLE ACCESS FULL | T | 1 | 8 |
14 (0)| 00:00:01 | 2 | 2 |
--------------------------------------------------------------------------------
----------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("T"."Y"=1 OR "T"."Y"=3 OR "T"."Y"=4 OR "T"."Y"=5 OR "T"."Y"=6 OR
Easy to see the optimization is still actual. But let’s add one more partition.
SQL> create table t (x int, y int)
2 partition by list (y) (
3 partition p_1 values(1)
4 )
5 /
SQL> begin
2 for i in 2..1002 loop
3 execute immediate
4 'alter table t add partition p_'||i||' values('||i||')';
5 end loop;
6 end;
7 /
...
SQL> select * from t where x = 10
2 /
select * from t where x = 10
*
error in row 1:
ORA-01795: maximum expression number in list - 1000
Oooops… We’ve got the error !
This has the natural explanation – we have 1002 partitions total and just one is INDEXING OFF.
Oracle tries to execute the in-list filter with 1001 element what causes the error.
Another example shows what we can get full scan just adding DEFAULT partition to the list:
SQL> create table t (x int, y int)
2 partition by list (y) (
3 partition p_1 values(1)
4 )
5 /
SQL> begin
2 for i in 2..8 loop
3 execute immediate
4 'alter table t add partition p_'||i||' values('||i||')';
5 end loop;
6 end;
7 /
SQL> alter table t add partition p_default values(default)
2 /
SQL> alter table t modify partition p_2 indexing off
2 /
SQL> create sequence t_seq
2 /
SQL> insert into t select t_seq.nextval, mod(rownum, 8)+1
2 from dual connect by level commit
2 /
SQL> create index t_i on t(x) indexing partial
2 /
SQL> begin
2 dbms_stats.gather_table_stats(ownname => user,tabname => 'T',cascade => true);
3 end;
4 /
SQL> select * from t where x = 10
2 /
X Y
---------- ----------
10 3
SQL>
SQL> select * from table(dbms_xplan.display_cursor)
2 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 61c22yvcwkaut, child number 0
-------------------------------------
select * from t where x = 10
Plan hash value: 3845649146
--------------------------------------------------------------------------------
-----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Psta
rt| Pstop |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------
| 0 | SELECT STATEMENT | | | | 522 (100)| |
| |
| 1 | PARTITION LIST ALL| | 1 | 8 | 522 (1)| 00:00:01 |
1 | 9 |
|* 2 | TABLE ACCESS FULL| T | 1 | 8 | 522 (1)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 | 9 |
--------------------------------------------------------------------------------
-----------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("X"=10)
Seems Oracle can not compose clear in-list filter due to undefined DEFAULT value. But we can
get the situation back if we mark DEFAULT partition as non-indexed:
SQL> create table t (x int, y int)
2 partition by list (y) (
3 partition p_1 values(1)
4 )
5 /
SQL> begin
2 for i in 2..8 loop
3 execute immediate
4 'alter table t add partition p_'||i||' values('||i||')';
5 end loop;
6 end;
7 /
SQL> alter table t add partition p_default values(default)
2 /
SQL> alter table t modify partition p_2 indexing off
2 /
SQL> alter table t modify partition p_default indexing off
2 /
SQL> create sequence t_seq
2 /
SQL> insert into t select t_seq.nextval, mod(rownum, 8)+1
2 from dual connect by level commit
2 /
SQL> create index t_i on t(x) indexing partial
2 /
SQL> begin
2 dbms_stats.gather_table_stats(ownname => user,tabname => 'T',cascade => true);
3 end;
4 /
SQL> select * from t where x = 10
2 /
X Y
---------- ----------
10 3
SQL>
SQL> select * from table(dbms_xplan.display_cursor)
2 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 61c22yvcwkaut, child number 0
-------------------------------------
select * from t where x = 10
Plan hash value: 1776253938
--------------------------------------------------------------------------------
----------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time | Pstart| Pstop |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
| 0 | SELECT STATEMENT | | | |
71 (100)| | | |
| 1 | VIEW | VW_TE_2 | 2 | 52 |
71 (2)| 00:00:01 | | |
| 2 | UNION-ALL | | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| | | |
|* 3 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T | 1 | 8 |
4 (0)| 00:00:01 | ROWID | ROWID |
|* 4 | INDEX RANGE SCAN | T_I | 1 | |
3 (0)| 00:00:01 | | |
| 5 | PARTITION LIST OR | | 1 | 8 |
67 (2)| 00:00:01 |KEY(OR)|KEY(OR)|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 6 | TABLE ACCESS FULL | T | 1 | 8 |
67 (2)| 00:00:01 |KEY(OR)|KEY(OR)|
--------------------------------------------------------------------------------
----------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("T"."Y"=1 OR "T"."Y"=3 OR "T"."Y"=4 OR "T"."Y"=5 OR "T"."Y"=6 OR
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"T"."Y"=7 OR "T"."Y"=8))
4 - access("X"=10)
6 - filter(("X"=10 AND (("T"."Y"1 AND "T"."Y"3 AND "T"."Y"4 AND "T"."Y"
5 AND "T"."Y"6 AND
"T"."Y"7 AND "T"."Y"8) OR "T"."Y" IS NULL) AND ((TBL$OR$IDX$PA
RT$NUM(,0,0,65535,ROWID)>=2 AND
TBL$OR$IDX$PART$NUM(,0,0,65535,ROWID)<=2) OR (TBL$OR$IDX$PART$N
UM(,0,0,65535,ROWID)>=9 AND
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
TBL$OR$IDX$PART$NUM(,0,0,65535,ROWID)<=9))))
Блог Дмитрия Никифорова / Dmitry Nikiforov's blog 
Very interesting, Dmitry
but ORA-01795 is result of TABLE EXPANSION unresolved problem for the table with more than 1000 partitions, and not related to 12c partial indexes feature, imho
Test on 11.2 with one unusable index partition:
11.2.0.3.ORCL112@SCOTT SQL> create table t (x int, y int) storage(initial 10k next 10k) 2 partition by list (y) ( 3 partition p_1 values(1) 4 ) 5 / Table created. SQL> begin 2 for i in 2..1002 loop 3 execute immediate 4 'alter table t add partition p_'||i||' values('||i||')'; 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. SQL> create index t_i on t(x) local storage(initial 10k next 10k) 2 / Index created. SQL> alter index t_i MODIFY partition p_1 unusable 2 / Index altered. SQL> insert into t select level, mod(level,1002) + 1 2 from dual connect by level <= 100000 3 / 100000 rows created. SQL> exec dbms_stats.gather_table_stats('','T') BEGIN dbms_stats.gather_table_stats('','T'); END; * ERROR at line 1: ORA-20011: Approximate NDV failed: ORA-01795: maximum number of expressions in a list is 1000 -- the same error for dbms_stats ORA-06512: at "SYS.DBMS_STATS", line 23829 ORA-06512: at "SYS.DBMS_STATS", line 23880 ORA-06512: at line 1 SQL> select * from t where x = 10 2 / select * from t where x = 10 * ERROR at line 1: ORA-01795: maximum number of expressions in a list is 1000 -- the same error when CBO tries Table Expansion transformation SQL> select/*+ NO_EXPAND_TABLE(t)*/ * from t where x = 10 2 / X Y ---------- ---------- 10 11 -- no errors when TE is offMany thanks Igor, extremely useful point.
Yes, the table expansion is the root cause in both cases.
I just wanted to show that partial indexing is useless under certain conditions.
Really
solves problem and allows us to get result and in the same time it expectedly prevents partial indexing usage.
SQL> select * from table(dbms_xplan.display_cursor('47jntzmb5xsqc')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 47jntzmb5xsqc, child number 0 ------------------------------------- select /*+ NO_EXPAND_TABLE(t)*/ * from t where x = 10 Plan hash value: 3845649146 -------------------------------------------------------------------------------- ----------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Psta rt| Pstop | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------- | 0 | SELECT STATEMENT | | | | 12494 (100)| | | | | 1 | PARTITION LIST ALL| | 1440 | 37440 | 12494 (1)| 00:00:01 | 1 | 1002 | |* 2 | TABLE ACCESS FULL| T | 1440 | 37440 | 12494 (1)| 00:00:01 |Again thanks much for the nice point.
Pingback: Особенности преобразования Table Expansion | Oracle mechanics