Force execution path for a delete

Hi,

I have a big table BIGTAB in an Oracle 8.1.7 database from which I want to delete rows with ID's that are in another table TMPIDTAB with this statement:

delete from BIGTAB
where ID in (select ID from TMPIDTAB);

There is an index BIGTAB_IDX on ID in BIGTAB.

For a small TMPIDTAB this is the explain plan:

DELETE STATEMENT
 DELETE bigtab
   NESTED LOOPS
    VIEW vw_nso_1

     SORT UNIQUE
      TABLE ACCESS FULL tmpidtab
    TABLE ACCESS BY INDEX ROWID bigtab
     INDEX RANGE SCAN bigtab_idx

But from a certain size of TMPIDTAB the execution path changes, giving rise to a much longer processing time. How do I force the execution path from the explain plan? I've tried all kinds of hints but to no avail. IMO it boils down to telling the optimizer is has to do a nested loop join with TMPIDTAB as the driving table, but how do you do that if one table is at the level of the delete statement itself while the other table is in a subselect?

Jaap. Received on Fri Nov 14 2003 - 01:01:35 CST