-
Index fragment question
Hello,
I run the following to find tables/indexes fragmentation and I get the following output. It shows the Max extents and # extents >100. How do I interpret that, do I have to rebuild indexes or what is the correct way, how much should the max extents / #extents be usually? how to go about fixing this ? I hope you can provide inputs .
select a.owner "Owner",
segment_name "Name",
segment_type "Type",
bytes/(1024*1024) "Size (MB)",
max_extents "Max Extents",
extents "# Extents"
from dba_segments a
where segment_type in ('TABLE','INDEX')
and a.owner not in ('SYS','SYSTEM','OUTLN','MDSYS','ORDSYS','CSMIG','AURORA$JIS$UTILITY$','DBSNMP')
and extents > 100
and rownum <= 20
order by 5 desc
Owner Name Size(MB) Max Extents #Extents
------------------------------ ---------------------------------------------------------
QP QP_DEBUG_REQ_LDETS_N2 9.53125 2147483645 243
CZ CZ_CONFIG_ATTRIBUTES 165.585938 2147483645 224
CZ CZ_CONFIG_ITEMS_PK 900.039063 505 181
CZ CZ_CONFIG_INPUTS_PK 530.039063 505 107
-
how on earth has that query got anything to do with fragmentation?
it just lists the number of extents
-
Yes, so, what is the ideal number to arrive at. what should the number be ?
if according to the query and extents > 100, is that a problem and they all should be < 100 ? how to detrmine the number ?
-
hundreds of thousands - so forget about it
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|