oracle 对视图使用提示？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4222430/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use Hints for views?
提问by kupa
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 indexon table1.col1called "index1".
我在table1.col1上有一个名为“ 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..
当我看到这个查询的解释计划时，它告诉我该查询不使用“index1”，我想指出它..
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)
我可以指出意见的提示吗？？（如果我不想在创建此视图期间指出它）
回答by Justin Cave
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>>) */
您可以对针对视图的查询使用提示，以强制 Oracle 使用基表上的索引。但是您需要知道基础视图中基表（如果有）的别名。一般语法是/*+ 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
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) Verify that the index is not used normally but that Oracle will use it with a hint
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) 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
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>
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.
尽管如此，在尝试调整查询时，提示通常是最后的手段——通常最好弄清楚优化器缺少哪些信息并提供适当的统计信息，以便它可以自己做出正确的选择。这是一个更稳定的解决方案。当您减少到指定涉及多层别名的提示时，更是如此——例如，对于触摸视图定义的人来说，通过更改表名的别名来破坏您的查询太容易了。
回答by 3per
I tried Justin Cave's (the answer beside) syntax
我尝试了 Justin Cave 的（旁边的答案）语法
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
我试过Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
