Re: bad SQL, naughty SQL....

Jason Salter <jason_at_seahorseNOSPAM.demon.co.uk> wrote in message news:37ff43eb.755770_at_news.demon.co.uk...
>
> I've been looking into performance problems with a purchase orders
> package that my company uses.
>
> They run an end-of-period program that has taken on average 24-30
> hours to complete. During my investigation I found the usual stuff;
> redo logs too small thus archiving too often, block buffers too small,
> hot spots on discs etc etc. I managed to fix all of these and halved
> the time it took to run the EOP. But, twelve hours still seems a lot.
> So I started looking at what SQL was being performed by the EOP job.
>
> I've managed to track down the most offensive piece of SQL I've ever
> seen. Consider this :-
>
> Table A has 110,000 rows
> Table B has 120,000 rows
>
> To clear down purchase orders over a certain age this piece of SQL is
> used :-
>
> delete from tablea
> where rowid NOT in ( select rowid from tableb );

[snip]

What do you want to do? Your SQL is meaningless.

1. The ROWIDs are not logically relevant with the data in the tables. You should use primary keys instead of ROWID.

2 .The ROWIDs of two rows in different tables are the same ONLY IF the tables are in the same cluster.

3. Since the ROWIDs in two tables are completely different if they are

    not in the same cluster, your SQL may lead to error in some versions.     For example, in Oracle 8.0.4:

SQLWKS> delete from table1 where rowid in (select rowid from table2); ORA-01410: invalid ROWID

4. For performance issue, you should use "not exists" or outer join, instead of "not in".

delete from tablea a
  where not exists
    (select * from tableb where primary_key=a.primary_key); Received on Sun Oct 10 1999 - 12:07:54 CDT