ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【Oracle】如何查询视图时使用索引

【Oracle】如何查询视图时使用索引

原创 Linux操作系统 作者:杨奇龙 时间:2012-06-12 20:25:16 0 删除 编辑
通常我们使用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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
MySQL DBA NoSQL DEVOPS

注册时间:2009-10-07

  • 博文量
    1026
  • 访问量
    7513874