| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Why does index degenerate after many deletes and inserts
There are various ways in which you can make indexes degenerate. One of the easier options is to do the sort of thing you describe - delete rows and insert "exact replacements" in the same transaction. The problem is that Oracle will not release a row-entry, even though you could theoretically re-use it, until you issue a commit. This is probably to make it easier to deal with read-consistency and rollback issues - and in indexes the level of protection offered to the deleted data is quite aggressive. In fact, if you check the table, you will find that the same degeneration is happening in the table but to a much less significant degree.
It is possible that you could reduce the damage that this activity is doing by reviewing the PCTFREE and PCTUSED on the TABLE - you may find that the replacement rows will be able to fit into the same table block if you set these parameter suitably, and re-use of the same table rowids may allow Oracle to reuse the same leaf blocks for subsequent passes of the delete/insert cycle.
If you check Tom's comments about rebuilds, you will find that whilst he warns quite strongly against automatic (and thoughtless) rebuilds of indexes according to the dictats of some generic statistic, he does point out that there are always special cases where the nature of the application requires you to consider rebuilding - and allows you to work out why (and when) it is necessary.
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated Jan 21st 2005 "Martin Drautzburg" <martin.drautzburg_at_web.de> wrote in message news:348229d8.0502080053.22b6a97a_at_posting.google.com...Received on Tue Feb 08 2005 - 03:19:53 CST
> This is on Oracle 9i on Windows
>
> There is a partitioned table with one local index. For a certain key
> there are initially zero rows. I insert 24 rows. Then I delete the 24
> rows and insert 48 rows all for that same key and all in the same
> transaction. If I do this over and over again (deleting all rows for a
> given key and insert 24 more rows) the delete operation becomes slower
> and slower. Selects that use that index slow down by several orders of
> magnitude too once that key has apx. 2000 rows.
>
>
> Analyzing/validate structure the index partition then shows
>
> Height|blocks|name |partition_Name|lf_Rows|lf_Blks
> --------------------------------------------------------
> 3 |32768 |IT_ICCF_SOP|PARTITION_G_1 |202179 |31815
>
> |lf_Rows_Len|lf_Blk_Len|br_Rows|br_Blks|br_Rows_Len|br_Blk_Len
> --------------------------------------------------------------
> |4243666 |8000 |31814 |164 |729150 |8032
>
> |del_Lf_Rows|del_Lf_Rows_Len|distinct_Keys|most_Repeated_Key
> ------------------------------------------------------------
> |178611 |3749170 |60 |10854
>
> |btree_Space|used_Space|pct_Used|rows_Per_Key|blks_Gets_Per_Access
> ------------------------------------------------------------------
> |255837248 |4972816 |2 |3369,65 |1688,325
>
> |pre_Rows|pre_Rows_Len|opt_Cmpr_Count|opt_Cmpr_Pctsave|
> -------------------------------------------------------
> |0 |0 |3 |40 |
>
>
> When I rebuild the index things are back to normal and selects and
> deletes are fast again.
>
> Height|blocks|name |partition_Name|lf_Rows|lf_Blks
> --------------------------------------------------------
> 2 |128 |IT_ICCF_SOP|PARTITION_G_1 |23568 |69
>
> |lf_Rows_Len|lf_Blk_Len|br_Rows|br_Blks|br_Rows_Len|br_Blk_Len
> --------------------------------------------------------------
> |494496 |8000 |68 |1 |1562 |8032
>
> |del_Lf_Rows|del_Lf_Rows_Len|distinct_Keys|most_Repeated_Key
> ------------------------------------------------------------
> |0 |0 |48 |816
>
> |btree_Space|used_Space|pct_Used|rows_Per_Key|blks_Gets_Per_Access
> ------------------------------------------------------------------
> |560032 |496058 |89 |491 |248
>
> |pre_Rows|pre_Rows_Len|opt_Cmpr_Count|opt_Cmpr_Pctsave|
> -------------------------------------------------------
> |0 |0 |3 |47 |
>
>
>
> Can someone explain whats going on here and maybe show a way to
> prevent degeneration by setting some clever paramaters ?
>
> I can see that rebuilding the index "fixes" the problem, but I am
> confused because Tom Kyte opts against "preventive rebuilding" of
> indexes.
>
> Martin Drautzburg
>
> __________________________________________________________________________
>
> In case it matters here is the definition of the table and index
>
> 0:spm_at_spmdtz> pull is_code_composed_from
> CREATE TABLE is_code_composed_from
> (
> par_id INTEGER
> , sop_id INTEGER
> , sop_type VARCHAR2 (1)
> , sortcode VARCHAR2 (40)
> , scf_id INTEGER
> , mal_id INTEGER
> , steps INTEGER
> )
> ORGANIZATION HEAP
> NOMONITORING
> PARTITION BY RANGE
> (
> sop_type
> , par_id
> )
> (
> PARTITION partition_a_1 VALUES LESS THAN
> (
> 'C', 1
> )
> , PARTITION partition_c_1 VALUES LESS THAN
> (
> 'G', 1
> )
> , PARTITION partition_g_1 VALUES LESS THAN
> (
> 'I', 1
> )
> , PARTITION partition_i_1 VALUES LESS THAN
> (
> 'P', 1
> )
> , PARTITION partition_p_1 VALUES LESS THAN
> (
> 'S', 1
> )
> , PARTITION partition_s_1 VALUES LESS THAN
> (
> 'T', 1
> )
> );
>
>
> CREATE INDEX it_iccf_sop ON is_code_composed_from
> (
> sop_id
> , sop_type
> , par_id
> )
> LOCAL
> (
> PARTITION partition_a_1
> , PARTITION partition_c_1
> , PARTITION partition_g_1
> , PARTITION partition_i_1
> , PARTITION partition_p_1
> , PARTITION partition_s_1
> );
>
> ALTER TABLE is_code_composed_from ADD CONSTRAINT
> fk_iccf_composed_product FOREIGN KEY
> (
> sop_id
> , sop_type
> , par_id
> )
> REFERENCES spm.sorting_product
> (
> sop_id
> , sop_type
> , par_id
> )
> ON DELETE CASCADE
> NOT DEFERRABLE
> INITIALLY IMMEDIATE
> ENABLE NOVALIDATE
> ;
>
> ALTER TABLE is_code_composed_from ADD CONSTRAINT fk_iccf_mal FOREIGN
> KEY
> (
> mal_id
> , par_id
> )
> REFERENCES spm.mail_attribute_line
> (
> mal_id
> , par_id
> )
> ON DELETE CASCADE
> NOT DEFERRABLE
> INITIALLY IMMEDIATE
> ENABLE NOVALIDATE
> ;
> show errors
 |
 |