Hi,
I have a LOV query as below
SELECT
*
FROM
(
SELECT s.col1 -- varchar2 column
FROM
table_1 s
WHERE s.code != 'R'
AND EXISTS (SELECT 'X' FROM v_view_1 vs -- complex view
WHERE vs.col2 = s.col2
AND vs.status IN ('A','B')
AND ROWNUM < 2
)
) inline_view
WHERE upper(col1) = upper('&I_col1');There is a function based index on
create index table_1_indx_fn on table_1(UPPER(COL1),COL2);
Now execution plan shows full table scan of table_1. If index table_1_indx_fn used then query will be benfit.
How to make above query use index table_1_indx_fn ?
Does queries like this can be made to use index using hints ?
Due to limitation of UI functionality we are not able to push upper(col1) = upper('&I_col1') where clause, Dev want to all values from col1 when user clicks on LOV
Thanks,
Girish
There is nothing *stopping* this kind of query from using the index, eg
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> create index t1ix on t1 ( upper(object_name));
Index created.
SQL>
SQL> explain plan for
2 select *
3 from
4 (
5 select object_name
6 from t1
7 where secondary != 'X'
8 and exists
9 ( select 'x'
10 from dba_objects d
11 where d.object_type = t1.object_type
12 and created > date '1900-01-01'
13 and rownum < 2
14 )
15 ) inline_view
16 where upper(object_name) = upper('emp');
Explained.
SQL>
SQL> select * from dbms_xplan.display();
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 896 | 6252K (1)| 00:04:05 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 398 | 44576 | 168 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1IX | 318 | | 3 (0)| 00:00:01 | <<======
|* 4 | COUNT STOPKEY | | | | | |
|* 5 | VIEW | DBA_OBJECTS | 70981 | 901K| 125K (1)| 00:00:05 |
| 6 | UNION-ALL | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | SUM$ | 1 | 10 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | I_SUM$_1 | 1 | | 0 (0)| 00:00:01 |
|* 9 | FILTER | | | | | |
|* 10 | HASH JOIN | | 79572 | 7304K| 415 (3)| 00:00:01 |
| 11 | INDEX FULL SCAN | I_USER2 | 152 | 608 | 1 (0)| 00:00:01 |
|* 12 | HASH JOIN | | 79572 | 6993K| 413 (3)| 00:00:01 |
| 13 | INDEX FULL SCAN | I_USER2 | 152 | 3800 | 1 (0)| 00:00:01 |
|* 14 | TABLE ACCESS FULL | OBJ$ | 79572 | 5050K| 411 (2)| 00:00:01 |
| 15 | NESTED LOOPS | | 1 | 32 | 4 (0)| 00:00:01 |
| 16 | NESTED LOOPS | | 1 | 23 | 3 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 10 | 2 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 |
|* 19 | TABLE ACCESS CLUSTER | TAB$ | 1 | 13 | 1 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | I_OBJ1 | 1 | 9 | 1 (0)| 00:00:01 |
|* 21 | TABLE ACCESS CLUSTER | TAB$ | 1 | 13 | 2 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 |
|* 23 | TABLE ACCESS BY INDEX ROWID | SEQ$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | I_SEQ1 | 1 | | 0 (0)| 00:00:01 |
|* 25 | TABLE ACCESS BY INDEX ROWID BATCHED| USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | 1 (0)| 00:00:01 |
|* 27 | TABLE ACCESS BY INDEX ROWID BATCHED| USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | 1 (0)| 00:00:01 |
| 29 | NESTED LOOPS SEMI | | 1 | 29 | 2 (0)| 00:00:01 |
|* 30 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
|* 31 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 |
|* 32 | FILTER | | | | | |
|* 33 | HASH JOIN | | 6 | 96 | 3 (0)| 00:00:01 |
|* 34 | TABLE ACCESS FULL | LINK$ | 6 | 72 | 2 (0)| 00:00:01 |
| 35 | INDEX FULL SCAN | I_USER2 | 152 | 608 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
so it really comes down to a costing decision by the optimizer. However, one thing that *can* sometimes stop a query from being merged with its outer query is "rownum" and you do not need that for an exists clause. So try the following
SELECT *
FROM
(
SELECT s.col1 -- varchar2 column
FROM table_1 s
WHERE s.code != 'R'
AND EXISTS (SELECT 'X' FROM v_view_1 vs -- complex view
WHERE vs.col2 = s.col2
AND vs.status IN ('A','B') <=== no more rownum clause
)
) inline_view
WHERE upper(col1) = upper('&I_col1');
If that doesn't work, get back to us and we'll look at other options