RE: Why does a simple delete takes 12 hours and longer ?

If there is a PK on docnum in ojs_main, you'd better refrase the delete-statement into something like :

Delete from ojs_main_multiple mult
where not exists ( select null from ojs_main main where main.docnum = mult.docnum ) ;

This way you'll use the index. In the way you stated it, the index is not used !

Greets,

Kirsten

> -----Original Message-----
> From: Andreas Jung [SMTP:ajung_at_sz-sb.de]
> Sent: Friday, July 28, 2000 10:06 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Why does a simple delete takes 12 hours and longer ?
>
>
> Yesterday evening I started the following delete:
>
> delete from ojs_main_multiple where docnum not in (select docnum from
> ojs_main);
>
> ojs_main contains about 250.000 rows and ojs_main_multiple about 300.000
> rows.
> docnum is a unique primary key of ojs_main. This morning after about 12
> hours
> the delete was still running. Any idea why this happens ? Should I
> believe
> in "In Oracle we trust "?
>
> (our env.: Oracle 8i EE/Solaris on a Sun E450 with 4 CPUs)
>
> Andreas
>
> --
> Author: Andreas Jung
> INET: ajung_at_sz-sb.de
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Fri Jul 28 2000 - 05:55:34 CDT