Use Hints for views?

Question

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)

Answer 1

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.

Answer 2

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