SEO

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_Le n|br_Blk_Len
--------------------------------------------------------------
|4243666 |8000 |31814 |164 |729150 |8032

|del_Lf_Rows|del_Lf_Rows_Len|distinct_Keys|most_Re peated_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_Pct save|
-------------------------------------------------------
|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_Le n|br_Blk_Len
--------------------------------------------------------------
|494496 |8000 |68 |1 |1562 |8032

|del_Lf_Rows|del_Lf_Rows_Len|distinct_Keys|most_Re peated_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_Pct save|
-------------------------------------------------------
|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@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

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@web.de> wrote in message
news:348229d8.0502080053.22b6a97a@posting.google.c om...
> 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_Le n|br_Blk_Len
> --------------------------------------------------------------
> |4243666 |8000 |31814 |164 |729150 |8032
>
> |del_Lf_Rows|del_Lf_Rows_Len|distinct_Keys|most_Re peated_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_Pct save|
> -------------------------------------------------------
> |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_Le n|br_Blk_Len
> --------------------------------------------------------------
> |494496 |8000 |68 |1 |1562 |8032
>
> |del_Lf_Rows|del_Lf_Rows_Len|distinct_Keys|most_Re peated_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_Pct save|
> -------------------------------------------------------
> |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@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