Striving for Optimal Performance

In some specific situations the query optimizer is able to completely avoid executing a join even if a SQL statement explicitly calls for it. Two are the cases currently covered by this optimization technique, which is called join elimination. The first one was introduced in Oracle Database 10g Release 2, the second one in Oracle Database 11g Release 2. Let’s take a look at two cases to illustrate how join elimination works.

Case #1

Up to Oracle Database 11g Release 1 join elimination is especially useful when views containing joins are used. Note, however, that join elimination does not work only with views. It can be applied to SQL statements without views as well. The following SQL statements define two tables and one view. Notice that between table T1 and table T2, there is a master-child relationship. In fact, table T2, with its column T1_ID, references the primary key of table T1.

SQL> CREATE TABLE t1 (
  2    id NUMBER NOT NULL,
  3    n NUMBER,
  4    pad VARCHAR2(4000),
  5    CONSTRAINT t1_pk PRIMARY KEY(id)
  6  );

SQL> CREATE TABLE t2 (
  2    id NUMBER NOT NULL,
  3    t1_id NUMBER NOT NULL,
  4    n NUMBER,
  5    pad VARCHAR2(4000),
  6    CONSTRAINT t2_pk PRIMARY KEY(id),
  7    CONSTRAINT t2_t1_fk FOREIGN KEY (t1_id) REFERENCES t1
  8  );

SQL> CREATE VIEW v AS
  2  SELECT t1.id AS t1_id, t1.n AS t1_n, t2.id AS t2_id, t2.n AS t2_n
  3  FROM t1, t2
  4  WHERE t1.id = t2.t1_id;

When all the columns are referenced, as shown in the following example, the join is regularly executed. No surprise here.

SQL> EXPLAIN PLAN FOR SELECT * FROM v;

SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'basic'));

PLAN_TABLE_OUTPUT
----------------------------------------------
Plan hash value: 3114288414

----------------------------------------------
| Id  | Operation                    | Name  |
----------------------------------------------
|   0 | SELECT STATEMENT             |       |
|   1 |  NESTED LOOPS                |       |
|   2 |   NESTED LOOPS               |       |
|   3 |    TABLE ACCESS FULL         | T2    |
|   4 |    INDEX UNIQUE SCAN         | T1_PK |
|   5 |   TABLE ACCESS BY INDEX ROWID| T1    |
----------------------------------------------

However, as illustrated in the next example, when only columns defined in the child table are referenced, the query optimizer is able to eliminate the join. It can do so because there is a validated foreign key constraint that guarantees that all rows in table T2 reference one row in table T1.

SQL> EXPLAIN PLAN FOR SELECT t2_id, t2_n FROM v;

PLAN_TABLE_OUTPUT
----------------------------------
Plan hash value: 1513984157

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| T2   |
----------------------------------

The relevant part of the output of event 10053 is the following (notice that two queries are shown; the one before the transformation and the one after the transformation):

JE:   Considering Join Elimination on query block SEL$2 (#0)
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT "T2"."ID" "T2_ID","T2"."N" "T2_N" FROM CHA."T1" "T1",CHA."T2" "T2" WHERE "T1"."ID"="T2"."T1_ID"
JE:   cfro: T2 objn:86871 col#:2 dfro:T1 dcol#:2
JE:   cfro: T2 objn:86871 col#:2 dfro:T1 dcol#:2
Query block (0x2b732c78) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT "T2"."ID" "T2_ID","T2"."N" "T2_N" FROM CHA."T1" "T1",CHA."T2" "T2" WHERE "T2"."T1_ID"="T1"."ID"
JE:   eliminate table: T1 (T1)
Registered qb: SEL$FFBD8603 0x2b732c78 (JOIN REMOVED FROM QUERY BLOCK SEL$2; SEL$2; "T1"@"SEL$2")
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$FFBD8603 nbfros=1 flg=0
    fro(0): flg=0 objn=86873 hint_alias="T2"@"SEL$2"

SQL:******* UNPARSED QUERY IS *******
SELECT "T2"."ID" "T2_ID","T2"."N" "T2_N" FROM CHA."T2" "T2"
Query block SEL$FFBD8603 (#0) simplified

Case #2

As of Oracle Database 11g Release 2 join elimination covers another case. Its aim is to avoid the execution of “unnecessary” self-joins. The following SQL statements show an example. Notice that since the join is performed on the primary key (column ID) there is no need to access the table twice. In fact, it is possible to replace the references to the eliminated table (T2 in this example) in the SELECT clause with columns of the table that is not eliminated (T1).

SQL> EXPLAIN PLAN FOR SELECT t11.*, t12.* FROM t1 t11, t1 t12 WHERE t11.id = t12.id;

SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'basic'));

PLAN_TABLE_OUTPUT
----------------------------------
Plan hash value: 3617692013

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| T1   |
----------------------------------

The relevant part of the output of event 10053 is the following (also in this case notice that there are two queries):

JE:   Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT "T11"."ID" "ID","T11"."N" "N","T11"."PAD" "PAD","T12"."ID" "ID","T12"."N" "N","T12"."PAD" "PAD" FROM "CHA"."T1" "T11","CHA"."T1" "T12" WHERE "T11"."ID"="T12"."ID"
JE:   cfro: T1 objn:86871 col#:1 dfro:T1 dcol#:1
JE:   cfro: T1 objn:86871 col#:1 dfro:T1 dcol#:1
JE:   cfro: T1 objn:86871 col#:1 dfro:T1 dcol#:1
JE:   cfro: T1 objn:86871 col#:1 dfro:T1 dcol#:1
Query block (0x2c14f098) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT "T11"."ID" "ID","T11"."N" "N","T11"."PAD" "PAD","T12"."ID" "ID","T12"."N" "N","T12"."PAD" "PAD" FROM "CHA"."T1" "T11","CHA"."T1" "T12" WHERE "T11"."ID"="T12"."ID"
JE:   eliminate table: T1 (T12)
JE:   Replaced column: T12.PAD with column: T11.PAD
JE:   Replaced column: T12.N with column: T11.N
JE:   Replaced column: T12.ID with column: T11.ID
Registered qb: SEL$DF69B110 0x2c14f098 (JOIN REMOVED FROM QUERY BLOCK SEL$1; SEL$1; "T12"@"SEL$1")
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$DF69B110 nbfros=1 flg=0
    fro(0): flg=0 objn=86871 hint_alias="T11"@"SEL$1"

SQL:******* UNPARSED QUERY IS *******
SELECT "T11"."ID" "ID","T11"."N" "N","T11"."PAD" "PAD","T11"."ID" "ID","T11"."N" "N","T11"."PAD" "PAD" FROM "CHA"."T1" "T11"
Query block SEL$DF69B110 (#0) simplified

Note that running the previous example in Oracle Database 11g Release 1 or earlier leads, as expected, to a join like the following one.

SQL> EXPLAIN PLAN FOR SELECT t11.*, t12.* FROM t1 t11, t1 t12 WHERE t11.id = t12.id;

SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'basic'));

PLAN_TABLE_OUTPUT
----------------------------------------------
Plan hash value: 774821007

----------------------------------------------
| Id  | Operation                    | Name  |
----------------------------------------------
|   0 | SELECT STATEMENT             |       |
|   1 |  NESTED LOOPS                |       |
|   2 |   NESTED LOOPS               |       |
|   3 |    TABLE ACCESS FULL         | T1    |
|   4 |    INDEX UNIQUE SCAN         | T1_PK |
|   5 |   TABLE ACCESS BY INDEX ROWID| T1    |
----------------------------------------------

Few weeks ago the Chinese Edition of my book, Troubleshooting Oracle Performance, was published by China-Pub! Honestly, this is something I did not expect when publishing the book.

Even though I still have to touch one of them myself, let me spend few words about it…

I still remember when I saw for the first time part of my book translated in Chinese. I was looking at the news provided to me by Google Alerts when, among the other links, there was a link related to “Troubleshooting Oracle Performance” (this is one of the terms I monitor through Google Alerts) pointing to a Chinese page. I opened the referenced page and, it goes without saying, I was not able to read it. I just recognized the cover of my book and its name. So, I put the first part of the text in yet another Google application to let translate it to English. I immediately understood what it was all about. It was part of chapter 1 of my book. Amazing!

Since the book is now available, it means that the translators managed to finish their work. I know for sure that it was a huge undertaking and, therefore, I would like to thank David Feng, Jame Tong, Yiwen Hu and Yi Zhu for doing it. I know that they spent a considerable amount of their valuable time to translate the text. Thanks to them, more people will be able to learn something about Oracle Database, how to use it efficiently and how to approach performance problems. And that is something that makes me proud.

In case you are interested in it, click here.

You can add the COMPUTE STATISTICS clause to the CREATE INDEX statement. It instructs the SQL statement to gather and store index statistics in the data dictionary, while creating the index. This is useful because the overhead associated with the gathering of statistics while executing this SQL statement is negligible. In Oracle9i, the gathering of statistics is performed only when this clause is specified. As of Oracle Database 10g, whenever statistics are not locked, their gathering is done by default, which means the COMPUTE STATISTICS clause is deprecated and available for backward compatibility only.

Unfortunately, CREATE INDEX does not gather global statistics. As a result, whenever you are creating partitioned indexes, the global statistics might be inaccurate. Let me show you an example:

• Create partitioned table, insert data (notice that the number of distinct values is equal to the number of rows) and create a local index

SQL> CREATE TABLE t (n1 number, n2 number)
  2  PARTITION BY RANGE (n1) (
  3    PARTITION p1 VALUES LESS THAN (11),
  4    PARTITION p2 VALUES LESS THAN (21)
  5  );

Table created.

SQL> INSERT INTO t
  2  SELECT rownum, rownum
  3  FROM dual
  4  CONNECT BY level <= 20;

20 rows created.

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

Index created.

• The CREATE INDEX statement gathered the statistics for the index; let’s check them…

SQL> SELECT partition_name, global_stats, distinct_keys
  2  FROM user_ind_statistics
  3  WHERE index_name = 'I';

PARTITION_NAME GLOBAL_STATS DISTINCT_KEYS
-------------- ------------ -------------
               NO                      10
P1             NO                      10
P2             NO                      10

As you can see 1) the number of distinct keys at the global level is wrong; it should be 20! 2) the GLOBAL_STATS column at the index level is set to NO. As a result, when you create a partitioned index, you should manually gather the global index statistics straight after. In other words, you should do the following:

• Manually gather global level index statistics

SQL> execute dbms_stats.gather_index_stats(ownname=>user, indname=>'i', granularity=>'global')

PL/SQL procedure successfully completed.

• Check whether the index statistics are accurate

SQL> SELECT partition_name, global_stats, distinct_keys
  2  FROM user_ind_statistics
  3  WHERE index_name = 'I';

PARTITION_NAME GLOBAL_STATS DISTINCT_KEYS
-------------- ------------ -------------
               YES                     20
P1             NO                      10
P2             NO                      10

There are situations, however, where it is not necessary to manually gather the global index statistics. For example, when the index is prefixed. But, as a general rule, I would not rely on the automatically gathered statistics for partitioned indexes.

Instance caging is another small but useful feature of Oracle Database 11g Release 2. Thanks to it the database resource manager is able, for the first time, to limit the number of CPUs that can be used by a given instance. (By the way, note that this limit has no “impact” on the number of CPUs that have to be licensed.)

Using instance caging requires only two simple configurations from our part:

• Enable the resource manager by assigning a resource plan (e.g. DEFAULT_PLAN)
• Set the number of CPUs through the CPU_COUNT initialization parameter

To illustrate how it works, let me show you what I observed on my small test server with 4 cores.

To burn all the available CPU resources, I started four sessions executing the following PL/SQL block:

DECLARE
  n NUMBER;
BEGIN
  WHILE (TRUE)
  LOOP
    n:= dbms_random.random();
  END LOOP;
END;

With the four sessions up and running, the output of vmstat(8) looks like the following:

procs ------------memory------------ --swap-- ----io---- --system-- -----cpu-----
 r  b  swpd     free   buff    cache  si   so   bi    bo    in   cs   us sy id wa
 5  0     0  6037164  71568  5476620   0    0   66   198  1033  823  100  0  0  0
 5  0     0  6037164  71572  5476616   0    0    0   158  1021  801  100  0  0  0
 5  0     0  6037164  71572  5476616   0    0    0    48  1016  750  100  0  0  0

Notice that there is no idle time and that the number of processes waiting for run time is 5.
Now, to show what instance caging can do to limit the CPU utilization, I started the following PL/SQL block:

DECLARE
  l_sql VARCHAR2(100) := 'ALTER SYSTEM SET cpu_count = ';
BEGIN
  EXECUTE IMMEDIATE l_sql || '4';
  dbms_lock.sleep(10);
  EXECUTE IMMEDIATE l_sql || '3';
  dbms_lock.sleep(10);
  EXECUTE IMMEDIATE l_sql || '2';
  dbms_lock.sleep(10);
  EXECUTE IMMEDIATE l_sql || '1';
  dbms_lock.sleep(10);
  EXECUTE IMMEDIATE l_sql || '0';
END;

This time the output of vmstat(8) looks like the following:

procs ------------memory------------ --swap-- ----io---- --system-- -----cpu-----
 r  b  swpd     free   buff    cache  si   so   bi    bo    in   cs   us sy id wa
 5  0      0 3695176  80856  7780352   0    0    0    96  1019  783  100  0  0  0
 5  0      0 3695176  80856  7780352   0    0    0   170  1023  795  100  0  0  0
 5  0      0 3695184  80856  7780352   0    0    0   156  1028  795  100  0  0  0
 5  0      0 3695184  80860  7780348   0    0    0   120  1021  795  100  0  0  0
 5  0      0 3694928  80860  7780348   0    0    2   168  1029  810  100  0  0  0
 3  0      0 3694928  80860  7780348   0    0    0   214  1029  939   77  0 23  0
 3  0      0 3694928  80864  7780344   0    0    0   118  1020  961   75  0 25  0
 3  0      0 3694928  80864  7780344   0    0    2   152  1026  961   75  0 25  0
 3  0      0 3694596  80868  7780340   0    0    2   158  1027  975   75  0 25  0
 3  0      0 3694612  80868  7780340   0    0    0   142  1031  979   75  0 25  0
 3  0      0 3694612  80868  7780340   0    0    2   164  1024  963   75  0 25  0
 3  0      0 3694616  80872  7780336   0    0    0   358  1079  961   52  0 49  0
 2  0      0 3694616  80872  7780336   0    0    0   120  1021  909   50  0 50  0
 2  0      0 3697312  80872  7780336   0    0    0   162  1025  952   50  0 50  0
 2  0      0 3694744  80876  7780332   0    0    0   142  1027  948   50  0 50  0
 1  0      0 3694744  80876  7780332   0    0    0   120  1021  954   40  0 60  0
 1  0      0 3694748  80876  7780332   0    0    0   234  1034  953   26  0 74  0
 1  0      0 3694748  80876  7780332   0    0    0   134  1021  921   26  0 74  0
 1  0      0 3696484  80876  7780332   0    0    0   120  1020  954   26  0 74  0
 1  0      0 3696476  80880  7780328   0    0    2   196  1035  996   26  0 74  0
 5  0      0 3696476  80880  7780328   0    0    0   112  1020  643   96  0  4  0
 6  0      0 3696484  80880  7780328   0    0    0   216  1040  778  100  0  0  0
 5  0      0 3696484  80884  7780324   0    0    0   160  1021  763  100  0  0  0
 5  0      0 3696484  80884  7780324   0    0    0   112  1020  775  100  0  0  0
 5  0      0 3696468  80888  7780320   0    0    0   156  1026  785  100  0  0  0

Notice that not only the idle time but also the number of processes waiting for run time dynamically changes according to the value specified for the CPU_COUNT initialization parameter. Great!

The only problem I faced during my tests is that the management does not work as expected when lot of time is spent running kernel code. On my server I am able to reproduce such a problem by running the following PL/SQL block:

BEGIN
  WHILE (TRUE)
  LOOP
    FOR i IN (SELECT * FROM t)
    LOOP
      NULL;
    END LOOP;
  END LOOP;
END;

When four sessions run that code, the output of vmstat(8) looks like the following:

procs ------------memory------------ --swap-- ----io---- --system-- -----cpu-----
 r  b  swpd     free   buff    cache  si   so   bi    bo    in   cs   us sy id wa
 4  0     0  3690112  81032  7780652   0    0    0   138  1025  786   65 35  0  0
 4  0     0  3690112  81036  7780648   0    0    0   182  1024  775   66 34  0  0
 4  0     0  3690112  81036  7780648   0    0    0    84  1018  779   66 34  0  0
 4  0     0  3690112  81036  7780648   0    0    0   138  1024  822   66 34  0  0
 4  0     0  3690112  81040  7780644   0    0    0   204  1032  809   66 34  0  0
 4  0     0  3690112  81044  7780640   0    0    0   120  1027  798   63 36  1  0
 4  0     0  3690112  81044  7780640   0    0    0   146  1021  833   61 37  2  0
 4  0     0  3690112  81044  7780640   0    0    0   160  1023  785   63 35  1  0
 5  0     0  3690112  81044  7780640   0    0    0    98  1021  782   62 36  1  0
 4  0     0  3690112  81044  7780640   0    0    0   146  1021  818   64 36  1  0
 4  0     0  3690048  81044  7780640   0    0    2   180  1031  803   63 36  1  0
 3  0     0  3690048  81044  7780640   0    0    0   126  1027  921   53 29 18  0
 3  0     0  3690048  81044  7780640   0    0    0   146  1022  988   46 25 29  0
 3  0     0  3690044  81048  7780636   0    0    0   180  1032  976   48 25 28  0
 3  0     0  3690044  81048  7780636   0    0    0   212  1058  974   48 25 27  0
 2  0     0  3690052  81048  7780636   0    0    0   144  1028  950   47 23 30  0
 2  0     0  3690052  81048  7780636   0    0    0   204  1035  971   34 14 51  0
 2  0     0  3691404  81048  7780636   0    0    0   106  1020  939   32 13 55  0
 2  0     0  3691404  81048  7780636   0    0    0   124  1021  972   34 14 53  0
 2  0     0  3691404  81048  7780636   0    0    0   202  1032  986   33 14 53  0
 4  0     0  3691404  81048  7780636   0    0    0    80  1024  850   51 29 21  0
 4  0     0  3691404  81048  7780636   0    0    0   172  1028  790   65 35  0  0
 4  0     0  3691404  81048  7780636   0    0    0   220  1033  785   63 37  0  0
 5  0     0  3691404  81048  7780636   0    0    0    62  1016  771   64 36  0  0
 5  0     0  3691404  81048  7780636   0    0    0   170  1022  785   62 38  0  0
 5  0     0  3691404  81048  7780636   0    0    0   18   1026  772   64 36  0  0

Notice that in this case about one third of the time is spent running kernel code. As a result, the CPU utilization (for the values of 3, 2 and 1) is always higher than expected.

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 |
----------------------------------------------------------