logo标签列表

使用提示来优化视图？

oracleoptimizationindexingsql-execution-planhints
14

我有一个视图，并且我想像这样查询我的视图来提示一些基表中的索引，我能做到吗？

我的意思是：

--view
create or replace view temp_view
as select col1,col2,col3
from table1,table2....

我在 table1.col1 上有一个名为 "index1" 的索引。

我有一个查询：

--query
select * 
from temp_view 
where col1=12;

当我查看此查询的执行计划时，发现该查询未使用“index1”，我想要指出这一点。

因此，举个例子，我希望它变成：

--query with hint
select /*+ index(temp_view  index1)*/* 
from temp_view 
where col1=12;

我能否为视图指定提示？（如果我不想在创建此视图时指定它）

我已经测试过了，它不起作用，我的意思是这个 /+ index(temp_view index1)/ 不起作用。我在这里写下来是因为我想知道是否存在其他指示视图提示的方法。我不想更改视图，因为这个视图是由另一个用户创建的，更改她的视图是不正确的。 - kupa
还有一件事我想问……你知道一些有用的教程，可以让我了解如何通过提示来优化查询吗？谢谢。 - kupa
@ACP 你编辑了什么？？ :D:D 我在我的帖子中没有发现任何编辑:D - kupa
2个回答
19
您可以在针对视图的查询中使用提示，以强制Oracle使用基表上的索引。但您需要知道底层视图中基本表的别名（如果有的话）。一般的语法是：/*+ index(<<从查询中得到的视图别名>> <<从视图中得到的表别名>> <<索引名称>>) */ 例如：
1）创建一个带有10,000条相同记录的表，并在该表上创建一个索引。由于索引不具有选择性，因此Oracle不会使用它。
SQL> ed
Wrote file afiedt.buf

  1  create table foo
  2  as
  3  select 1 col1
  4    from dual
  5* connect by level <= 10000
SQL> /

Table created.

SQL> create index idx_foo on foo(col1);

Index created.

2) 确认该索引通常不被使用但Oracle将使用提示来使用它

SQL> set autotrace traceonly;
SQL> select * from foo where col1 = 1;

10000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1245013993

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 |   126K|     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| FOO  | 10000 |   126K|     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("COL1"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
        713  consistent gets
          5  physical reads
          0  redo size
     172444  bytes sent via SQL*Net to client
       7849  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL> select /*+ index(foo idx_foo) */ *
  2    from foo
  3   where col1 = 1;

10000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 15880034

----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         | 10000 |   126K|    25   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_FOO | 10000 |   126K|    25   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - access("COL1"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
        715  consistent gets
         15  physical reads
          0  redo size
     172444  bytes sent via SQL*Net to client
       7849  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

3) 现在创建视图。通过指定查询中的视图别名和视图定义中的表别名来强制使用索引，验证对视图的普通查询不使用索引。

SQL> create view vw_foo
  2  as
  3  select col1
  4    from foo f;

View created.

SQL> select col1
  2    from vw_foo
  3   where col1 = 1;

10000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1245013993

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 |   126K|     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| FOO  | 10000 |   126K|     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("COL1"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         16  recursive calls
          0  db block gets
        715  consistent gets
          0  physical reads
          0  redo size
     172444  bytes sent via SQL*Net to client
       7849  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL> select /*+ index(vf f idx_foo) */ col1
  2    from vw_foo vf
  3   where col1 = 1;

10000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 15880034

----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         | 10000 |   126K|    25   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_FOO | 10000 |   126K|    25   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - access("COL1"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         14  recursive calls
          0  db block gets
        717  consistent gets
          0  physical reads
          0  redo size
     172444  bytes sent via SQL*Net to client
       7849  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL>

尽管如此，一般来说，提示是在调整查询时的最后一个选择--通常更好的方法是找出优化器缺少的信息，并提供适当的统计数据，以便它可以自己做出正确的选择。这是一个更加稳定的解决方案。在涉及多层别名的提示中，如果你被迫使用提示，那么很容易因为某人更改表名的别名而破坏你的查询，例如。

比我刚刚放弃的答案要全面得多 :) 我也同意最后才使用调整提示的建议。 - APC
非常感谢，我得到了最好的答案...谢谢你的帮助...我真的很惊讶...我可以问一下关于如何深入了解SQL提示的问题吗？我正在寻找一个好的教程。 - kupa
什么是识别哪些SQL语句是“糟糕”的并检查是否已经进行了优化的首选方法？我看到你使用了“set autotrace traceonly;”，你不使用v$sql_longops或ADDM吗？ - kupa
@kupa - 通常确定哪些 SQL 语句存在问题要么通过 AWR/ Statspack 报告中的 Top N 报告，要么通过会话跟踪来识别占用大部分经过时间的一条 SQL 语句。我使用 autotrace 只是为了方便包含查询计划。如果您找到一个慢的 SQL 语句，其中 CBO 生成了不正确的计划，通常最好找出原因并更正统计信息，而不是添加提示，因为正确的统计信息可能会解决许多问题，并且更加健壮。 - Justin Cave
4
我尝试了Justin Cave（旁边的答案）的语法。
select /*+ index(vf f idx_foo) */ col1
from vw_foo vf
where col1 = 1;

我尝试了这个方法，但对我不起作用。接下来的方法可行。

select /*+ index(vf.f idx_foo) */ col1
from vw_foo vf
where col1 = 1;

我尝试了Oracle数据库11g企业版Release 11.2.0.1.0 - 64位生产版本

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接