oracle hint之hint_index_ffs,index_join

oracle hint_index_ffs,index_join

index_ffs hint
1,对表用快速索引全扫描进行访问
2,经测,仅count可以使用index_ffs,而非count聚合函数好像不能使用index_ffs
SQL> explain plan for select /*+ index_ffs(t_test idx_t_test */  a from t_test;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 2796558804

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |   999K|  4882K|   305   (2)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| T_TEST |   999K|  4882K|   305   (2)| 00:00:04 |
----------------------------------------------------------------------------

已选择8行。

SQL> explain plan for select  /*+ index_ffs(t_test idx_t_test) */  count(a) from
 t_test;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 1936070979

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

----

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

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

----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |            |     1 |     5 |   384   (2)| 00:00:

05 |

|   1 |  SORT AGGREGATE       |            |     1 |     5 |            |
   |

|   2 |   INDEX FAST FULL SCAN| IDX_T_TEST |   999K|  4882K|   384   (2)| 00:00:

05 |

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

----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

 

已选择9行。

SQL>

index_join
1,index_join会自动连接where条件多个索引列
2,index_join不像index_combile转化为位图方式访问表,它是直接以rowid访问
SQL> explain plan for select /*+index_join(t_test idx_t_test idx_t_b) */ a,b fro
m t_test where a=3 and b=1;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 4085113357

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

-------

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

e     |

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

-------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                  |     1 |     8 |   951   (1)| 00:

00:12 |

|*  1 |  VIEW              | index$_join$_001 |     1 |     8 |   951   (1)| 00:

00:12 |

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

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

00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|*  4 |    INDEX RANGE SCAN| IDX_T_B          |     1 |     8 |   948   (1)| 00:

00:12 |

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

-------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("A"=3 AND "B"=1)
   2 - access(ROWID=ROWID)
   3 - access("A"=3)
   4 - access("B"=1)

已选择19行。

SQL>

SQL> explain plan for select /*+index_join(t_test idx_t_test idx_t_b) */ a,b fro
m t_test where a=3 and b=1 and c=19;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 2799059507

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

--------

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

me     |

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

--------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |                  |     1 |     8 |   958   (1)| 00

:00:12 |

|*  1 |  VIEW               | index$_join$_001 |     1 |     8 |   958   (1)| 00

:00:12 |

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

|*  3 |    HASH JOIN        |                  |       |       |            |
       |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|*  4 |     INDEX RANGE SCAN| IDX_T_TEST       |     1 |     8 |     3   (0)| 00

:00:01 |

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

:00:01 |

|*  6 |    INDEX RANGE SCAN | IDX_T_B          |     1 |     8 |   948   (1)| 00

:00:12 |

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------

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

   1 - filter("A"=3 AND "C"=19 AND "B"=1)
   2 - access(ROWID=ROWID)
   3 - access(ROWID=ROWID)
   4 - access("A"=3)
   5 - access("C"=19)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   6 - access("B"=1)

已选择23行。

SQL>
SQL> explain plan for select /*+index_join(t_test idx_t_test idx_t_b idx_c) */ a
,b from t_test where a=3 and b=1 and c=20;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 2799059507

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

--------

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

me     |

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

--------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |                  |     1 |     8 |   958   (1)| 00

:00:12 |

|*  1 |  VIEW               | index$_join$_001 |     1 |     8 |   958   (1)| 00

:00:12 |

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

|*  3 |    HASH JOIN        |                  |       |       |            |
       |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|*  4 |     INDEX RANGE SCAN| IDX_T_TEST       |     1 |     8 |     3   (0)| 00

:00:01 |

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

:00:01 |

|*  6 |    INDEX RANGE SCAN | IDX_T_B          |     1 |     8 |   948   (1)| 00

:00:12 |

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------

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

   1 - filter("A"=3 AND "C"=20 AND "B"=1)
   2 - access(ROWID=ROWID)
   3 - access(ROWID=ROWID)
   4 - access("A"=3)
   5 - access("C"=20)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   6 - access("B"=1)

已选择23行。

SQL>