Discussion :

[genio]

Bonjour ...
Je ne comprends pas un truc ... j'ai le message suivant
*** !!! ERREUR D EXECUTION !!! SQLCODE:-6502 ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "TATA.PKG_TOTO", line 355
ORA-01632: max # extents (256) reached in index TDBFI.IXB1_EDBFI_REJ

Comment se peut-il que je tombe enb 'MAX EXTENTS' alors que le package TOTO effectue des delete de masse ...

Bizarre non !

PS : L'index incriminé est un bitmap Index...

[LeoAnderson]

Merci d'éditer votre message pour utiliser les balises de citation [ quote ] ou de code [ code ] en lieu et place des effets gras/couleurs/etc... ;-)

[lafouine]

Comment se peut-il que je tombe enb 'MAX EXTENTS' alors que le package TOTO effectue des delete de masse ...

Bizarre non !

PS : L'index incriminé est un bitmap Index...

Ce n'est pas du tout bizarre, c'est meme tout a fait normal
Pour ta culture, cette article de Metalink expliquant ton pb :

Understanding Bitmap Indexes Growth while Performing DML operations on the Table
Note:260330.1

PURPOSE
-------

To analyze the growth of Bitmap index while performing DML operations on the
table.


SCOPE & APPLICATION
-------------------

DBAs who want to analyze the unexpected growth of bitmap indexes.


Bitmap Indexes Growth with DML operations on table
-----------------------------------------------------

While performing DML operations (INSERT / UPDATE or even DELETE) on a column of
a table having a bitmap index, you find an unexpected growth in the bitmap index
size. After the the update is complete, rebuilding the index reduces the size.

The growth of the bitmap with DML operations is expected behaviour.

Below is a small testcase to illustrate this issue :-

+ Create table bitmap_test

create table bitmap_test(a number, b varchar2(1));

+ Insert 1000000 rows in the table (with column b containig only 2 distinct
values Y and N).

-- Insert 1000000 rows into the table
DECLARE
2 x number :=0;
3 BEGIN
4 for i in 1..1000000 LOOP
5 if mod(i,2) = 0 then
6 insert into bitmap_test values (i,'Y');
7 else
8 insert into bitmap_test values (i,'N');
9 end if;
10 x :=x +1;
11 if x > 10000 then
12 commit;
13 x :=0;
14 end if;
15 End Loop;
16 commit;
17 END;
18 /

PL/SQL procedure successfully completed.


+ Create a bitmap index on the column b

CREATE BITMAP INDEX bitmap_test_b1 ON bitmap_test(b);

+ Check the size of the bitmap index

SELECT bytes, blocks
FROM dba_segments
WHERE segment_name='BITMAP_TEST_B1';

BYTES BLOCKS
--------- ----------
393216 48

Size of Bitmap index = 384 KB

+ The procedure updates the column 'b' of the table bitmap_text. This will
cause the bitmap index to grow (it may go upto 300MB or more)

Please make a note that we are updating 20000 rows with a commit interval 5000.

DECLARE
2 x number :=0;
3 p_a number;
4 p_b varchar2(1);
5 cursor c1 is select * from bitmap_test where a<20000;
6 BEGIN
7 open c1;
8 LOOP
9 fetch c1 into p_a,p_b;
10 exit when c1%notfound;
11 if p_b='Y' then
12 update bitmap_test set b='N' where a=p_a;
13 else
14 update bitmap_test set b='Y' where a=p_a;
15 end if;
16 x :=x +1;
17 if x > 5000 then
18 commit;
19 x :=0;
20 end if;
21 End Loop;
22 commit;
23 END;
24 /


Now check the size of bitmap index after update of the table

SELECT bytes, blocks
FROM dba_segments
WHERE segment_name='BITMAP_TEST_B1';

BYTES BLOCKS
---------- ----------
335544320 40960

Size of Bitmap index increased from 384KB to 320 MB

+ The growth of the bitmap can be restricted by the commit interval (as the
commit will decrease, the size of bitmap index growth will also decrease)

+ The above test case clarifies the growth of bitmap

+ If you rebuild the bitmap index, the size will decrease.

ALTER INDEX bitmap_test_b1 REBUILD;

After rebuild the size again shrinks back to normal

SELECT bytes, blocks
FROM dba_segments
WHERE segment_name='BITMAP_TEST_B1';

BYTES BLOCKS
---------- ----------
393216 48


+ Now we will summarize the reason for such a growth (Please remember that
this is an expected behaviour)

The problem that you are seeing is that the bitmap index is filling with index
records marked as deleted and we are not reclaiming the space.

Workarounds :

1) A workaround for this problem would be to commit after every update.
Committing will enable index logic to reclaim the wasted space created by
these deleted records.

2) The best workaround is still to rewrite your application to use bulk dml.
This may be done by creating a table to store up all the changes you intend
to make. This table should enable you to code a bulk dml statement that applies
all the changes. Since bitmap index dml is specially optimized for bulk dml
this would probably be the best mechanism to rapidly apply many changes to a
bitmap indexed table.

3) One workaround is to drop the index, do the updates and then again recreate
the index

4) Last workaround is to set the Bitmap to UNUSED.

+ ALTER SESSION SET SKIP_UNUSABLE_INDEXES=TRUE;

+ ALTER INDEX <bitmap_index_name> UNUSABLE; -> It will set the status of
bitmap to unusable.

+ Then perform the insert / update operation on the table.

+ After the insert is over ... ALTER SESSION SET SKIP_UNUSABLE_INDEXES=FALSE;

+ Then last step to change the status of bitmap index to valid ->
ALTER INDEX <bimap_index_name> REBUILD;

Unfortunately, this is the behavior of bitmap indexes. Single row dml operations
perform the maintenance to the bitmap and the implementation of the bitmap index
forces the index layer to delete/insert the row.

[genio]

2) The best workaround is still to rewrite your application to use bulk dml.
This may be done by creating a table to store up all the changes you intend to make. This table should enable you to code a bulk dml statement that applies all the changes. Since bitmap index dml is specially optimized for bulk dml this would probably be the best mechanism to rapidly apply many changes to a bitmap indexed table.

BULK DML ?

Merci en tout cas pour ta réponse...

PS : Dans quel sens se rapporte le mot 'WORKAROUND' (Aborder, exprimer, parvenir ?)

[ducho]

bonsoir,

on en apprend tous les jours avec oracle ...

workaround : contournement ( ou contourner)

dire que c' est normal ce cas, c' est du oracle tout craché ...

cdlt