我的意思是：@H_403_2@
--view create or replace view temp_view as select col1,col2,col3 from table1,table2....
我在table1.col1上有一个名为“index1”的索引.@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;
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@
