How Oracle queries access to more than two indexes in the same table (iii) Index HASH join execution plan

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.

A brief introduction to the index HASH join execution plan.

First set up a test table:

sql> DROP TABLE t_double_ind PURGE;

Table dropped.

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;

76058 rows created.

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 have a test table and two indexes, let's see how you can use an index hash connection:

Sql> EXEC dbms_stats. Gather_table_stats (USER, ' T_double_ind ')

Pl/sql procedure successfully completed.

Sql> SET Autot on EXP

Sql> SELECT NAME, TYPE

2 from T_double_ind

3 WHERE NAME = ' T_double_ind '

4 and TYPE = ' TABLE ';

NAME TYPE

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

T_double_ind TABLE

Execution Plan

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

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

Plan Hash value:3887138334

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

| id| Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

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

| 0 |                 SELECT STATEMENT |    |    1 |     32 | 3 (0) | 00:00:01 |

|*1 | TABLE ACCESS by INDEX rowid|    T_double_ind |    1 |     32 | 3 (0) | 00:00:01 |

|*2 | INDEX RANGE SCAN |    Ind_double_name |       2 |     | 1 (0) | 00:00:01 |

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

predicate information (identified by Operation ID):

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

1-filter ("TYPE" = ' TABLE ')

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

Sql> SELECT/*+ index_join (A ind_double_name ind_double_type) */NAME, TYPE

2 from T_double_ind A

3 WHERE NAME = ' T_double_ind '

4 and TYPE = ' TABLE ';