Use Hints for views? - Printable Version +- 0Day Forums (https://0day.red) +-- Forum: Coding (https://0day.red/Forum-Coding) +--- Forum: Database (https://0day.red/Forum-Database) +---- Forum: Oracle (https://0day.red/Forum-Oracle) +---- Thread: Use Hints for views? (/Thread-Use-Hints-for-views)

Use Hints for views? - discontentive517251 - 07-31-2023 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) RE: Use Hints for views? - apophyllite881 - 07-31-2023 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. RE: Use Hints for views? - prady485 - 07-31-2023 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*