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)

Default

I have tested and it doesn't work I mean this /*+ index(temp_view index1)*/ doesn't work..I have written this here because I want to know any other way if there exist to indicate hint for views..I don't want to change view because this view is created by another user and it is not right to change her view. – Default

and one more thing I want to ask...Do you know some useful tutorials which will give me a good knowledge about how to optimize queries by hints? please – Default

@ACP what did you edit?? :D:D I did not find any edition in my post:D – Default

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.

Sawyer

+1 Way more comprehensive than the answer I have just abandoned :) I also agree with the advice about tuning hints as a last resort. – Copyread

Thank you very much I've got the greatest answer...Thank you for your help...I am really surprised...And can I ask you something about how to deep my knowledge about sql hints? I am searching for a good tutorial – Default

And what is a preferable way to to identify which sqls are "bad" and check if you have tuned it well...As I see you use "set autotrace traceonly;" don't you use v$sql_longops or ADDM? – Default

@Default - Identifying which SQL statements are problematic is generally done either from a Top N report from an AWR/ Statspack report or from a session trace where you identify one SQL statement that is accounting for a large fraction of the elapsed time. I was using autotrace just as an easy way of including the query plans. If you find a slow SQL statement where the CBO is generating an incorrect plan, it is generally better to figure out why and to correct the statistics than to throw in hints because correct statistics are likely to fix many problems and are less brittle. – Sawyer

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

Imhoff