RE: ORA 28604 - table is too fragmented

From: <genegurevich_at_discoverfinancial.com>
Date: Mon, 21 May 2007 11:23:30 -0500
Message-ID: <OF54381914.B1936075-ON862572E2.0059ECCF-862572E2.005A0839@discoverfinancial.com>

Jacques,

The reason I am executing these commands is to force the bitmap index to complete. I am not sure how that
works internally with oracle. My goal is not specifically to minimize records_per_block, though

thank you

Gene Gurevich

                                                                           
             "Jacques                                                      
             Kilchoer"                                                     
             <Jacques.Kilchoer                                          To 
             @quest.com>               <genegurevich_at_discoverfinancial.com 
                                       >, <oracle-l_at_freelists.org>         
             05/18/2007 07:52                                           cc 
             PM                                                            
                                                                   Subject 
                                       RE: ORA 28604 - table is too        
                                       fragmented

This is pure guesswork on my part, but if you want to minimize the number of records per block, could you do it be re-creating the table with a high PCTFREE?
-----Message d'origine-----

De : oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] De la part de genegurevich_at_discoverfinancial.com Envoyé : vendredi, 18. mai 2007 14:43
À : oracle-l_at_freelists.org
Objet : RE: ORA 28604 - table is too fragmented

I did a little bit more testing and here is what I have now:

a partitioned table called T1 and an exchange table XCHG_T1
alter table XCHG_T1 minimize records_per_block fixes the problem with building the bitmap indices
this however changes the value of the hakan factor on XCHG_T1 table and now it is different from the value of hakan factor on the T1 table
now alter table t1 exchange partition with table xchg_t1 fails
alter table t1 minimize records_per_block resets the hakan factor on this table to the value different from the value on the XCHG_T1 table, therefore my exchange command still works.

I don't know whether there is a way to fix both of these two issues - bitmap index build and exchange.

thank you

Gene Gurevich

             "Bobak, Mark"
             <Mark.Bobak_at_il.pr
             oquest.com>                                                To
             Sent by:                  <genegurevich_at_discoverfinancial.com
             oracle-l-bounce_at_f         >, <oracle-l_at_freelists.org>
             reelists.org                                               cc

                                                                   Subject
             05/18/2007 12:33          RE: ORA 28604 - table is too
             PM                        fragmented


             Please respond to
             Mark.Bobak_at_il.pro
                 quest.com

Gene,

Not tested, and I'm not sure there's a quick, easy test case for this one, but, after moving data out of the problem table, you may try this: 1.) Figure how many rows per block you want. 2.) Insert that number of rows into the table. insert into problem_table select * from temp_table where rownum <= number_of_desired_rows_per_block; 3.) Verify that all those rows are in the same block. 4.) alter table problem_table minimize records_per_block; --This sets the Hakan factor, a limit on the max. number of rows/block the table will ever have.
5.) delete from problem_table; --don't truncate, that will reset the Hakan factor.
6.) insert into problem_table select * from temp_table;

-Mark
--

Mark J. Bobak
Senior Oracle Architect
ProQuest/CSA

"There are 10 types of people in the world: Those who understand binary, and those who don't."

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of genegurevich_at_discoverfinancial.com
Sent: Friday, May 18, 2007 12:03 PM
To: oracle-l_at_freelists.org
Subject: ORA 28604 - table is too fragmented

Hi all:

I am running oracle 9.2.0.4 and am getting the following error when building bitmap indices:

ORA-28604: TABLE TOO FRAGMENTED TO BUILD BITMAP INDEX (25206487,40,40) I have found the note 119674 and applied the first solution that was recommended - rebuild the table through export/import - I have copied the data from the table into another table, dropped and recreate my table and inserted the data back. After that I tried to rebuild the indices and received the same error. I looked at the second solution and it looks like a reduced version of the first one.

I have temporarily rebuilt all bitmap indices as non-bitmap to allow the user testing to go on, but I would still like to have the bitmap indices instead. Does anyone have a better solution for this?

thank you

Gene Gurevich

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Mon May 21 2007 - 11:23:30 CDT