Re: Update on unreasoabley slow SQL statement.

In <stanbD0089u.I04_at_netcom.com> stanb_at_netcom.com (Stan Brown) writes:

>
> I thought you might be interested in an update on this problem.
>(Also I would be interested in anyones comments on this). As you might
>recall we have a single SQL statement that takes 2 1/2 days to complete!
>
> Well here is the simple test case, It consists of two SQL statements
>and their respective run times.
>
> Environment -
>
> HP 9000/712
> Main Table with 14 subsidiry 1 to 1 tables
> and 2 one to many tables
>
> Approx 200,000 entries.
>
> Method 1.
>
> Temporary table containg just one row
>
> MIN
> ----------------
> EAKXY6
>
> Statement:
>
> DELETE from main_table where min in
> (SELECT MIN FROM tmp_table);
>
> Method 2.
>
> DELETE from main_table where min = 'EAKXY6'
>
> Results.
>
> Method 1 time 3 Minutes
> Methid 2 time 3 seconds.
>
> Anyone have any clue as to why the dramitic diference ?
>
>Stan

I assume you have an index on MAIN.MIN. The second statement will use the index to obtain the rowid and delete the row.

The first statement will have to do a full table scan, as well as the additional work for the subselect; Oracle doesn't use indexes to satisfy the IN operation (if memory serves me :-).

However, 3 minutes seems a bit long.

You should read about TKPROF and EXPLAIN PLAN so that you can analyze your statements.

You might also try using the rule based optimizer instead of the cost based optimizer (CBO). Or try doing a full ANALYZE TABLE on both tables and then trying the CBO.

-JJ

 Jeffrey Jacobs & Associates
 Oracle CASE Consulting and Training
 951-2 Old Country Road, Suite 119
 Belmont, CA 94002
 Voice: 415-571-7092
 FAX: 800-665-1379
 CompuServe: 76702,456
 Internet: jjacobs_at_well.com
 Internet: jmjacobs_at_ix.netcom.com     Received on Tue Nov 29 1994 - 17:50:13 CET