Re: Very long query

Zachary,

Consider using an index on your table to improve your delete performance:

>I have a problem deleting stale records from a large table. If I try to
>delete a day's worth of records (every night: delete from ... where ... <
>sysdate-7;), the query runs forever.

Put an index on the column used in the WHERE clause (report_date, I'm guessing).

>If, on the other hand, I limit my deletion to only, say a 1000 records:
> delete... where rownum < 1001;
>I have no problem with this one.

That's because you told Oracle to delete the first 1000 records it finds. There are no other conditions, so the first 1000 rows are deleted. You're comparing apples to oranges.

>So, I tried to create a PL/SQL procedure, like this:

Your PL/SQL example combines the rownum part plus the WHERE on report_date, so it does a full table scan; the performance should be the same as the first delete, or slightly slower since it does more interim commits.

>Unfortunately, this also runs forever.

It runs forever because Oracle has to check every record in the table to determine
which rows to delete.

>I have fairly large rollback segments (four of them, about 136 MB each).

That's unrelated to your performance problem. Once your delete is done, you can drop the index until you need to do that kind of delete again. Deletes tend to leave empty space in indexes that Oracle can't reuse, so you should periodically rebuild the index on report_date (and the others on the table, for that matter).

hth.
Kevin Loney
http://www.kevinloney.com Received on Wed Dec 09 1998 - 14:19:39 CST