Avoid Full Table Scan with subquery or analytic function in view

Question

I can reproduce the following behavior both with Oracle 11 (see SQL Fiddle) and Oracle 12.

CREATE TYPE my_tab IS TABLE OF NUMBER(3);

CREATE TABLE test AS SELECT ROWNUM AS id FROM dual CONNECT BY ROWNUM <= 1000;
CREATE UNIQUE INDEX idx_test ON test( id );

CREATE VIEW my_view AS
  SELECT id, COUNT(1) OVER ( PARTITION BY id ) AS cnt
  FROM test;

The following case uses the index as expected:

SELECT * FROM my_view
WHERE id IN ( 1, 2 );

---------------------------------------------------------------------------------                                                                                                                       
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                       
---------------------------------------------------------------------------------                                                                                                                       
|   0 | SELECT STATEMENT     |          |     2 |    52 |     2   (0)| 00:00:01 |                                                                                                                       
|   1 |  VIEW                | MY_VIEW  |     2 |    52 |     2   (0)| 00:00:01 |                                                                                                                       
|   2 |   WINDOW BUFFER      |          |     2 |     8 |     2   (0)| 00:00:01 |                                                                                                                       
|   3 |    INLIST ITERATOR   |          |       |       |            |          |                                                                                                                       
|*  4 |     INDEX UNIQUE SCAN| IDX_TEST |     2 |     8 |     2   (0)| 00:00:01 |                                                                                                                       
---------------------------------------------------------------------------------                                                                                                                       

The following case does not use the index even though the cardinality hint is provided:

SELECT * FROM my_view
WHERE id IN ( SELECT /*+ CARDINALITY( tab 2 ) */ COLUMN_VALUE
              FROM TABLE( NEW my_tab( 1, 2 ) ) tab );

--------------------------------------------------------------------------------------------------                                                                                                      
| Id  | Operation                              | Name    | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                      
--------------------------------------------------------------------------------------------------                                                                                                      
|   0 | SELECT STATEMENT                       |         |     1 |    28 |    33   (4)| 00:00:01 |                                                                                                      
|*  1 |  HASH JOIN RIGHT SEMI                  |         |     1 |    28 |    33   (4)| 00:00:01 |                                                                                                      
|   2 |   COLLECTION ITERATOR CONSTRUCTOR FETCH|         |     2 |     4 |    29   (0)| 00:00:01 |                                                                                                      
|   3 |   VIEW                                 | MY_VIEW |  1000 | 26000 |     4  (25)| 00:00:01 |                                                                                                      
|   4 |    WINDOW SORT                         |         |  1000 |  4000 |     4  (25)| 00:00:01 |                                                                                                      
|   5 |     TABLE ACCESS FULL                  | TEST    |  1000 |  4000 |     3   (0)| 00:00:01 |                                                                                                      
--------------------------------------------------------------------------------------------------                                                                                                      

---

Edit:

Using an inline view and a JOIN instead of IN uses a similar plan:

SELECT /*+ CARDINALITY( tab, 2 ) */ *
FROM ( SELECT id, COUNT(1) OVER ( PARTITION BY id ) AS cnt FROM test ) t
JOIN TABLE( NEW my_tab( 1, 2 ) ) tab ON ( tab.COLUMN_VALUE = t.id );

-----------------------------------------------------------------------------------------------                                                                                                         
| Id  | Operation                              | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                         
-----------------------------------------------------------------------------------------------                                                                                                         
|   0 | SELECT STATEMENT                       |      |     2 |    56 |    33   (4)| 00:00:01 |                                                                                                         
|*  1 |  HASH JOIN                             |      |     2 |    56 |    33   (4)| 00:00:01 |                                                                                                         
|   2 |   COLLECTION ITERATOR CONSTRUCTOR FETCH|      |     2 |     4 |    29   (0)| 00:00:01 |                                                                                                         
|   3 |   VIEW                                 |      |  1000 | 26000 |     4  (25)| 00:00:01 |                                                                                                         
|   4 |    WINDOW SORT                         |      |  1000 |  4000 |     4  (25)| 00:00:01 |                                                                                                         
|   5 |     TABLE ACCESS FULL                  | TEST |  1000 |  4000 |     3   (0)| 00:00:01 |                                                                                                         
-----------------------------------------------------------------------------------------------                                                                                                         

Replacing the analytic function by a LEFT JOIN with GROUP BY does not help either:

SELECT *
FROM ( SELECT t.id, s.cnt
       FROM test t
       LEFT JOIN ( SELECT id, COUNT(*) AS cnt
                   FROM test
                   GROUP BY id
                 ) s ON ( s.id = t.id )
     )
WHERE id IN ( SELECT /*+ CARDINALITY( tab 2 ) */ COLUMN_VALUE
              FROM TABLE( NEW my_tab( 1, 2 ) ) tab );

-----------------------------------------------------------------------------------------------------                                                                                                   
| Id  | Operation                                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                   
-----------------------------------------------------------------------------------------------------                                                                                                   
|   0 | SELECT STATEMENT                         |          |     2 |    64 |    34   (6)| 00:00:01 |                                                                                                   
|*  1 |  HASH JOIN OUTER                         |          |     2 |    64 |    34   (6)| 00:00:01 |                                                                                                   
|   2 |   NESTED LOOPS                           |          |     2 |    12 |    30   (4)| 00:00:01 |                                                                                                   
|   3 |    SORT UNIQUE                           |          |     2 |     4 |    29   (0)| 00:00:01 |                                                                                                   
|   4 |     COLLECTION ITERATOR CONSTRUCTOR FETCH|          |     2 |     4 |    29   (0)| 00:00:01 |                                                                                                   
|*  5 |    INDEX UNIQUE SCAN                     | IDX_TEST |     1 |     4 |     0   (0)| 00:00:01 |                                                                                                   
|   6 |   VIEW                                   |          |  1000 | 26000 |     4  (25)| 00:00:01 |                                                                                                   
|   7 |    HASH GROUP BY                         |          |  1000 |  4000 |     4  (25)| 00:00:01 |                                                                                                   
|   8 |     TABLE ACCESS FULL                    | TEST     |  1000 |  4000 |     3   (0)| 00:00:01 |                                                                                                   
-----------------------------------------------------------------------------------------------------                                                                                                   

Replacing the PL/SQL Collection by a subselect does not seem to help either. The CARDINALITY hint is considered (the plan says 2 rows), but the index is still ignored.

SELECT *
FROM ( SELECT id, cnt FROM my_view )
WHERE id IN ( SELECT /*+ CARDINALITY( tab 2 ) */ id FROM test tab );

---------------------------------------------------------------------------------                                                                                                                       
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                       
---------------------------------------------------------------------------------                                                                                                                       
|   0 | SELECT STATEMENT     |          |     2 |    60 |     4  (25)| 00:00:01 |                                                                                                                       
|   1 |  NESTED LOOPS        |          |     2 |    60 |     4  (25)| 00:00:01 |                                                                                                                       
|   2 |   VIEW               | MY_VIEW  |  1000 | 26000 |     4  (25)| 00:00:01 |                                                                                                                       
|   3 |    WINDOW SORT       |          |  1000 |  4000 |     4  (25)| 00:00:01 |                                                                                                                       
|   4 |     TABLE ACCESS FULL| TEST     |  1000 |  4000 |     3   (0)| 00:00:01 |                                                                                                                       
|*  5 |   INDEX UNIQUE SCAN  | IDX_TEST |     1 |     4 |     0   (0)| 00:00:01 |                                                                                                                       
---------------------------------------------------------------------------------                                                                                                                       

Adding WHERE tab.id <= 2 to the in-list-subquery uses the index, so the optimizer seems to "not take the CARDINALITY hint serious enough" when selecting from a view with analytic functions (or another subselect) and filtering by a list of values.

---

How can I make these queries use the index as expected?

Answer 1

I think the one problem might be that the optimizer refuses to merge a view (and consider any indexes on the underlying tables) when the outer query block contains PL/SQL functions (e.g. TABLE()).

If you manually expand the view and query the table directly, it can access the index fine:

SELECT id, COUNT(1) OVER ( PARTITION BY id ) AS cnt
  FROM test
  WHERE id IN ( SELECT COLUMN_VALUE
              FROM TABLE( NEW my_tab( 1, 2 ) ) tab )
 ;

----------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |          |     1 |     6 |    31   (4)| 00:00:01 |
|   1 |  WINDOW SORT                            |          |     1 |     6 |    31   (4)| 00:00:01 |
|*  2 |   HASH JOIN SEMI                        |          |     1 |     6 |    30   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN                      | IDX_TEST |  1000 |  4000 |     1   (0)| 00:00:01 |
|   4 |    COLLECTION ITERATOR CONSTRUCTOR FETCH|          |  8168 | 16336 |    29   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

I'm not sure if there's a way to override this behavior, or if it's a limitation in the optimizer. I tried moving the TABLE function to a CTE, but that doesn't seem to help.