oracle hint之full,index,index_asc,index_desc,index_combile示例

1,full hint

SQL> explain plan for select a,b from t_test where a=3;

已解釋。

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
Plan hash value: 3273242040

--------------------------------------------------------------------------------
----------
| Id | Operation                   | Name       | Rows | Bytes | Cost (%CPU)|
Time     |
--------------------------------------------------------------------------------
----------
|   0 | SELECT STATEMENT            |            |     1 |     8 |     4   (0)|
00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| T_TEST     |     1 |     8 |     4   (0)|
00:00:01 |
|* 2 |   INDEX RANGE SCAN          | IDX_T_TEST |     1 |       |     3   (0)|
00:00:01 |
--------------------------------------------------------------------------------
----------

Predicate Information (identified by operation id):

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

2 - access("A"=3)

已選擇14行。

SQL> explain plan for select /*+ full(t_test) */ a,b from t_test where a=3;

已解釋。

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 |        |     1 |     8 |   306   (2)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T_TEST |     1 |     8 |   306   (2)| 00:00:04 |
----------------------------------------------------------------------------

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

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

1 - filter("A"=3)

已選擇13行。

SQL>

hash int
hash提示對指定的表進行hash 掃描,它僅適用於cluster table中的表

index hint

index提示如果對錶指定多個索引列表,oracle僅會選取成本最低的索引進行掃描表,而不會同時使用列表中所有索引,可以用index_combile

SQL> explain plan for select a,b from t_test where a=1 and b=1;

已解釋。

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

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

Plan hash value: 3273242040

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

----------

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

----------

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

| 0 | SELECT STATEMENT | | 1 | 8 | 4 (0)|
00:00:01 |

|* 1 | TABLE ACCESS BY INDEX ROWID| T_TEST | 1 | 8 | 4 (0)|
00:00:01 |

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

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

----------

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

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

1 - filter("B"=1)
2 - access("A"=1)

已選擇15行。

--oracle依舊僅應用一個索引,而非a,b列的兩個索引
SQL> explain plan for select /*+ index(t_test idx_t_test idx_t_b) */ a,b from
t_test where a=1 and b=1;

已解釋。

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

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

Plan hash value: 3273242040

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

----------

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

----------

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

| 0 | SELECT STATEMENT | | 1 | 8 | 4 (0)|
00:00:01 |

|* 1 | TABLE ACCESS BY INDEX ROWID| T_TEST | 1 | 8 | 4 (0)|
00:00:01 |

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

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

----------

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

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

1 - filter("B"=1)
2 - access("A"=1)

已選擇15行。

index_combile hint
1,連線多個索引進行訪問表
2,以點陣圖方式訪問表
3,如果不指定索引,oracle使用經評估後最小成本的索引組合來訪問表

SQL> explain plan for select /*+ index_combine(t_test idx_t_test idx_t_b) */
a,b from t_test where a=1 and b=1;

已解釋。

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

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

Plan hash value: 3260031232

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

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

U)| Time |

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

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

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

| 0 | SELECT STATEMENT | | 1 | 8 | 951

1)| 00:00:12 |

| 1 | BITMAP CONVERSION TO ROWIDS | | 1 | 8 | 951

1)| 00:00:12 |

| 2 | BITMAP AND | | | |
| |

| 3 | BITMAP CONVERSION FROM ROWIDS| | | |
| |

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

|* 4 | INDEX RANGE SCAN | IDX_T_TEST | | | 3

0)| 00:00:01 |

| 5 | BITMAP CONVERSION FROM ROWIDS| | | |
| |

|* 6 |     INDEX RANGE SCAN            | IDX_T_B    |       |       |   946 --先查詢單個索引,然後轉化為點陣圖,繼而把點陣圖進行與操作,即把兩個索引進行組合運算,因為where條件是and
                                                                                此時把點陣圖再轉化為rowid,最後用rowid定位查詢到的表記錄
                                                                                大家看點陣圖訪問成本很高的,要N步驟

1)| 00:00:12 |

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

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

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

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

4 - access("A"=1)
6 - access("B"=1)

已選擇19行。

SQL> explain plan for select /*+ index_combine(t_test) */ a,b from t_test wh
re a=1 and b=1;

已解釋。

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

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

Plan hash value: 3260031232

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

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

U)| Time |

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

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

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

| 0 | SELECT STATEMENT | | 1 | 8 | 951

1)| 00:00:12 |

| 1 | BITMAP CONVERSION TO ROWIDS | | 1 | 8 | 951

1)| 00:00:12 |

| 2 | BITMAP AND | | | |
| |

| 3 | BITMAP CONVERSION FROM ROWIDS| | | |
| |

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

|* 4 | INDEX RANGE SCAN | IDX_T_TEST | | | 3

0)| 00:00:01 |

| 5 | BITMAP CONVERSION FROM ROWIDS| | | |
| |

|* 6 | INDEX RANGE SCAN | IDX_T_B | | | 946

1)| 00:00:12 |

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

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

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

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

4 - access("A"=1)
6 - access("B"=1)

已選擇19行。

SQL>

oracle hint之full,index,index_asc,index_desc,index_combile示例

相關文章