Application case of constant composite index in Oracle

The problem extended from a customer's real optimization case.

A customer's database needs to be optimized, but because the program developer is not involved, this optimization cannot be modified for SQL.

Database-level adjustments generally have little effect, but there is a performance problem with individual SQL in the customer database, and this performance problem has affected the entire database. If you can optimize this SQL, you can solve the current database performance problems. Fortunately, this problem can be optimized by adding an index.

The simulation problem SQL is as follows:

Sql> select * from V$version;

BANNER

----------------------------------------------------------------

Oracle9i Enterprise Edition Release 9.2.0.4.0-production

Pl/sql Release 9.2.0.4.0-production

CORE 9.2.0.3.0 Production

TNS for Linux:version 9.2.0.4.0-production

Nlsrtl Version 9.2.0.4.0–production

Sql> CREATE TABLE T (ID number isn't null, created date, other char (200));

Table created.

sql> INSERT INTO T-select rownum, created, ' a ' from all_objects;

31126 rows created.

Sql> commit;

Commit complete.

sql> exec dbms_stats.gather_table_stats (user, ' T ')

Pl/sql procedure successfully completed.

sql> var v_id number

sql> var v_date varchar2 (14)

Sql> explain plan for

2 Select COUNT (*)

3 from T

4 where NVL (created, sysdate) > to_date (: v_date, ' Yyyymmddhh24miss ')-3

5 and id =: v_id;

explained.

Sql> select * FROM table (dbms_xplan.display);

Plan_table_output

------------------------------------------------------------------------------------------

--------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost |

--------------------------------------------------------------------

| 0 |            SELECT STATEMENT |    |   1 |   13 | 92 |

| 1 |            SORT AGGREGATE |    |   1 |      13 | |

|* 2 | TABLE ACCESS Full |    T |   1 |   13 | 92 |

--------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

2-filter (NVL ("T".) CREATED ", sysdate@!) >to_date (: Z, ' Yyyymmddhh24miss ')-3 and "T". " ID "=to_number" (: Z))

Note:cpu costing is off

Rows selected.

For this SQL, it is simple to optimize by indexing, and you can avoid full table scans by simply creating a composite index on the ID and created:

Sql> CREATE index ind_t_id_created on t (ID, created);

Index created.

Sql> explain plan for

2 Select COUNT (*)

3 from T

4 where NVL (created, sysdate) > to_date (: v_date, ' Yyyymmddhh24miss ')-3

5 and id =: v_id;

explained.

Sql> select * FROM table (dbms_xplan.display);

Plan_table_output

-------------------------------------------------------------------------------

--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost |

--------------------------------------------------------------------------

| 0 |                  SELECT STATEMENT |    |   1 |    13 | 2 |

| 1 |                  SORT AGGREGATE |    |   1 |      13 | |

|* 2 | INDEX RANGE SCAN |    ind_t_id_created |   1 |    13 | 2 |

--------------------------------------------------------------------------

Back to the column page: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

predicate information (identified by Operation ID):

---------------------------------------------------

2-access ("T".) ID "=to_number" (: Z))

Filter (NVL ("T".) CREATED ", sysdate@!) >to_date (: Z, ' Yyyymmddhh24miss ')-3)

Note:cpu costing is off

Rows selected.

Oracle has the option of selecting an index scan because the created column in the composite index is also saved as a blank record. Records with empty created can also be found in the index because the ID column is not empty and the index does not save all columns empty.

In fact, even if the ID is not empty, because another query condition specifies ID =: v_id, this makes the accessed records do not include records with an ID blank, which allows the composite index to still include all the data that the SQL needs to access.

The new question, however, is that if the SQL of the query does not contain a restriction on the ID column, the current index is not available:

Sql> ALTER TABLE t modify ID null;

Table altered.

Sql> explain plan for

2 Select COUNT (*)

3 from T

4 where NVL (created, sysdate) > to_date (: v_date, ' Yyyymmddhh24miss ')-3;

explained.

Sql> select * FROM table (dbms_xplan.display);

Plan_table_output

------------------------------------------------------------------------------------------

--------------------------------------------------------------------