and_equal:

description:

The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes. The syntax of the AND_EQUAL hint is AND_EQUAL(table index index) where table specifies the name or alias of the table associated with the indexes to be merged. and index specifies an index on which an index scan is to be performed. You must specify at least two indexes. You cannot specify more than five. (depricated in Oracle 10g)
这种方式需要查询条件里面包括所有索引列,然后取得每个索引中得到的rowid列表,然后对这些列表做merge join,过滤出相同的rowid后再去表中获取数据或者直接从索引中获得数据.and_equal有一些限制,比如它只对单列索引有效,只对非唯一索引有效,使用到的索引不能超过5个,查询条件只能是"=".在10g中,and_equal已经被废弃了,只能通过hint才能生效. (此段文字引用Oracle & Starcraft）

CREATE TABLE t1
(
c1 NUMBER NOT NULL,
c2 NUMBER NOT NULL,
c3 NUMBER NOT NULL
);

CREATE INDEX i1 ON t1 (c2);
CREATE INDEX i2 ON t1 (c3);

表中没有数据：
SQL> set autotrace traceonly
SQL> select c1 from t1
2 where c2=0 and c3=0;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1704772559

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 39 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I1 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------

SQL> insert into t1
2 select object_id,object_id+1,object_id+2 from dba_objects
3 where rownum<11;

SQL> select * from t1;

C1 C2 C3
---------- ---------- ----------
20 21 22
44 45 46
28 29 30
15 16 17
29 30 31
3 4 5
25 26 27
39 40 41
51 52 53
26 27 28
20 21 22

C1 C2 C3
---------- ---------- ----------
44 45 46
28 29 30
15 16 17
29 30 31
3 4 5
25 26 27
39 40 41
51 52 53
26 27 28

20 rows selected.

SQL> analyze table t1 compute statistics for all indexes;

Table analyzed.

SQL> set autotrace traceonly
SQL> select c1 from t1
2 where c2=0 and c3=0;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 39 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> select /*+AND_EQUAL (t1 i1 i2) */ c1 from t1
2 where c2=0 and c3=0;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3194547281

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 39 | 2 (0)| 00:00:01 |
| 2 | AND-EQUAL | | | | | |
|* 3 | INDEX RANGE SCAN | I1 | 1 | | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I2 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------

SQL> select /*+AND_EQUAL (t1 i1 i2) */ c1 from t1
2 where c2=0 or c3=0;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 39 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> select /*+AND_EQUAL (t1 i1 i2) */ c1 from t1
2 where c2=0;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 26 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> select /*+AND_EQUAL (t1 i1 i2) */ * from t1
where c2=0 and c3=0;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 26 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9599/viewspace-472974/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9599/viewspace-472974/