and_equal的hints

and_equal

AND_EQUALS(table_name This hint instructs the optimizer to merge the
index_name index_name specified indexes when retrieving rows for the
index_name ....) specified table. Similar to INDEX_COMBINE but
does not use the bitmap conversion strategy that
requires Enterprise Edition.

这种方式需要查询条件里面包括所有索引列,然后取得每个索引中得到的rowid列表,然后对这些列表做merge join,过滤出相同的rowid后再去表中获取数据或者直接从索引中获得数据.
and_equal有一些限制,比如它只对单列索引有效,只对非唯一索引有效,使用到的索引不能超过5个,查询条件只能是”=”.在10g中,and_equal已经被废弃了,只能通过hint才能生效.

SQL> CREATE TABLE a AS SELECT * FROM dba_objects
  2  ;

表已创建。

SQL> CREATE INDEX idx_object_name ON a(object_name);

索引已创建。

SQL> CREATE INDEX idx_object_id ON a(object_id);

索引已创建。

SQL> set autotrace on explain;
SQL> set autotrace on explain
SQL> SELECT /*+ AND_EQUAL(A idx_object_name idx_object_id) */  object_name,object_id FROM A WHERE object_name='A' AND object_id=23232;

未选定行

执行计划
----------------------------------------------------------
Plan hash value: 3861565527

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

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

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

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

|*  1 |  AND-EQUAL        |                 |       |       |            |
    |

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

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

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

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_NAME"='A' AND "OBJECT_ID"=23232)
   2 - access("OBJECT_ID"=23232)
   3 - access("OBJECT_NAME"='A')

Note
-----
   - dynamic sampling used for this statement