Skip to Main Content
  • Questions
  • Index is not used when an Inline view is present

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Girish.

Asked: March 31, 2021 - 4:54 am UTC

Last updated: March 31, 2021 - 6:30 am UTC

Version: 12.1.x

Viewed 100+ times

You Asked

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

and we said...

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

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.