【Oracle】如何查詢檢視時使用索引
通常我們使用hint來固定查詢計劃選擇走表的索引 固定表的連線等等，但是如果第一層查詢的是檢視呢？
yang@rac1>CREATE TABLE TA (ID NUMBER, NAME VARCHAR2(30), TYPE VARCHAR2(30));
Table created.
yang@rac1>CREATE TABLE TB (ID NUMBER, NAME VARCHAR2(30), TYPE VARCHAR2(30));
Table created.
yang@rac1>create index idx_id_ta on ta(id);
Index created.
yang@rac1>create index idx_id_tb on tb(id);
Index created.
yang@rac1>CREATE VIEW V_Tab AS
2 SELECT * FROM TA
3 UNION ALL
4 SELECT * FROM TB;
View created.
普通的查詢檢視，並沒有走索引。
yang@rac1> SELECT *
2 FROM V_TAB
3 WHERE ID<2500;
9996 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4036260501
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 2 (0)| 00:00:01 |
| 1 | VIEW | V_TAB | 1 | 47 | 2 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | TABLE ACCESS FULL| TA | 4998 | 229K| 9 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TB | 4998 | 229K| 9 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ID"<2500)
4 - filter("ID"<2500)
Note
-----
- dynamic sampling used for this statement (level=2)
====使用常規使用hint的方式(表名 索引名稱)
yang@rac1> SELECT /*+ index(tb idx_id_tb) index(ta idx_id_ta) */*
2 FROM V_TAB
3 WHERE ID<2500;
9996 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4036260501
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 2 (0)| 00:00:01 |
| 1 | VIEW | V_TAB | 1 | 47 | 2 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | TABLE ACCESS FULL| TA | 4998 | 229K| 9 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TB | 4998 | 229K| 9 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ID"<2500)
4 - filter("ID"<2500)
並不湊效！依然走全表掃描！
Note
-----
- dynamic sampling used for this statement (level=2)
==使用 檢視字首修飾表名的方式(VIVE.TABNAME INDEX_NAME)
yang@rac1> SELECT /*+ index(v_tab.tb idx_id_tb) index(v_tab.ta idx_id_ta) */*
2 FROM V_TAB
3 WHERE ID<2500;
9996 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 531820221
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 2 (0)| 00:00:01 |
| 1 | VIEW | V_TAB | 1 | 47 | 2 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| TA | 4998 | 229K| 18 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_ID_TA | 4998 | | 18 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| TB | 4998 | 229K| 5016 (1)| 00:01:01 |
|* 6 | INDEX RANGE SCAN | IDX_ID_TB | 4998 | | 17 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("ID"<2500)
6 - access("ID"<2500)
yang@rac1>
對於建立檢視的時候包含表的別名的情況：
yang@rac1>CREATE VIEW V_Tab02 AS
2 SELECT * FROM TA t1
3 UNION ALL
4 SELECT * FROM TB t2 ;
View created.
yang@rac1> SELECT /*+ index(tb idx_id_tb) index(ta idx_id) */*
2 FROM V_TAB02
3 WHERE ID<2500;
9996 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3023640653
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 2 (0)| 00:00:01 |
| 1 | VIEW | V_TAB02 | 1 | 47 | 2 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | TABLE ACCESS FULL| TA | 4998 | 229K| 9 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TB | 4998 | 229K| 9 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ID"<2500)
4 - filter("ID"<2500)
yang@rac1> SELECT /*+ index(v_tab02.tb idx_id_tb) index(v_tab02.ta idx_id) */*
2 FROM V_TAB02
3 WHERE ID<2500;
9996 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3023640653
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 2 (0)| 00:00:01 |
| 1 | VIEW | V_TAB02 | 1 | 47 | 2 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | TABLE ACCESS FULL| TA | 4998 | 229K| 9 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TB | 4998 | 229K| 9 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ID"<2500)
4 - filter("ID"<2500)
==此時必須使用建立檢視的表的相對應的別名(VIVE.TAB_ALIAS_NAME INDEX_NAME)
yang@rac1> SELECT /*+ index(v_tab02.t2 idx_id_tb) index(v_tab02.t1 idx_id) */*
2 FROM V_TAB02
3 WHERE ID<2500;
9996 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3173198873
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 2 (0)| 00:00:01 |
| 1 | VIEW | V_TAB02 | 1 | 47 | 2 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| TA | 4998 | 229K| 18 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_ID | 4998 | | 18 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| TB | 4998 | 229K| 5016 (1)| 00:01:01 |
|* 6 | INDEX RANGE SCAN | IDX_ID_TB | 4998 | | 17 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("ID"<2500)
6 - access("ID"<2500)
Note
-----
- dynamic sampling used for this statement (level=2)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-732606/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle 常用查詢檢視Oracle
- 如何使用SQL查詢檢視，Postico使用技巧分享~SQL
- RAC：在子查詢使用gv$檢視，有時查詢不出資料
- 【索引】Oracle查詢指定索引提高查詢效率索引Oracle
- 【索引】oracle查詢使用索引和不使用索引的比較索引Oracle
- 【PDB】Oracle跨PDB檢視查詢Oracle
- Oracle 索引的使用情況檢視Oracle索引
- 【SQL】Oracle查詢轉換之物化檢視查詢重寫SQLOracle
- oracle全文索引之如何實現查詢Oracle索引
- mongodb 如何檢視索引MongoDB索引
- 使用物化檢視查詢重寫 優化對於 UNION ALL檢視的CONNECT BY查詢優化
- 關於oracle使用者許可權查詢總結檢視Oracle
- 檢視查詢報錯
- 【MySQL】檢視&子查詢MySql
- 【SQL】Oracle查詢轉換之檢視合併SQLOracle
- Oracle檢視查詢慢之統計資訊收集Oracle
- oracle資料庫sql查詢檢視第二次查詢很慢Oracle資料庫SQL
- 使用index_stats檢視檢視索引效率Index索引
- Oracle OCP(14)：使用子查詢檢索資料Oracle
- NULL列時，如何使得IS NULL或者IS NOT NULL可以使用索引來提高查詢效率Null索引
- Oracle查詢轉換（一）簡單檢視合併Oracle
- Oracle查詢轉換（二）複雜檢視合併Oracle
- 從oracle v$version檢視中查詢os的資訊Oracle
- 檢視慢查詢進度
- 【VIEW】Oracle如何查詢固定檢視的定義或底層sql語句ViewOracleSQL
- 如何檢視某個查詢用了多少TempDB空間
- 物化檢視上使用bitmap索引索引
- Oracle查詢轉換（三）外連線檢視合併Oracle
- Oracle中分組查詢時keep的使用[轉][Oracle
- oracle學習筆記——檢視、索引Oracle筆記索引
- 索引監控-查詢從未被使用過的索引索引
- 為何在查詢中索引未被使用索引
- MySQL 查詢的成本的檢視MySql
- OushuDB 檢視查詢執行情況
- 【MV】物化檢視查詢重寫
- sql查詢檢視列備註SQL
- 【oracle 效能優化】組合索引查詢。Oracle優化索引
- 常用查詢Oracle的表，檢視，儲存過程，使用者等SQL命令Oracle儲存過程SQL