oracle – 使用提示进行查看？

我有一个看法,我想查询我的视图,从基表提示一些索引,我可以这样做吗？

我的意思是：

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

我能否提供意见提示？ (如果我不想在创建此视图时指出它)

您可以对视图使用查询提示,强制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将使用它提示

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>

然而,所有这一切,一般来说,试图调整查询是一个最后的手段 – 通常更好的是找出优化器丢失的信息,并提供适当的统计信息,以便它可以自己做出正确的选择.这是一个更稳定的解决方案.反之,如果您减少指定涉及多层别名的提示 – 例如,通过更改表名称的别名,触摸视图定义的人可以轻松地中断查询.

oracle – 使用提示进行查看？

猜你在找的Oracle相关文章