The change of index merge and and_equal in Oracle

And_equal is a specific operation supported by Oracle that can combine multiple Single-column indexes (index merge) to output query results.

At the same time and_equal can be enforced by hints, with a minimum of two indexes specified, up to 5.

The following execution plans are common and_equal execution methods:

Sql> Select/*+ and_equal (T1 IU II) * * Username,password from T1 where username= ' Eygle ' and user_id=58;

USERNAME PASSWORD

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

Eygle b726e09fe21f8e83

Execution plan

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

Plan Hash value:1425017857

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

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

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

| 0 |      SELECT STATEMENT |     |    1 |     47 | 2 (0) | 00:00:01 |

|* 1 | TABLE ACCESS by INDEX rowid|     T1 |    1 |     47 | 2 (0) | 00:00:01 |

|   2 |      and-equal |       |       |            |          | |

|* 3 | INDEX RANGE SCAN |     II |       1 |     | 1 (0) | 00:00:01 |

|* 4 | INDEX RANGE SCAN |     IU |       1 |     | 1 (0) | 00:00:01 |

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

predicate information (identified by Operation ID):

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

1-filter ("USERNAME" = ' eygle ' and "user_id" =58)

3-access ("user_id" =58)

4-access ("USERNAME" = ' eygle ')

However, starting with Oracle 10g, the and_equal operation was discarded (depricated) and Oracle no longer supports it. The meaning of abandonment here is not to be removed completely, but to say no further improvement, through hints still can force to implement the and_equal operation of the index merge.

The output above is from Oracle 10.2.0.4, and when using the rule hint, Oracle chooses And_equal, so it is no longer supported because the method is too restrictive and is far less flexible than the composite index:

Sql> select * from V$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-prod

Pl/sql Release 10.2.0.4.0-production

CORE 10.2.0.4.0 Production

TNS for 32-bit windows:version 10.2.0.4.0-production

Nlsrtl Version 10.2.0.4.0-production

Sql> Select/*+ Rule */Username,password from T1 where username= ' Eygle ' and user_id=58;

USERNAME PASSWORD

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

Eygle b726e09fe21f8e83

Execution plan

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

Plan Hash value:3072843751

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

| Id | Operation | Name |

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

Back to the column page: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/