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

「爱情、让人受尽委屈。」 2023-07-17 02:44 9阅读 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)

内容转自 [https://www.cnblogs.com/emilyyoucan/p/7873041.html][https_www.cnblogs.com_emilyyoucan_p_7873041.html]

[https_www.cnblogs.com_emilyyoucan_p_7873041.html]: https://www.cnblogs.com/emilyyoucan/p/7873041.html