and_equal最多可以指定5个index！

If the WHERE clauses uses columns of many single-column indexes, then Oracle executes the statement by performing a range scan on each index to retrieve the rowids of the rows that satisfy each condition. Oracle then merges the sets of rowids to obtain a set of rowids of rows that satisfy all conditions. Oracle then accesses the table using these rowids.

Oracle can merge up to five indexes. If the WHERE clause uses columns of more than five single-column indexes, then Oracle merges five of them, accesses the table by rowid, and then tests the resulting rows to determine whether they satisfy the remaining conditions before returning them.

[@more@]

SQL> create table t1(a int , b int ,c int , d int ,e int ,f int);

表已创建。

SQL> insert into t1 values(1,1,1,1,1,1);

已创建 1 行。

SQL> insert into t1 values(2,2,2,2,2,2);

已创建 1 行。

SQL> commit;

提交完成。

SQL> create index idx_a on t1(a);

索引已创建。

SQL> create index idx_b on t1(b);

索引已创建。

SQL> create index idx_c on t1(c);

索引已创建。

SQL> create index idx_d on t1(d);

索引已创建。

SQL> create index idx_e on t1(e);

索引已创建。

SQL> create index idx_f on t1(f);

索引已创建。

SQL> commit;

提交完成。

SQL> set autotrace on
SQL> select *from t1 where a=1 and b=1 and c=1 and d=1 and e=1 and f=1;

A B C D E F
---------- ---------- ---------- ---------- ---------- ----------
1 1 1 1 1 1

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

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

-----

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

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

-----

| 0 | SELECT STATEMENT | | 1 | 78 | 2 (0)| 00:00

:01 |

|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 78 | 2 (0)| 00:00

:01 |

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

:01 |

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

-----

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

1 - filter("B"=1 AND "C"=1 AND "D"=1 AND "E"=1 AND "F"=1)
2 - access("A"=1)

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

统计信息
----------------------------------------------------------
5 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
682 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

--rule下oracle自动使用and_equal对单列index进行了合并
SQL> select /*+ rule */ *from t1 where a=1 and b=1 and c=1 and d=1 and e=1 and f
=1;

A B C D E F
---------- ---------- ---------- ---------- ---------- ----------
1 1 1 1 1 1

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

---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 |
| 2 | AND-EQUAL | |
|* 3 | INDEX RANGE SCAN | IDX_A |
|* 4 | INDEX RANGE SCAN | IDX_B |
|* 5 | INDEX RANGE SCAN | IDX_C |
|* 6 | INDEX RANGE SCAN | IDX_D |
|* 7 | INDEX RANGE SCAN | IDX_E |
---------------------------------------------

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

1 - filter("F"=1)
3 - access("A"=1)
4 - access("B"=1)
5 - access("C"=1)
6 - access("D"=1)
7 - access("E"=1)

Note
-----
- rule based optimizer used (consider using cbo)

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
682 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

--6个index提示不起作用了

SQL> select /*+ and_equal(t1 idx_a idx_b idx_c idx_d idx_e idx_f) */ *from t1 wh
ere a=1 and b=1 and c=1 and d=1 and e=1 and f=1;

A B C D E F
---------- ---------- ---------- ---------- ---------- ----------
1 1 1 1 1 1

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

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

-----

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

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

-----

| 0 | SELECT STATEMENT | | 1 | 78 | 2 (0)| 00:00

:01 |

|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 78 | 2 (0)| 00:00

:01 |

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

:01 |

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

-----

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

1 - filter("B"=1 AND "C"=1 AND "D"=1 AND "E"=1 AND "F"=1)
2 - access("A"=1)

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

统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
682 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

--说明了5个index

SQL> select /*+ and_equal(t1 idx_a idx_b idx_c idx_d idx_e) */ *from t1 where a=
1 and b=1 and c=1 and d=1 and e=1 and f=1;

A B C D E F
---------- ---------- ---------- ---------- ---------- ----------
1 1 1 1 1 1

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

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

-----

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

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

-----

| 0 | SELECT STATEMENT | | 1 | 78 | 5 (0)| 00:00

:01 |

|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 78 | 5 (0)| 00:00

:01 |

| 2 | AND-EQUAL | | | | |
|

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

:01 |

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

:01 |

|* 5 | INDEX RANGE SCAN | IDX_C | 1 | | 1 (0)| 00:00

:01 |

|* 6 | INDEX RANGE SCAN | IDX_D | 1 | | 1 (0)| 00:00

:01 |

|* 7 | INDEX RANGE SCAN | IDX_E | 1 | | 1 (0)| 00:00

:01 |

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

-----

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

1 - filter("A"=1 AND "B"=1 AND "C"=1 AND "D"=1 AND "E"=1 AND "F"=1)
3 - access("A"=1)
4 - access("B"=1)
5 - access("C"=1)
6 - access("D"=1)
7 - access("E"=1)

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

统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
682 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

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

转载于:http://blog.itpub.net/19602/viewspace-1003238/