Re: delete statement performance

A copy of this was sent to "Larry Pettit" <larry.pettit_at_ps.net> (if that email address didn't require changing) On Mon, 13 Dec 1999 11:45:54 -0700, you 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.
>

• triggers (are there any delete triggers). If so, what do they do?
• is this a parent table to other tables (are there fkeys to IT). If so, are those foreign keys indexed or do you force a full scan by us of that child table?
• have you used tkprof, it shows other things like cpu vs elapsed, blocks read (physical and logical IOs) and so on.

>Does anyone have any suggestions on how to speed it up? Are there any hints
>that would help other than (parrallel).

parallel will not help an INDEX UNIQUE SCAN go faster.

>
>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
>0
>
>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.
>
>
>
>
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Dec 13 1999 - 12:55:45 CST