SQL> DELETE FROM nc_params WHERE (object_id, attr_id)
  2  IN (SELECT object_id, attr_id FROM TABLE(getcoll));

0 rows deleted.

Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE (Cost=91860251 Card=1 Byte
          s=29)

   1    0   DELETE OF 'NC_PARAMS'
   2    1     NESTED LOOPS (SEMI) (Cost=91860251 Card=1 Bytes=29)
   3    2       TABLE ACCESS (FULL) OF 'NC_PARAMS' (Cost=10306 Card=83
          49995 Bytes=242149855)

   4    2       COLLECTION ITERATOR (PICKLER FETCH) OF 'GETCOLL'

SQL> DELETE /*+ INDEX(nc_params xif12nc_params)*/ FROM nc_params WHERE (object_id, attr_id)
  2  IN (SELECT object_id, attr_id FROM TABLE(getcoll));

0 rows deleted.

Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE (Cost=94784683 Card=1 Byte
          s=29)

   1    0   DELETE OF 'NC_PARAMS'
   2    1     NESTED LOOPS (SEMI) (Cost=94784683 Card=1 Bytes=29)
   3    2       INDEX (FULL SCAN) OF 'XIF12NC_PARAMS' (NON-UNIQUE) (Co
          st=32663 Card=8349995 Bytes=242149855)

   4    2       COLLECTION ITERATOR (PICKLER FETCH) OF 'GETCOLL'

SQL> DELETE /*+ RULE*/ FROM nc_params WHERE (object_id, attr_id)
  2  IN (SELECT object_id, attr_id FROM TABLE(getcoll));

0 rows deleted.

Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=HINT: RULE
   1    0   DELETE OF 'NC_PARAMS'
   2    1     NESTED LOOPS
   3    2       VIEW OF 'VW_NSO_1'
   4    3         SORT (UNIQUE)
   5    4           COLLECTION ITERATOR (PICKLER FETCH) OF 'GETCOLL'
   6    2       INDEX (RANGE SCAN) OF 'XIF12NC_PARAMS' (NON-UNIQUE)

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE    10.1.0.2.0      Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

SQL> show parameter optimizer

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.1.0
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      CHOOSE

SQL> desc bin_idx_tab;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 A                                                  NUMBER
 B                                                  VARCHAR2(2)

SQL> create type bin_obj as object(a number, b varchar2(2));
  2  /

Type created.

SQL> create type bin_obj_tab is table of bin_obj;
  2  /

Type created.

SQL> create package bin_pkg
  2  is
  3    objs bin_obj_tab := bin_obj_tab();
  4    function get_data return bin_obj_tab;
  5  end;
  6  /

Package created.

SQL> create package body bin_pkg
  2  is 
  3    function get_data return bin_obj_tab
  4    is
  5    begin
  6     return objs;
  7    end;
  8  end;
  9  /

Package body created.

SQL> exec bin_pkg.objs.extend(1); bin_pkg.objs(1) := bin_obj(1,'C');

PL/SQL procedure successfully completed.

SQL> select * from table(bin_pkg.get_data);

         A B
---------- --
         1 C

SQL> create index b01 on bin_idx_tab (a,b);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'bin_idx_tab', cascade=>true);

PL/SQL procedure successfully completed.

SQL>set autotrace traceonly expl
SQL> select a.* from bin_idx_tab a, table(bin_pkg.get_data) b
  2  where a.a=b.a and a.b = b.b
  3  /

1000 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=31 Card=8168908 Byte
          s=57182356)

   1    0   HASH JOIN (Cost=31 Card=8168908 Bytes=57182356)
   2    1     COLLECTION ITERATOR (PICKLER FETCH) OF 'GET_DATA'
   3    1     INDEX (FAST FULL SCAN) OF 'B01' (INDEX) (Cost=6 Card=900
          1 Bytes=45005)

SQL> select /*+RULE*/ a.* from bin_idx_tab a, table(bin_pkg.get_data) b
  2  where a.a=b.a and a.b = b.b
  3  /

1000 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   NESTED LOOPS
   2    1     COLLECTION ITERATOR (PICKLER FETCH) OF 'GET_DATA'
   3    1     INDEX (RANGE SCAN) OF 'B01' (INDEX)

SQL> alter session set optimizer_mode = rule;

Session altered.

SQL> select a.* from bin_idx_tab a, table(bin_pkg.get_data) b
  2  where a.a=b.a and a.b = b.b
  3  /

1000 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   NESTED LOOPS
   2    1     COLLECTION ITERATOR (PICKLER FETCH) OF 'GET_DATA'
   3    1     INDEX (RANGE SCAN) OF 'B01' (INDEX)