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: help: query tuning

Re: help: query tuning

From: John Higgins <JH33378_at_deere.com>
Date: Fri, 09 Jul 1999 21:55:10 -0500
Message-ID: <3786B60E.4844CDB4@deere.com>


Check again. Its even worse!

Table a is being full scanned. Every row is retrieved. Then table b is full scanned to see if that row matches. The scan of table b is repeated for each and every row of table a.

Without indexes, how else could this be done?

How about using a sort-merge join of the two tables to obtain the rowid's to delete? A sort-merge join should take about 1 minute.

ewong74_at_netscape.net wrote:

> Hi,
>
> I have the following query which take me 2 hour to finish:
>
> delete from table_a a where term_id in
> (select term_id from table_b b where a.code = b.code);
>
> table_a has 300k rows, table_b has 100k rows.
> Both tables have no indexes and i cannot create any indexes on them.
> When I do an explain plan, it looks like oracle are doing full table
> scan for both tables.
>
> Please help. Thanx.
>
> Ed
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Fri Jul 09 1999 - 21:55:10 CDT

Original text of this message

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