Re: Force execution path for a delete

Jaap W. van Dijk <j.w.vandijk_at_donotspam.kpn.com> wrote in message news:<ftt8rv0378jjf0925kn9hk4mvhjp5i96t0_at_4ax.com>...
> 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.

Several ideas
- If you are using dbms_stats, manipulate the statistics so they are geared at the execution path above
- (Easier) Set up a stored outline for this statement. As far as I can see the statement doesn't change, so when you have an outline, it will use that execution path.
- You might consider using the PUSH_SUBQ hint in the subquery.

Regards

Sybrand Bakker
Senior Oracle DBA Received on Fri Nov 14 2003 - 07:01:41 CST