An explanation of the use of composite indexes in Oracle

In Oracle, you can create composite indexes, which are indexes that contain two or more columns at one time. In terms of the use of composite indexes, Oracle has the following features:

1. When a rule-based optimizer (RBO) is used, the index is used only if the preamble of the composite Index is listed in the WHERE clause of the SQL statement now;

2. When using the cost-based optimizer (CBO) prior to oracle9i, the index may be used only if the preamble of the composite Index is listed in the WHERE clause of the SQL statement, depending on the cost of using the index and the cost of using a full table scan, which is calculated by the optimizer. Oracle will automatically select a low-cost access path (see Test 1 and Test 2 below);

3. From Oracle9i, Oracle introduced a new index scanning Method-index skip scan, which can only be used by the cost-based optimizer (CBO). In this way, Oracle uses this method to scan the composite index when the WHERE clause of the SQL statement does not have a leading column for the combined index, and the cost of the index hop scan is lower than the cost of other scanning methods (see test 3 below);

4, the Oracle Optimizer sometimes makes the wrong choice, because it is again "smart", also than our SQL statement writer more clear data distribution in the table, in this case, by using hints (hint), we can help the Oracle optimizer to make a better choice (see Test 4 below).

For the above, we test the following separately:

We create the test table T, which is derived from the Data dictionary table all_objects of Oracle, and the table T is structured as follows:

Sql> desc T

is the name empty? Type

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

OWNER not NULL VARCHAR2 (30)

object_name not NULL VARCHAR2 (30)

Subobject_name VARCHAR2 (30)

OBJECT_ID not NULL number

DATA_OBJECT_ID number

Object_type VARCHAR2 (18)

CREATED not NULL DATE

Last_ddl_time not NULL DATE

TIMESTAMP VARCHAR2 (19)

STATUS VARCHAR2 (7)

Temporary VARCHAR2 (1)

GENERATED VARCHAR2 (1)

Secondary VARCHAR2 (1)

The data in the table is distributed as follows:

Sql> Select Object_type,count (*) from the T Group by Object_type;

Object_type COUNT (*)

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

CONSUMER GROUP 20

EVALUATION CONTEXT 10

FUNCTION 360

INDEX 69

LIBRARY 20

LOB 20

OPERATOR 20

Package 1210

PROCEDURE 130

Synonym 16100

TABLE 180

TYPE 2750

VIEW 8600

13 rows have been selected.

Sql> Select COUNT (*) from T;

COUNT (*)

----------

29489

We create the following index on the table T and parse it:

Sql> CREATE index indx_t on T (object_type,object_name);

The index has been created.

sql> ANALYZE TABLE T COMPUTE STATISTICS

2 for TABLE

3 for all INDEXES

4 for all INDEXED COLUMNS

5/

Table has been analyzed.

Now let's write a few SQL statements to test the Oracle Optimizer's choice of access paths:

Test 1)

Sql> Set Autotrace traceonly

Sql> SELECT * from T WHERE object_type= ' LOB ';

20 rows have been selected.

Execution Plan

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

0 SELECT STATEMENT optimizer=choose (cost=22 card=20 bytes=1740)

1 0 TABLE ACCESS (by INDEX ROWID) of ' T ' (cost=22 card=20 bytes=1740)

2 1 INDEX (RANGE SCAN) of ' indx_t ' (non-unique) (cost=2 card=20)

As we expected, Oracle chose the index scan wisely, because the leading column of the combined index was used and a small number of records in the table were accessed. So, what kind of Access path does Oracle Choose when we access a large amount of data in a table? Take a look at the following tests:

Test 2)

Sql> SELECT * from T WHERE object_type= ' synonym ';

16100 rows have been selected.

Execution Plan

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

0 SELECT STATEMENT optimizer=choose (cost=38 card=16100 bytes=1400700)

1 0 TABLE ACCESS (full) of ' T ' (cost=38 card=16100 bytes=1400700)

Statistics

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

0 Recursive calls

0 db Block gets

1438 consistent gets

Physical Reads

0 Redo Size

941307 Bytes sent via sql*net to client

12306 bytes received via sql*net from client

1075 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

16100 rows processed

Obviously, even though the leading columns of the combined index are used, Oracle has chosen to use full table scanning without indexes because of the large amount of data accessed in the table, because the optimizer considers the cost of the full table scan to be lower, but is the fact true? We force it to use the index by adding hints (hint) to see:

Sql> select/**//*+ INDEX (t indx_t) */* from T WHERE object_type= ' synonym ';

16100 rows have been selected.

Execution Plan

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

0 SELECT STATEMENT optimizer=choose (cost=16180 card=16100 bytes=1400700)

1 0 TABLE ACCESS (by INDEX ROWID) of ' T ' (cost=16180 card=16100 bytes=1400700)

2 1 INDEX (RANGE SCAN) of ' indx_t ' (non-unique) (cost=80 card=16100)

Statistics

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

0 Recursive calls

0 db Block gets

17253 consistent gets

Physical Reads

0 Redo Size

298734 Bytes sent via sql*net to client

12306 bytes received via sql*net from client

1075 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

16100 rows processed

As can be seen from the above results, the use of indexes does result in higher execution costs when accessing large amounts of data, as can be seen from the number of logical reads in the statistics section (consistent gets). The number of logical reads caused by using an index is 10 times times more than the logical reads caused by using an index. As a result, Oracle wisely chooses a full table scan instead of an index scan.

Let's look at the case where there is no index leading column in the WHERE clause:

Test 3)

Sql> select * from t where object_name= ' DEPT ';

10 rows have been selected.

Execution Plan

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

0 SELECT STATEMENT optimizer=choose (cost=29 card=14 bytes=1218)

1 0 TABLE ACCESS (by INDEX ROWID) of ' T ' (cost=29 card=14 bytes=1218)

2 1 INDEX (SKIP SCAN) of ' indx_t ' (non-unique) (cost=14 card=14)

Statistics

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

0 Recursive calls

0 db Block gets

Consistent gets

0 physical Reads

0 Redo Size

1224 Bytes sent via sql*net to client

503 Bytes received via sql*net from client

2 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

Rows processed

Ok! Because only 10 data is queried, Oracle correctly chooses an index hop scan, even if no leading column is used. Let's take a look. If you do not use an index hop scan, the cost of the statement:

Sql> select/**//*+ no_index (t indx_t) */* from T where object_name= ' DEPT ';

10 rows have been selected.

Execution Plan

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

0 SELECT STATEMENT optimizer=choose (cost=38 card=14 bytes=1218)

1 0 TABLE ACCESS (full) of ' T ' (cost=38 card=14 bytes=1218)

Statistics

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

0 Recursive calls

0 db Block gets

375 consistent gets

Physical Reads

0 Redo Size

1224 Bytes sent via sql*net to client

503 Bytes received via sql*net from client

2 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

Rows processed

As we expected, the logical reading (375) that is caused by not using an index is actually more than 10 times times more than the logical read (24) of the index.

To continue our testing, let's look at the case where Oracle does not choose to use the index:

Test 4)

Sql> select * from t where object_name like ' de% ';

180 rows have been selected.

Execution Plan

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

0 SELECT STATEMENT optimizer=choose (cost=38 card=37 bytes=3219)

1 0 TABLE ACCESS (full) of ' T ' (cost=38 card=37 bytes=3219)

Statistics

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

0 Recursive calls

0 db Block gets

386 consistent gets

Physical Reads

0 Redo Size

12614 Bytes sent via sql*net to client

624 Bytes received via sql*net from client

Sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

Processed rows

This time only 180 data is selected, compared with the total data volume of 29,489 in table T, it is obviously only a small part, but Oracle chose a full table scan with 386 logical reads. In this case, what happens if we force the index to be used?

Sql> select/**//*+ INDEX (t indx_t) */* from T where object_name like ' de% ';

180 rows have been selected.

Execution Plan

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

0 SELECT STATEMENT optimizer=choose (cost=182 card=37 bytes=3219)

1 0 TABLE ACCESS (by INDEX ROWID) of ' T ' (cost=182 card=37 bytes=3219)

2 1 INDEX (full SCAN) of ' indx_t ' (non-unique) (cost=144 card=37)

Statistics

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

0 Recursive calls

0 db Block gets

335 consistent gets

0 physical Reads

0 Redo Size

4479 Bytes sent via sql*net to client

624 Bytes received via sql*net from client

Sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

Processed rows

By adding a hint (hint), we force Oracle to use Index Scan, which performs 335 logical reads, which is less than using full-table scans.

This shows that the Oracle optimizer sometimes makes the wrong choice, because it is "smarter" than our SQL statement writers are more aware of the distribution of the data in the table, in which case we can help the Oracle optimizer make better choices by using hints (hint).

A detailed explanation of the use of composite Indexes in Oracle (RPM)