Zero-Size Unusable Indexes and the Query Optimizer

Zero-size unusable indexes and index partions is a small but useful feature of Oracle Database 11g Release 2. Simply put, its aim is to save space in the database by immediately releasing the segment associated to unusable indexes or index partitions. To illustrate this, let’s have a look to an example…

  • Create a partitioned table, insert data, create a local index and gather object statistics:
SQL> CREATE TABLE t (
  2    id NUMBER NOT NULL,
  3    d DATE NOT NULL,
  4    n NUMBER NOT NULL,
  5    pad VARCHAR2(4000) NOT NULL
  6  )
  7  PARTITION BY RANGE (d) (
  8    PARTITION t_jan_2009 VALUES LESS THAN (to_date('2009-02-01','yyyy-mm-dd')),
  9    PARTITION t_feb_2009 VALUES LESS THAN (to_date('2009-03-01','yyyy-mm-dd')),
 10    PARTITION t_mar_2009 VALUES LESS THAN (to_date('2009-04-01','yyyy-mm-dd')),
 11    PARTITION t_apr_2009 VALUES LESS THAN (to_date('2009-05-01','yyyy-mm-dd')),
 12    PARTITION t_may_2009 VALUES LESS THAN (to_date('2009-06-01','yyyy-mm-dd')),
 13    PARTITION t_jun_2009 VALUES LESS THAN (to_date('2009-07-01','yyyy-mm-dd')),
 14    PARTITION t_jul_2009 VALUES LESS THAN (to_date('2009-08-01','yyyy-mm-dd')),
 15    PARTITION t_aug_2009 VALUES LESS THAN (to_date('2009-09-01','yyyy-mm-dd')),
 16    PARTITION t_sep_2009 VALUES LESS THAN (to_date('2009-10-01','yyyy-mm-dd')),
 17    PARTITION t_oct_2009 VALUES LESS THAN (to_date('2009-11-01','yyyy-mm-dd')),
 18    PARTITION t_nov_2009 VALUES LESS THAN (to_date('2009-12-01','yyyy-mm-dd')),
 19    PARTITION t_dec_2009 VALUES LESS THAN (to_date('2010-01-01','yyyy-mm-dd'))
 20  );

SQL> INSERT INTO t
  2  SELECT rownum, to_date('2009-01-01','yyyy-mm-dd')+rownum/274, mod(rownum,11), rpad('*',100,'*')
  3  FROM dual
  4  CONNECT BY level <= 100000;

SQL> CREATE INDEX i ON t (d) LOCAL;

SQL> execute dbms_stats.gather_table_stats(user,'T')
  • Make all partitions but the last one unusable:
SQL> ALTER INDEX i MODIFY PARTITION t_jan_2009 UNUSABLE;

SQL> ALTER INDEX i MODIFY PARTITION t_feb_2009 UNUSABLE;

SQL> ALTER INDEX i MODIFY PARTITION t_mar_2009 UNUSABLE;

SQL> ALTER INDEX i MODIFY PARTITION t_apr_2009 UNUSABLE;

SQL> ALTER INDEX i MODIFY PARTITION t_may_2009 UNUSABLE;

SQL> ALTER INDEX i MODIFY PARTITION t_jun_2009 UNUSABLE;

SQL> ALTER INDEX i MODIFY PARTITION t_jul_2009 UNUSABLE;

SQL> ALTER INDEX i MODIFY PARTITION t_aug_2009 UNUSABLE;

SQL> ALTER INDEX i MODIFY PARTITION t_sep_2009 UNUSABLE;

SQL> ALTER INDEX i MODIFY PARTITION t_oct_2009 UNUSABLE;

SQL> ALTER INDEX i MODIFY PARTITION t_nov_2009 UNUSABLE;
  • Check whether the segments associated to the unusable partitions still exist:
SQL> SELECT partition_name, bytes
  2  FROM user_segments
  3  WHERE segment_name = 'I'
  4  AND segment_type = 'INDEX PARTITION'
  5  ORDER BY partition_name;

PARTITION_NAME       BYTES
--------------- ----------
T_DEC_2009          262144

As you can see from the output of the last query, only the segment associated to the partition T_DEC_2009 exists. All other segments have been freed. (Note that up to Oracle Database 11g Release 1 all segments would still exist.)

This is nice but, in my opinion, there is a more important thing to consider…
What does the query optimizer do when it has to generate the execution plan for a query that reads data stored into a table having unusable index partitions? For example, does the query optimizer take advantage of the usable partitions to apply a restriction? If yes, what happens when both partitions having usable and unusable index partitions have to be accessed? Let’s have a look to some examples…

  • First, let’s check whether the usable index partition can be used to apply a restriction:
SQL> SELECT count(d)
  2  FROM t
  3  WHERE d BETWEEN to_date('2009-12-01 23:00:00','yyyy-mm-dd hh24:mi:ss')
  4              AND to_date('2009-12-02 01:00:00','yyyy-mm-dd hh24:mi:ss');

SQL> SELECT * FROM table(dbms_xplan.display_cursor(format=>'basic +partition'));

--------------------------------------------------------
| Id  | Operation               | Name | Pstart| Pstop |
--------------------------------------------------------
|   0 | SELECT STATEMENT        |      |       |       |
|   1 |  SORT AGGREGATE         |      |       |       |
|   2 |   PARTITION RANGE SINGLE|      |    12 |    12 |
|   3 |    INDEX RANGE SCAN     | I    |    12 |    12 |
--------------------------------------------------------

Nice, an index range scan can be performed.


  • Second, let’s check what happen when an unusable index partition would be accessed:
SQL> SELECT count(d)
  2  FROM t
  3  WHERE d BETWEEN to_date('2009-11-01 23:00:00','yyyy-mm-dd hh24:mi:ss')
  4              AND to_date('2009-11-02 01:00:00','yyyy-mm-dd hh24:mi:ss');

SQL> SELECT * FROM table(dbms_xplan.display_cursor(format=>'basic +partition'));

--------------------------------------------------------
| Id  | Operation               | Name | Pstart| Pstop |
--------------------------------------------------------
|   0 | SELECT STATEMENT        |      |       |       |
|   1 |  SORT AGGREGATE         |      |       |       |
|   2 |   PARTITION RANGE SINGLE|      |    11 |    11 |
|   3 |    TABLE ACCESS FULL    | T    |    11 |    11 |
--------------------------------------------------------

Obviously, a partition scan is performed. Note that this is only true if the initialization parameter SKIP_UNUSABLE_INDEXES is set to TRUE (this is the default). Otherwise an ORA-01502 would be generated.


  • Third, let’s check what happens when both usable and unusable index partitions would be accessed:
SQL> SELECT count(d)
  2  FROM t
  3  WHERE d BETWEEN to_date('2009-11-30 23:00:00','yyyy-mm-dd hh24:mi:ss')
  4              AND to_date('2009-12-01 01:00:00','yyyy-mm-dd hh24:mi:ss');

SQL> SELECT * FROM table(dbms_xplan.display_cursor(format=>'basic +partition'));

-------------------------------------------------------------
| Id  | Operation                 | Name    | Pstart| Pstop |
-------------------------------------------------------------
|   0 | SELECT STATEMENT          |         |       |       |
|   1 |  SORT AGGREGATE           |         |       |       |
|   2 |   VIEW                    | VW_TE_2 |       |       |
|   3 |    UNION-ALL              |         |       |       |
|   4 |     PARTITION RANGE SINGLE|         |    12 |    12 |
|   5 |      INDEX RANGE SCAN     | I       |    12 |    12 |
|   6 |     PARTITION RANGE SINGLE|         |    11 |    11 |
|   7 |      TABLE ACCESS FULL    | T       |    11 |    11 |
-------------------------------------------------------------

Good stuff! The query optimizer generates an execution plan containing a UNION ALL to takes advantage of the usable index partition. This is really interesting because it allows us to selectively remove unnecessary index partitions. For example, in case some indexes are only used for the “current” partition(s), the index partitions of the older ones could be set unusable. As a result, lot of space might be released.

Be careful, however, that the flexibility of the query optimizer has (still?) some limits. For example, in the following query the unusable partition is the one in the “middle”. In such a case, I was not able to let the query optimizer apply the optimization described above.

SQL> ALTER INDEX i REBUILD PARTITION t_oct_2009;

SQL> SELECT count(d)
  2  FROM t
  3  WHERE d BETWEEN to_date('2009-10-30 23:00:00','yyyy-mm-dd hh24:mi:ss')
  4              AND to_date('2009-12-01 01:00:00','yyyy-mm-dd hh24:mi:ss');

SQL> SELECT * FROM table(dbms_xplan.display_cursor(format=>'basic +partition'));

----------------------------------------------------------
| Id  | Operation                 | Name | Pstart| Pstop |
----------------------------------------------------------
|   0 | SELECT STATEMENT          |      |       |       |
|   1 |  SORT AGGREGATE           |      |       |       |
|   2 |   PARTITION RANGE ITERATOR|      |    10 |    12 |
|   3 |    TABLE ACCESS FULL      | T    |    10 |    12 |
----------------------------------------------------------

9 Comments

  1. November 26, 2009    

    Chris. Thanks for sharing valueable test cases.

    This behavior seems to controlled by the new hidden parameter “_optimizer_table_expansion”. Table expansion makes it possible to expand the predicates spanning multiple partitions into the UNION ALL version of the query.

    And my simple test case showed that the table expansion works with no problem even with the middle local index partition disabled. Your case would be the result of cost based query transformation which would have determined that expanded query is more costly.

    Cheers.

  2. November 26, 2009    

    Hi Dion

    Yes, table expansion is related to it. For example, when tracing the query optimizer work with event 10053 the information related to this feature is reported in the “sections” of table expansion (search for “TE:”). Among others, the query after the transformation is given.

    About the limitation… As I wrote, in this specific case I was not able to make it generate a UNION ALL with three SELECTs. It might be that in other case it works… Honestly, I didn’t spent much time on it. But, again, in this case it doesn’t work and, IMO, there is no good reason for it. In fact, when I run the query with the following ranges it works as expected:
    - 2009-10-30 23:00:00 – 2009-11-30 23:59:59
    - 2009-11-01 00:00:00 – 2009-12-01 01:00:00
    Also from a cost point of view it makes no sense what the query optimizer do. In fact, in this case, the index range scan costs 2 and the full partition scan costs 68. When table expansion happens, the total cost is simply the sum of the two (70). However, when I run the query covering three partitions the resulting cost is 200! So, much higher than 2+68+2 ;-)

    Anyway, in practice I don’t think that this limitation will be relevant.

    Cheers,
    Chris

  3. December 17, 2009    

    This is a nice touch, of course, for all those system which use range partitioning to “age” their data and want to have one set of indexes for the recent data and another set for the old data. You just have to be careful to let people know where the boundaries are, and make sure that lots of unusable partitions don’t get rebuilt by accident.

  4. Todor Botev's Gravatar Todor Botev
    December 21, 2009    

    What if the index is there to support PK or UK:

    – could we still make slected index partitions unusable?

    – if yes – what about the inserts? Can we still insert into the table partitions with usable index?

  5. January 11, 2010    

    Hi Todor

    > could we still make slected index partitions unusable?

    Yes

    > if yes – what about the inserts? Can we still insert into the table partitions with usable index?

    Yes and no. In fact, it is only possible to insert data in partitions that are not in unusable state.

    Cheers,
    Chris

  6. Sachin Butala's Gravatar Sachin Butala
    February 18, 2010    

    i used “OR” instead or “BETWEEN” and got different results.
    i run this test on oracle11gR2 – Solaris 10

    SELECT count(d)
    FROM t
    WHERE
    ((
             d >= to_date('2009-12-01 01:00:00','yyyy-mm-dd hh24:mi:ss')
         and d = to_date('2009-12-01 05:00:00','yyyy-mm-dd hh24:mi:ss')
         and d < = to_date('2009-12-01 06:00:00','yyyy-mm-dd hh24:mi:ss')
    ));
    
    | Id  | Operation           | Name | Pstart| Pstop |
    ----------------------------------------------------
    |   0 | SELECT STATEMENT    |      |       |       |
    |   1 |  SORT AGGREGATE     |      |       |       |
    |   2 |   PARTITION RANGE OR|      |KEY(OR)|KEY(OR)|
    |   3 |    TABLE ACCESS FULL| T    |KEY(OR)|KEY(OR)|
    ----------------------------------------------------
  7. February 28, 2010    

    Hi Sachin

    Sorry, but:
    - I do not understand what the aim of your query is
    - The query does not containt an OR and, probably for that reason, to me it seems that such an execution plan cannot be generated based on the query you provided.

    BTW, the PARTITION RANGE OR is a new kind of partition pruning that is available as of 11g. It is used when, in the WHERE clause, there are several restrictions on the partition key that are connected by an OR. Up to 10g such a query would lead to a FTS.

    HTH
    Chris

    PS: I edited you comment to make it more readable

  8. Mayank's Gravatar Mayank
    April 18, 2014    

    Hi,
    Please let me know if there is way to make usable all local partition index USABLE in one go
    As i know i can make all Partition local index unusable by doing
    ALTER INDEX ACB UNUSABLE ;

    My goal is i have i have to load 1 M records in partition table and i dont know in which partition data will go and i want to follow below approach
    1) Make all indexes unusable in partitioned table ( Possible with one syntax cmd)
    2) Load data using Impdp in partitioned table
    3) Make Usable all partitioned indexes, but i am not able to find syntax to make all partitoned index Usable in one command , i have to it one by one for all partition

    I have around 80 Partitioned table and each have around 1000 partition

    Please suggest me

    Mayank

    • April 18, 2014    

      Hi Mayank

      AFAIK it’s not possible. You have to rebuild every (sub)partition separately. I would write a small piece of PL/SQL code to do it…

      Best,
      Chris

  1. Blogroll Report 20/11/2009-27/11/2009 « Coskan’s Approach to Oracle on December 12, 2009 at 20:05
  2. Partition Indexing « Oracle Scratchpad on December 17, 2009 at 22:03
  3. How to gain space on indexes of HCC partitioned tables | ocpdba oracle weblog on June 30, 2011 at 01:20
  4. Bitmap / Btree | Oracle Scratchpad on September 3, 2013 at 06:28

Leave a Reply

Your email address will not be published. Required fields are marked *