Re: DELETE statement performance problem

From: Tanya <tanya8g_at_yahoo.com>
Date: 22 Feb 2002 08:05:49 -0800
Message-ID: <1e0a87eb.0202220805.16036450@posting.google.com>

I analyze this table (i do that constantly) with COMPUTE STATISTICS and I do analyze index too.

explain plan shows me that it picks up the PK :

explain plan for
delete from address
where address_id = 12345

Plan Table

---

| Operation                 |  Name                    |  Rows |

Bytes| Cost | Pstart| Pstop |

---

| DELETE STATEMENT          |                          |     1 |    5
|      2 |       |       |
|  DELETE                   |ADDRESS                   |       |     
|        |       |       |
|   INDEX UNIQUE SCAN       |PK_ADDRESS                |     1 |    5
|      2 |       |       |
------------------------------------------------------------------------------------------------

and I don't use sqlplus set timing.
I use SQL-Navigator and it shows processing time of a statement. In sqlplus it takes the same time thou ...

Thanks,
Tanya.

"Keith Boulton" <kboulton_at_ntlworld.com> wrote in message news:<pvfd8.5032$H43.609153_at_news11-gui.server.ntli.net>...
> Deletes can generate a great deal more rollback activity than updates.
>
> Have you checked the access path. If you analyzed the table before it was
> populated, you may find you're doing full table scans. Your figures is
> suspiciously close to my rule of thumb of about 2.5 seconds per million
> records.
>
> I would expect a single row delete on a table this size to take less time
> than is reported by sqlplus set timing on e.g.
>
> SQL> delete from fred where id = 6000000;
>
> 1 row deleted.
>
> Commit complete.
> Elapsed: 00:00:00.00
>
> Interestingly, with 9i, I could not re-produce the problem although I have
> encountered it a few years ago with an earlier version.
>
> In any case, try re-analyzing the table.
> Received on Fri Feb 22 2002 - 10:05:49 CST