当前位置： 文江博客话题详情

使用视图提示？

发布于 2024-10-03 11:02:23 字数 598 浏览 1 评论 0原文

我有一个视图，我想像这样查询我的视图以暗示基表中的某些索引，我可以这样做吗？

我的意思是：

--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;

我可以指出视图提示吗？ （如果我不想在创建此视图期间指出它）

原文

I have a view and I want to query my view like that to hint some index from a base table,can I do that?

I mean:

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

I have an index on table1.col1 called "index1".

I have a query:

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

And when I see explain plan of this query it shows me that query doesn't use "index1" and I want to indicate it..

So I want it to be,for example:

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

Can I indicate hints for views?? (If I don't want to indicate it during creation of this view)

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（2）

永言不败 2024-10-10 11:02:23

您可以在针对视图的查询上使用提示来强制 Oracle 使用基表上的索引。但是您需要知道基础视图中基表（如果有）的别名。一般语法为 /*+ index(<<查询中视图的别名>> <<视图中表的别名>> <<索引名称>>) */< /code>

示例

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>

尽管如此，提示通常是最后的手段当尝试调整查询时，通常最好找出优化器缺少哪些信息并提供适当的统计信息，以便它可以自行做出正确的选择。这是一个更加稳定的解决方案。更重要的是，当您减少到指定涉及多层别名的提示时，例如，对于接触视图定义的人来说，通过更改表名的别名来破坏您的查询太容易了。

You can use a hint on a query against a view to force Oracle to use an index on the base table. But you need to know the alias of the base table (if any) in the underlying view. The general syntax would be /*+ index(<<alias of view from query>> <<alias of table from view>> <<index name>>) */

An example

1) Create a table with 10,000 identical rows and create an index on the table. The index won't be selective, so Oracle won't want to use it

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) Verify that the index is not used normally but that Oracle will use it with a hint

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) Now create the view. Verify that normal queries against the view don't use the index but force the index to be used by specifying both the view alias in the query and the table alias from the view definition

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>

All that said, however, hints in general are a last resort when trying to tune a query-- it's generally far preferable to figure out what information the optimizer is missing and provide appropriate statistics so that it can make the correct choice on its own. That's a much more stable solution going forward. Doubly so when you're reduced to specifying hints that involve multiple layers of aliases-- it's way too easy for someone touching the view definition to break your query by changing the alias of the table name, for example.

回复 原文
野心澎湃 2024-10-10 11:02:23

我尝试了贾斯汀·凯夫（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 Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production 上尝试的工作

I tried Justin Cave's (the answer beside) syntax

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

, but it doesn't work for me. The next is worked

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

I tried on Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

回复 原文
~没有更多了~

关于作者

残疾

暂无简介

0 文章
0 评论
20 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

策马西风

文章 0 评论 0

柠檬心

文章 0 评论 0

1331

文章 0 评论 0

七度光

文章 0 评论 0

qq_oc2LaO

文章 0 评论 0

野却迷人

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文