delete statement performance

From: Larry Pettit <larry.pettit_at_ps.net>
Date: Mon, 13 Dec 1999 11:45:54 -0700
Message-ID: <c1b54.1217$q91.22356@news.uswest.net>

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

Analyze for the table;

BLOCKS    EMPTY_BLOCKS    AVG_ROW_LEN     CHAIN_CNT
     ---------                     ------------                    ---------
--                 ---------
     16900                           7404                              67

Analyze for the index;

NAME                              HEIGHT   LF_ROWS DEL_LF_ROWS DISTINCT_KEYS

MOST_REPEATED_KEY BTREE_SPACE USED_SPACE ROWS_PER_KEY BLKS_GETS_PER_ACCESS

------------------------------ --------- --------- ----------- -------------
 ----------------- ----------- ---------- ------------ --------------------
PK_HIST_DATA                          3   1781022           2       1781022
1    30696224   27512069            1                    4

Thanks for any help. Received on Mon Dec 13 1999 - 12:45:54 CST