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

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

-----

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

-----

| 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

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

-----

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

-----

| 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

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

-----

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

-----

| 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>

and_equal最多可以指定5個index！

相關文章