How Oracle queries access to more than two indexes of the same table (i) Introduction and-equal

I've often seen people ask this question, I've set up multiple indexes on the same table, why does Oracle choose one at a time and not use multiple indexes at the same time? Generally speaking, the common access to the same table more than two indexes, there are three cases, and-equal, index HASH join and bitmap index and/or.

In addition, there is a design question, if there are a, B, c three fields, may be as a query condition, is to establish multiple composite index, or to establish three Single-column index. The problem is not answered because it has a lot to do with the business or the mode of the query, but if you understand when Oracle will choose more than one index to access the table, it will be a great help to understand how to design a reasonable index.

Briefly introduce the and-equal execution plan.

First set up a test table:

sql> CREATE TABLE T_double_ind

2 (ID number,

3 NAME VARCHAR2 (30),

4 TYPE VARCHAR2 (30),

5 CONTENTS VARCHAR2 (4000));

Table created.

Sql> INSERT into T_double_ind

2 SELECT rownum,

3 object_name,

4 Object_type,

5 Lpad (' A ', 1000, ' a ')

6 from Dba_objects;

75856 rows created.

Sql> COMMIT;

Commit complete.

sql> CREATE INDEX Ind_double_name

2 on T_double_ind (NAME);

Index created.

sql> CREATE INDEX Ind_double_type

2 on T_double_ind (TYPE);

Index created.

Now that you've set up a test table and two indexes, let's look at how And-equal executes:

Sql> SELECT ID, NAME, TYPE

2 from T_double_ind

3 WHERE NAME = ' T_double_ind '

4 and TYPE = ' TABLE ';

ID NAME TYPE

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

75688 T_double_ind TABLE

Execution Plan

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

Plan Hash value:474554719

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

| Id | Operation | Name |

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

| 0 |                 SELECT STATEMENT | |

|  1 | TABLE ACCESS by INDEX rowid| T_double_ind |

|   2 |                 and-equal | |

|* 3 | INDEX RANGE SCAN | Ind_double_name |

More Wonderful content: http://www.bianceng.cn/database/Oracle/

|* 4 | INDEX RANGE SCAN | Ind_double_type |

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

predicate information (identified by Operation ID):

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

3-access ("NAME" = ' t_double_ind ')

4-access ("TYPE" = ' TABLE ')

Note

-----

-rule based optimizer used (consider using CBO)

Because two columns are specified, and two columns contain indexes, Oracle chooses to scan two indexes and uses the And-equal execution plan. This scanning method is to obtain the corresponding ROWID of the index key value through two indexes respectively, then merges the equal rowid in two scans, and scans the table through this rowid.

However, the observation execution plan shows that the current optimization mode is RBO, and if the index is collected, run the same query again:

Sql> Show PARAMETER Optimizer_mode

NAME TYPE VALUE