Nested table as condition prevents using index The following query joining a subquery does a TABLE ACCESS FULL when the master-table uses nested table as condition. When the condition is a simple =, the same query does an INDEX RANGE SCAN as expected. (DDL below)

Why does the query with the nested table not use the index?

SELECT m.id, sub.cnt
  FROM m
  LEFT JOIN (
    SELECT d.m_id, COUNT(1) AS cnt -- (+ other functions)
      FROM d
     GROUP BY d.m_id
  ) sub ON ( sub.m_id = m.id )
 WHERE m.id IN ( SELECT /*+ CARDINALITY(tab,1) */ COLUMN_VALUE FROM TABLE( NEW t(1) ) tab )
-- WHERE m.id = 1 -- alternative condition




Condition m.id = 1 uses the index on d as expected:

----------------------------------------------------------------------------
| Id | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT    |       |  1000K|    37M|     1   (0)| 00:00:01 |
|  1 |  NESTED LOOPS OUTER |       |  1000K|    37M|     1   (0)| 00:00:01 |
|* 2 |   INDEX UNIQUE SCAN | PK_M  |     1 |    13 |     0   (0)| 00:00:01 |
|  3 |   VIEW              |       |  1000K|    24M|     1   (0)| 00:00:01 |
|  4 |    SORT GROUP BY    |       |  1000K|    12M|     1   (0)| 00:00:01 |
|* 5 |     INDEX RANGE SCAN| IDX_D |  1000K|    12M|     1   (0)| 00:00:01 |
----------------------------------------------------------------------------


Using the nested table does not use the index:

------------------------------------------------------------------------------------------------------
| Id | Operation                                | Name | Rows  | Bytes |TempSp|Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                         |      |  1000K|    39M|      |  185K  (4)| 00:37:05 |
|* 1 |  HASH JOIN OUTER                         |      |  1000K|    39M| 2640K|  185K  (4)| 00:37:05 |
|  2 |   MERGE JOIN SEMI                        |      |   100K|  1464K|      |   30   (4)| 00:00:01 |
|  3 |    INDEX FULL SCAN                       | PK_M |    10M|   123M|      |    0   (0)| 00:00:01 |
|* 4 |    SORT UNIQUE                           |      |     1 |     2 |      |   30   (4)| 00:00:01 |
|  5 |     COLLECTION ITERATOR CONSTRUCTOR FETCH|      |     1 |     2 |      |   29   (0)| 00:00:01 |
|  6 |   VIEW                                   |      |   100M|  2479M|      | 4898 (100)| 00:00:59 |
|  7 |    HASH GROUP BY                         |      |   100M|  1239M|      | 4898 (100)| 00:00:59 |
|  8 |     TABLE ACCESS FULL                    | D    |   100M|  1239M|      |  571  (95)| 00:00:07 |
------------------------------------------------------------------------------------------------------




DDL

CREATE TABLE m ( id NUMBER);
  ALTER TABLE m ADD CONSTRAINT pk_m PRIMARY KEY ( id );
CREATE TABLE d ( m_id NUMBER );
  CREATE INDEX idx_d ON d ( m_id );

CREATE TYPE t AS TABLE OF NUMBER;

EXEC dbms_stats.set_table_stats ( sys_context( 'userenv', 'current_schema' ),
                                  'M', numrows =&gt; 1000 );
EXEC dbms_stats.set_table_stats ( sys_context( 'userenv', 'current_schema' ),
                                  'D', numrows =&gt; 10000 );
Hmmm.... this is an interesting one. The Oracle optimizer is a bit of a black box as far as mere mortals like myself are concerned... Jonathan Lewis wrote a 536 page book on the topic and that's just the **fundamentals* (and it ain't bedtime reading!).

Two questions.

a) Did you flush the caches before/after each query - maybe the first affects the second - i.e. the optimizer figures out after the first query that it's not worth using an index?

b) What happens if you bump up those numbers in 'M' and 'D'? Does there come a point where it switches back to an INDEX scan? I'm guessing that such a point will come (but see point a)

Just a couple of thoughts - I don't have an Oracle system to hand.