| 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 -> Why does index degenerate after many deletes and inserts
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
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
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
sop_id
, sop_type
, par_id
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
Received on Tue Feb 08 2005 - 02:53:20 CST
 |
 |