Oracle Composite Index

--composite index, also known as Union Index, combined index.

In Oracle, you can create a composite index, which is an index that contains two or more columns at the same time. Oracle has the following features with regard to the use of composite indexes:

1. When using a rule-based optimizer (RBO), the index is used only if the leading column of the combined index (that is, the first column) appears in the WHERE clause of the SQL statement;

2. The cost-based optimizer (CBO) before using oracle9i is likely to be used only if the lead of the combined index is listed in the WHERE clause of the SQL statement now, depending on the cost of using the index and the cost of using a full table scan that the optimizer calculates. Oracle automatically chooses the low cost access path (see Test 1 and Test 2 below);

3. From oracle9i onwards, Oracle introduced a new index scan Method-index skip scan (index skipping scan), which can only be used by a cost-based optimizer (CBO). This way, when the SQL statement's WHERE clause does not have a leading column for the combined index, and the first column of the index has a high repeat rate, Oracle scans the combined index using that method (see test 3 below);

--About index jump scan meaning understanding, an article says: "It's like skipping its leading column while scanning the index, starting scanning directly from the non-leading column of the index", article link: http://book.51cto.com/art/201312/422441.htm

4. The Oracle optimizer sometimes makes the wrong choice, because it is "smart" and not as well as our SQL statement writers are more aware of the distribution of data in the table, in which case, by using hints (hint), we can help the Oracle optimizer to make better choices.

For the first three cases, we tested each of the following:

We created the test table T, which is derived from Oracle's Data dictionary table all_objects, and the structure of the table T is 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 created the following index on table T and analyzed 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/

The table is parsed.

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

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 wisely selected index scans because of the use of the leading columns of the combined index and access to a small number of records in the table. So, what kind of Access path will Oracle choose if we have access to a large amount of data in the table? Take a look at the following test:

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) ' 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 with the leading columns of the combined index, Oracle chose to use the full table scan without the index because of access to a large amount of data in the table, because the optimizer considered the cost of a full table scan to be lower, but the fact is not true. We force it to use the index by adding a hint (hint):

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 you can see from the results above, the use of indexes does result in higher execution costs when accessing large amounts of data, as can be seen from the logical reads (consistent gets) of the statistics section. The number of logical reads that are caused by using an index is 10 times times more than the logical reads that result from using an index. Therefore, Oracle has wisely chosen a full table scan rather than 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)

Ten rows processed

Let's take a look. If you do not use an index jump 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) ' 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)

Ten rows processed

As we expected, the logical reads (375) that are caused by not using an index are indeed more than 10 times times more logical than using an index (24).

To continue with our tests, now let's look at how Oracle does not choose to use indexes:

--This article from: http://www.cnblogs.com/rootq/archive/2008/10/19/1314669.html, and made a few changes.