Indexes on Nested Tables

This will the continuation of Earlier post. One big difference here is that in the nested table implementation, we could have created an index on the nested table's ID column, and the optimizer would have been able to use that. but that is not possible using Varray.

rajesh@10GR2> create type too as object(
  2     id number,
  3     dt date,
  4     data varchar2(20)
  5  );
  6  /

Type created.

Elapsed: 00:00:00.15
rajesh@10GR2> create type foo is table of too;
  2  /

Type created.

Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2> create table t(
  2  x number,
  3  y foo) nested table y store as t_y;

Table created.

Elapsed: 00:00:00.12
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> INSERT
  2  INTO t
  3  SELECT level,
  4    CAST( multiset
  5        (SELECT too(level,sysdate,rpad('*',20,'*'))
  6        FROM dual
  7          CONNECT BY level <= 1000
  8        ) AS foo)
  9  FROM dual
 10  CONNECT BY level <= 5;

5 rows created.

Elapsed: 00:00:00.09
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.06
rajesh@10GR2> create unique index t_ind on t_y(nested_table_id,id);

Index created.

Elapsed: 00:00:00.14
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly explain;
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> select t.x,t_tab.id
  2  from t, table(t.y) t_tab
  3  where t.x = 2
  4  and t_tab.id <= 5;
Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 1251723223

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     5 |   230 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |       |     5 |   230 |     6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T     |     1 |    23 |     5   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | T_IND |     5 |   115 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T"."X"=2)
   3 - access("T_TAB"."NESTED_TABLE_ID"="T"."SYS_NC0000200003$" AND
              "T_TAB"."ID"<=5)

rajesh@10GR2>