Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: delete statement performance
On Mon, 13 Dec 1999 11:45:54 -0700, "Larry Pettit"
<larry.pettit_at_ps.net> wrote:
>We're running Oracle 8.1.5 on NT. When I delete a single row from the
>largest table which has 1.78 million rows (9 indexes and 11 fk) it takes 11
>seconds.
>
>Does anyone have any suggestions on how to speed it up? Are there any hints
>that would help other than (parrallel).
>
>Explain plan shows the following;
>
> SELECT STATEMENT Cost =
> DELETE HIST_DATA
> TABLE ACCESS BY INDEX ROWID HIST_DATA
> INDEX UNIQUE SCAN PK_HIST_DATA
Are you sure this is the actual plan used? 11 seconds sounds about the
right time for a full table scan of that many rows. Is the primary key
column a varchar2 containing a number value with the deletion being
passed a number i.e. delete from <table> where <column> = 1234. This
will cause an implicit to_number conversion on the column, resulting
in a full table scan which is not reflected in the explain plan.
Received on Wed Dec 15 1999 - 06:21:40 CST