前端之家收集整理的这篇文章主要介绍了
oracle – 使用提示进行查看?,
前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
@H_
404_0@
我有一个看法,我想
查询我的视图,从基表
提示一些索引,我可以这样做吗?
我的意思是:@H_403_2@
--view
create or replace view temp_view
as select col1,col2,col3
from table1,table2....
我在table1.col1上有一个名为“index1”的索引.@H_403_2@
我有一个查询:@H_403_2@
--query
select *
from temp_view
where col1=12;
当我看到这个查询的解释计划时,它向我显示,查询不使用“index1”,我想指出它@H_403_2@
所以我想要它,例如:@H_403_2@
--query with hint
select /*+ index(temp_view index1)*/*
from temp_view
where col1=12;
我能否提供意见提示? (如果我不想在创建此视图时指出它)@H_403_2@
您可以对视图使用
查询提示,强制Oracle在基表上使用索引.但是您需要知道基础视图中的基表(如果有)的别名.一般语法是/ * index(<<来自
查询的视图别名><<来自视图>><< index name>>>< 一个例子 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将使用它提示@H_403_2@
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)现在创建视图.验证针对视图的普通查询不使用索引,但强制使用索引,方法是在查询中指定视图别名,并从视图定义中指定表别名@H_403_2@
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>
然而,所有这一切,一般来说,试图调整查询是一个最后的手段 – 通常更好的是找出优化器丢失的信息,并提供适当的统计信息,以便它可以自己做出正确的选择.这是一个更稳定的解决方案.反之,如果您减少指定涉及多层别名的提示 – 例如,通过更改表名称的别名,触摸视图定义的人可以轻松地中断查询.@H_403_2@