Analysis of creating a composite index in oracle-create a composite index containing three SQL statements in

The previous article mentioned the idea of creating a composite index:
1 leading columns as much as possible to enable more core business SQL to be used
2 Leading columns of a single SQL statement index select the equivalent condition as the leading column of the index

Here we do some more in-depth analysis of the creation of compound indexes on predicates in, three of conditional SQL statements, and the detailed examples are as follows:

Sql> select * from V$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition release 11.2.0.4.0-64bit Production
Pl/sql Release 11.2.0.4.0-production
CORE 11.2.0.4.0 Production
TNS for Linux:version 11.2.0.4.0-production
Nlsrtl Version 11.2.0.4.0-production

Sql> CREATE TABLE t09 as SELECT * from Dba_objects;

Table created.

Sql> CREATE index Ind_owner_type_objid on t09 (owner,object_type,object_id);

Index created.

Sql> CREATE index Ind_type_owner_objid on t09 (object_type,owner,object_id);

Index created.

Sql> set Autotrace traceonly;
sql> Analyze table t09 Compute statistics for all indexes;

Table analyzed.
Select/*+index (t09 ind_owner_type_objid) */* from t09 where owner= ' SYS ' and object_type in (' TABLE ', ' Index ') and object_id >30000 and object_id<310000; The SQL statement requires the creation of a composite index on owner, object_type, object_id, and the order in which the composite index is created, where we consider only the best execution plan for that SQL. Regardless of the other SQL's ability to share the index, let's look at the performance and execution plans of the two composite indexes.

Index (OWNER+OBJECT_TYPE+OBJECT_ID):
Sql> Select/*+index (t09 ind_owner_type_objid) */* from t09 where owner= ' SYS ' and object_type in (' TABLE ', ' Index ') and O bject_id>30000 and object_id<310000;

Rows selected.

Execution Plan
----------------------------------------------------------
Plan Hash value:1730993038

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------------------------
| 0 |                      SELECT STATEMENT |     |   1 |     207 | 5 (0) | 00:00:01 |
|  1 |                      INLIST Iterator |       |       |            |          | |
|   2 | TABLE ACCESS by INDEX rowid|     T09 |   1 |     207 | 5 (0) | 00:00:01 |
|* 3 | INDEX RANGE SCAN |     Ind_owner_type_objid |       1 |     | 4 (0) | 00:00:01 |
-----------------------------------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

3-access ("OWNER" = ' SYS ' and ("object_type" = ' INDEX ' OR ' object_type ' = ' TABLE ') and
"OBJECT_ID" >30000 and "object_id" <310000)

Note
-----
-Dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
Consistent gets
0 physical Reads
0 Redo Size
7609 Bytes sent via sql*net to client
556 Bytes received via sql*net from client
5 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
Rows processed
Index (OBJECT_TYPE+OWNER+OBJECT_ID):
Sql> Select/*+index (t09 ind_type_owner_objid) */* from t09 where owner= ' SYS ' and object_type in (' TABLE ', ' Index ') and O bject_id>30000 and object_id<310000;

Rows selected.

Execution Plan
----------------------------------------------------------
Plan Hash value:1925664837

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------------------------
| 0 |                      SELECT STATEMENT |     |   1 |     207 | 5 (0) | 00:00:01 |
|  1 |                      INLIST Iterator |       |       |            |          | |
|   2 | TABLE ACCESS by INDEX rowid|     T09 |   1 |     207 | 5 (0) | 00:00:01 |
|* 3 | INDEX RANGE SCAN |     Ind_type_owner_objid |       1 |     | 4 (0) | 00:00:01 |
-----------------------------------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

3-access ("object_type" = ' INDEX ' OR "object_type" = ' TABLE ') and ' OWNER ' = ' SYS ' and
"OBJECT_ID" >30000 and "object_id" <310000)

Note
-----
-Dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
Consistent gets
0 physical Reads
0 Redo Size
7609 Bytes sent via sql*net to client
556 Bytes received via sql*net from client
5 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
Rows processed
Look out for the predicate conditions owner= ' SYS ' and object_type in (' TABLE ', ' INDEX ') and object_id>30000 and object_id<310000, either by selecting (owner+object_type+object_id) or a composite index (OBJECT_TYPE+OWNER+OBJECT_ID), the optimizer can take access directly to predicate conditions during the index range scan without having to go to filter Here the optimizer does the inlist iterator execution plan, which is similar to an Oracle Index skip range Execution plan, which, when the first group in the in condition is done with the index range scan, jumps back to the branch block to do the index range Scan, this is more efficient than Oracle's other execution plan concatenation, since it is no longer necessary to move the branch block back from the root node to the leaf block.

If our optimizer returns to 8I, these two SQL execution plans remain the same
Sql> Select/*+optimizer_features_enable (' 8.1.7 ') index (t09 Ind_owner_type_objid) */* from t09 where owner= ' SYS ' and Object_type in (' TABLE ', ' INDEX ') and object_id>30000 and object_id<310000;

Rows selected.

Execution Plan
----------------------------------------------------------
Plan Hash value:1730993038

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------------
| 0 |                      SELECT STATEMENT |     |   1 |     207 | 4 |
|  1 |                      INLIST Iterator |       |       |       | |
|   2 | TABLE ACCESS by INDEX rowid|     T09 |   1 |     207 | 4 |
|* 3 | INDEX RANGE SCAN |     Ind_owner_type_objid |       1 |     | 3 |
-------------------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

3-access ("OWNER" = ' SYS ' and (' object_type ' = ' INDEX ' OR
"Object_type" = ' TABLE ') and "object_id" >30000 and "object_id" <310000)

Note
-----
-CPU costing is off (consider enabling it)

Statistics
----------------------------------------------------------
1 Recursive calls
0 db Block gets
Consistent gets
0 physical Reads
0 Redo Size
7609 Bytes sent via sql*net to client
556 Bytes received via sql*net from client
5 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
Rows processed

Sql> Select/*+optimizer_features_enable (' 8.1.7 ') index (t09 Ind_type_owner_objid) */* from t09 where owner= ' SYS ' and Object_type in (' TABLE ', ' INDEX ') and object_id>30000 and object_id<310000;

Rows selected.

Execution Plan
----------------------------------------------------------
Plan Hash value:1925664837

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------------
| 0 |                      SELECT STATEMENT |     |   1 |     207 | 4 |
|  1 |                      INLIST Iterator |       |       |       | |
|   2 | TABLE ACCESS by INDEX rowid|     T09 |   1 |     207 | 4 |
|* 3 | INDEX RANGE SCAN |     Ind_type_owner_objid |       1 |     | 3 |
-------------------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

3-access ("object_type" = ' INDEX ' OR "object_type" = ' TABLE ') and
"OWNER" = ' SYS ' and "object_id" >30000 and "object_id" <310000)

Note
-----
-CPU costing is off (consider enabling it)

Statistics
----------------------------------------------------------
1 Recursive calls
0 db Block gets
Consistent gets
0 physical Reads
0 Redo Size
7609 Bytes sent via sql*net to client
556 Bytes received via sql*net from client
5 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
Rows processed