RE: ORA 28604 - table is too fragmented

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Mon, 21 May 2007 13:54:18 -0700
Message-ID: <22EC50E60931494FA666A8CF8260C45B5D9D69@ALVMBXW05.prod.quest.corp>

Yes, I understood that, but if one way to allow the creation of the bitmap index is having less records per block (wasn't that the whole point of resetting the hakan factor?), you can accomplish that goal with pctfree.

-----Message d'origine-----

De : genegurevich_at_discoverfinancial.com [mailto:genegurevich_at_discoverfinancial.com] Envoyé : lundi, 21. mai 2007 09:24
À : Jacques Kilchoer
Cc : oracle-l_at_freelists.org
Objet : RE: ORA 28604 - table is too fragmented

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 - 15:54:18 CDT