Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Force execution path for a delete
Try
/*+ unnest */
in the subquery.
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html ____Belgium__November (EOUG event - "Troubleshooting") ____UK_______December (UKOUG conference - "CBO") Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html ____UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html "Jaap W. van Dijk" <j.w.vandijk_at_donotspam.kpn.com> wrote in message news:ftt8rv0378jjf0925kn9hk4mvhjp5i96t0_at_4ax.com...Received on Fri Nov 14 2003 - 03:19:38 CST
> 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.