Shulou(Shulou.com)05/31 Report--

This article is about how to use combinatorial indexes in Oracle. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The use of Composite Index in Oracle

Keywords: combined index leader column rule-based optimizer (RBO) cost-based optimizer (CBO) hint (hint)

You can create a composite index in Oracle, that is, an index that contains two or more columns at the same time. In terms of the use of composite indexes, Oracle has the following characteristics:

1. When using a rule-based optimizer (RBO), the composite index is used only if the leading column of the composite index appears in the where clause of the SQL statement

2. When using the cost-based optimizer (CBO) before Oracle9i, the composite index can be used only if the leading column of the composite index appears in the where clause of the SQL statement, depending on the cost calculated by the optimizer to use the index and the cost of using a full table scan. Oracle will automatically select a low-cost access path (see Test 1 and Test 2 below)

3. Starting from Oracle9i, Oracle introduces a new index scanning method-Index hopping scan (index skip scan), which can only be used by cost-based optimizer (CBO). In this way, when there is no leading column of the combined index in the where clause of the SQL statement, and the cost of index jump scanning is lower than that of other scanning methods, Oracle will scan the combined index in this way (see Test 3 below).

4. The Oracle optimizer sometimes makes the wrong choice, because no matter how smart it is, it is not as clear as our SQL statement writers about the distribution of the data in the table. In this case, by using tips (hint), we can help the Oracle optimizer make a better choice (see Test 4 below).

[@ more@]

With regard to the above situation, we have tested as follows:

We create a test table T, whose data is derived from Oracle's data dictionary table all_objects. The structure of table T is as follows:

SQL > desc t

Is the name empty? Types

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 distribution in the table is as follows:

SQL > select object_type,count (*) from 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 lines have been selected.

SQL > select count (*) from t

COUNT (*)

-

29489

We create the following index on table T and analyze 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 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)

10 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 chose the index scan because it used the leading column of the composite index and accessed a small number of records in the table. So what access path would Oracle choose if we accessed 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)

10 TABLE ACCESS (FULL) OF 'T' (Cost=38 Card=16100 Bytes=1400700)

Statistics

0 recursive calls

0 db block gets

1438 consistent gets

13 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 if the leading column of the composite index is used, because Oracle accesses a large amount of data in the table, Oracle chooses to use the full table scan instead of the index, because the optimizer thinks the full table scan is cheaper, but is this really the case? Let's force it to use the index by adding a prompt (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)

10 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

16 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, using an index does result in higher execution costs when accessing a large amount of data, and as can be seen from the logical reads (consistent gets) of the statistics section, the number of logical reads caused by using an index is more than 10 times that of logical reads caused by not using an index. Therefore, Oracle wisely chose a full table scan over an index scan.

Next, let's take a look at the absence of an index leading column in the where clause:

Test 3)

SQL > select * from t where object_name= 'DEPT'

Ten rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=29 Card=14 Bytes=1218)

10 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

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

10 rows processed

OK! Since only 10 pieces of data were queried, even without using the leading column, Oracle correctly selected the index jump scan. Let's take a look at the cost of this statement without index jump scanning:

SQL > select/*+ NO_INDEX (T INDX_T) * / * from t where object_name= 'DEPT'

Ten rows have been selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=14 Bytes=1218)

10 TABLE ACCESS (FULL) OF 'T' (Cost=38 Card=14 Bytes=1218)

Statistics

0 recursive calls

0 db block gets

375 consistent gets

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

10 rows processed

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

Continuing our testing, let's take a look at how Oracle chose not 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)

10 TABLE ACCESS (FULL) OF 'T' (Cost=38 Card=37 Bytes=3219)

Statistics

0 recursive calls

0 db block gets

386 consistent gets

16 physical reads

0 redo size

12614 bytes sent via SQL*Net to client

624 bytes received via SQL*Net from client

13 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

180 rows processed

This time only 180 pieces of data were selected, which is obviously a small fraction compared with the total number of 29489 pieces of data in Table T, but Oracle chose a full table scan with 386logical reads. In this case, what happens if we force the use of indexes?

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)

10 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

13 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

180 rows processed

By adding a hint (hint), we forced Oracle to use an index scan (index full scan), performing 335 logical reads, a little less than when using a full table scan.

It can be seen that the Oracle optimizer sometimes makes the wrong choice, because no matter how smart it is, it is not as smart as us SQL statement writers to know the distribution of the data in the table. In this case, by using hint, we can help the Oracle optimizer make a better choice.

Thank you for reading! This is the end of the article on "how to use combinatorial indexes in Oracle". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.