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 |
----------------------------------------------------------
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.
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
[…] 15-How does zero size unusable indexes affect execution plans ? Christian Antognini-Zero-Size Unusable Indexes and the Query Optimizer […]
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.
[…] I’m guessing has been created to address this issue. Christian Antognini introduces it in this posting – which is actually starts by talking about zero-sized segments and unusable indexes. […]
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?
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
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)| ----------------------------------------------------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
[…] googling a bit and browsing the Oracle docs, it turned out that a blog post explaining how to implement Ghassan’s idea already existed. It’s from Christian […]
[…] resulting plan shows “Table Expansion” – a feature that became available in 11.2 (to be checked). We have effectively created an […]
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
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
If you hint an index with unusable partitions and the SQL accesses a partition where the index is unusable, it will error.
Not nice!
https://orastory.wordpress.com/2014/03/13/an-example-of-where-the-optimizer-should-ignore-a-hint/