SQL> select count(*) from t4;

COUNT(*)

----------

10000

id为主键，n上有索引

SQL> EXPLAIN PLAN FOR

2 SELECT /*+ index_join(t4 t4_n t4_pk) */ id, n --通过hint强制索引连接

3 FROM t4

4 WHERE id BETWEEN 10 AND 20

5 AND n < 100;

已解释。

SQL> SELECT * FROM table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1079343316

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

e |

PLAN_TABLE_OUTPUT

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

| 0 | SELECT STATEMENT | | 1 | 10 | 5 (20)| 00

00:01 |

|* 1 | VIEW | index$_join$_001 | 1 | 10 | 5 (20)| 00

00:01 |

|* 2 | HASH JOIN | | | | | |

|* 3 | INDEX RANGE SCAN| T4_PK | 1 | 10 | 3 (34)| 00

00:01 |

|* 4 | INDEX RANGE SCAN| T4_N | 1 | 10 | 3 (34)| 00

00:01 |

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

Predicate Information (identified by operation id):

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

PLAN_TABLE_OUTPUT

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

1 - filter("ID"<=20 AND "N"<100 AND "ID">=10)

2 - access(ROWID=ROWID) --是通过rowid连接的

3 - access("ID">=10 AND "ID"<=20)

4 - access("N"<100)

已选择19行。

索引连接的目的就是通过一张表的不通索引之间的连接去代替全表扫描。

ITPub博客

索引扫描