Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Force execution path for a delete

Re: Force execution path for a delete

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 14 Nov 2003 09:19:38 -0000
Message-ID: <bp26kn$6ev$1$8302bc10@news.demon.co.uk>

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...

> 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 - 03:19:38 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US