12c partial indexes and list partitioning

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:

http://richardfoote.wordpress.com/2013/07/08/12c-partial-indexes-for-partitioned-tables-part-i-ignoreland/#comment-104322

http://richardfoote.wordpress.com/2013/07/12/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))))                   
About these ads

About dnikiforov

Oracle programmer/support engineer
This entry was posted in Uncategorized. Bookmark the permalink.

3 Responses to 12c partial indexes and list partitioning

  1. Игорь Усольцев says:

    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 off
    • dnikiforov says:

      Many 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

      select/*+ NO_EXPAND_TABLE(t)*/ * from t where x = 10

      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.

  2. Pingback: Особенности преобразования Table Expansion | Oracle mechanics

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s