Fragmented table list [message #458236] |
Fri, 28 May 2010 01:07 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
lalitm_2003
Messages: 86 Registered: May 2005 Location: delhi
|
Member |
|
|
Hi experts,
Last week, i was facing some problem with a report that is using 4 tables and doing some joins. On debugging, i found that one of the table was fragmented and that causes the performance issue for that report.
I came to know this when i try to build bitmap index on one of the column of that fragmented table. That happens with me early on too.
I resolved the issue by moving the table to another tablespace.
1) Can i find out the list of the tables that are fragmented in the schema using any sql.
2) Why bitmap indexes are throwing fragmented table error while the normal index on the same column can be created without any error.
Great thanx in advance for your input and knowledge sharing.
Regards
Lalit
|
|
|
|
Re: Fragmented table list [message #458244 is a reply to message #458240] |
Fri, 28 May 2010 01:39 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
lalitm_2003
Messages: 86 Registered: May 2005 Location: delhi
|
Member |
|
|
Thanks Michel for the prompt reply,
I got the below error when i try to create bitmap index.
CREATE BITMAP INDEX EP75.psAvoucher_line ON EP75.PS_VOUCHER_LINE(Business_unit);
ORA-28604: table too fragmented to build bitmap index (37782412,24,24)
I moved the table to some other tablespace and then i was able to create the bitmap index on the same column.
Meanwhile i was able to create the table normal index before moving the table to another tablespace.
Regards
Lalit
|
|
|
|
Re: Fragmented table list [message #458256 is a reply to message #458236] |
Fri, 28 May 2010 04:39 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
lalitm_2003
Messages: 86 Registered: May 2005 Location: delhi
|
Member |
|
|
Michel,
I already got this message/information on google. But my question still there.
Is there any query which generate list of the tables that are having this issue?
Because until unless we try to create bitmap index, we are not able to see any issue/information about this fragmentation.
Regards
Lalit
|
|
|
|
Re: Fragmented table list [message #458290 is a reply to message #458244] |
Fri, 28 May 2010 08:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
lalitm_2003 wrote on Fri, 28 May 2010 02:39Thanks Michel for the prompt reply,
I got the below error when i try to create bitmap index.
CREATE BITMAP INDEX EP75.psAvoucher_line ON EP75.PS_VOUCHER_LINE(Business_unit);
ORA-28604: table too fragmented to build bitmap index (37782412,24,24)
Wow, I'll have to admit that in all my years using Oracle I have never seen this error. And now that I just said that I am sure it will happen to me today.
|
|
|
Re: Fragmented table list [message #458293 is a reply to message #458290] |
Fri, 28 May 2010 08:40 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
That is having more than 24 different rows (regarding the index key) (in the OP's example) in a single block is quite rare.
Regards
Michel
|
|
|