Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: help: query tuning
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