Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Tom Kyte

Thanks for the question, kathy.

Asked: March 12, 2002 - 5:38 pm UTC

Answered by: Tom Kyte - Last updated: January 23, 2013 - 1:00 am UTC

Category: Database - Version: 8.1.6

Viewed 10K+ times! This question is

Whilst you are here, check out some content from the AskTom team: Inline your subprograms - but only within same program unit

You Asked

Hi Tom,

i have a question about the index fragmentation, on the famous white paper "How to stop defragmenting and start living: the definitive world of fragmentation" by Bhaskar Himatsingka and Juan Loaiza, on section 4.2, they provide a script to tell if the index can reduce a blevel, the procedure is this:
1.) analyze index index_name validate structure
2.) select name NAME,
(br_rows_len*100)/(br_blk_len*br_blks) BRANCH_UTILIZATION,
((lf_rows_len - del_lf_rows_len)*100)/ (lf_blk_len*lf_blks) LEAF_UTILIZATI,
decode (sign(ceil(log(br_blk_len/(br_rows_len/br_rows),
lf_blk_len/((lf_rows_len - del_lf_rows_len)/(lf_rows - del_lf_rows)
+1 - height), -1,'YES','NO') CAN_REDUCE_LEVEL
from index_stats;

i have a couple of indexes, after run the script, "CAN_REDUCE_LEVEL" shows yes, after i did a "alter index xxx rebuild ...", the flag still shows yes, why the "height" didn't reduce after "rebuild" ?

and we said...

I think the operative word here is "CAN_REDUCE_LEVEL" -- it is CAN, not "will".

This query is (as they say) based on 'the formula existent in most database textbooks on how to calculate expected optimal height of the
tree based on the statistics'

EXPECTED optimal height
^^^^^^^^

It is a query to help you locate candidates, it does not assure you "you will lower the height".

My opinion -- 99.9% of all reorgs, rebuilds, etc are a total and utter waste of time and energy. We spend way way way too much time losing sleep over this non-event.

If you are going to spend time on this exercise -- make sure you come up with a way to MEASURE what you've just done in some quanitative fashion you can report to your mgmt (eg: these rebuilds I spend X hours a week doing save us from doing X IO's every day, or let us do Y more transactions then otherwise possible, or .....) No one, but no one, seems to do that (keep metrics). They just feel "it must be better". Who knows -- you may actually be DECREASING performance!! (you'll never know until you measure)



and you rated our response

  (27 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Excellent

March 12, 2002 - 7:16 pm UTC

Reviewer: pawan from USA

I think Tom has gone over this atleast 10 times and I totally agree with him. I used to awake at 2 AM reorganizing Indexes and never kept any metrices so am not sure whether it helped. My question though is if there exists with Tom a script/method to gather statistics on a regular basis ( before and after).

Thanks

Tom Kyte

Followup  

March 13, 2002 - 6:57 am UTC

things like statspack (IO's and waits), application logs (showing transactions processed, response times), tkprof reports are what you need.

LMT

March 13, 2002 - 1:36 am UTC

Reviewer: Dusan Valasek from Slovakia

"How
to stop defragmenting and start living: the definitive world of fragmentation" - there is the only answer: Start living in Locally Managed Tablespaces.


Index fragmented in LMT

March 05, 2004 - 6:45 am UTC

Reviewer: KJ from USA

Hi Tom

This is a DW database - 9.2.0.4

analyze index NON_TRADE_ACCOUNT_BALANCE_PK validate structure;

select trunc(100*(del_lf_rows/lf_rows)) pct_deleted, pct_used from index_stats - gave a 47%

It is a LMT - Automatic Allocation and Auto Segment Space management. Should we check for fragmentation even with LMT's? I see few other indexes with the same issue too.
Thanks

Tom Kyte

Followup  

March 05, 2004 - 8:37 am UTC

that is not fragmented.

that might be very very very very normal for that index.

rebuilding it might just result in increased redo
or
rebuilding it might result in reduced LIO's during the day



All this means is you apply inserts, updates and deletes against this index.


Try alter index iname coalesce; that might be all you need (an online operation).

Index fragmented in LMT

March 05, 2004 - 7:02 am UTC

Reviewer: KJ from USA

Hi Tom

This is a DW database - 9.2.0.4

analyze index NON_TRADE_ACCOUNT_BALANCE_PK validate structure;

select trunc(100*(del_lf_rows/lf_rows)) pct_deleted, pct_used from index_stats - gave a 47%

It is a LMT - Automatic Allocation and Auto Segment Space management. Should we check for fragmentation even with LMT's? I see few other indexes with the same issue too.
Thanks

height is 4

May 17, 2004 - 8:53 pm UTC

Reviewer: READER from US

validate index xxx
select * from index_stats reveals that the height is 4 .
What does this mean ?
root,branch,leaf ...
Is it bad ?

Thanks


Tom Kyte

Followup  

May 18, 2004 - 11:14 am UTC

root -> branch -> branch -> leaf

means it'll take 4lio's to get from a key value to a leaf node.

is it bad? insufficient data to comment. size of key? number of distinct vlaues of key? block size? ....

OEM Reorganization Features

May 18, 2004 - 11:38 am UTC

Reviewer: reader

With 9.2 OEM allows for online reorganization of tables
and indexes that include function-based and reverse-key
indexes. You can validate the structure of an object while
users are making changes to a table.

It will generate an Impact and Job Summary Report.

Haven't made use of this feature myself so can't offer any concrete evidence of it's usefulness and performance.


Tom Kyte

Followup  

May 18, 2004 - 6:16 pm UTC

well, the database has allowed for online stuff for a while, doesn't need a gui or anything.

but -- just because you see the number "4" doesn't mean *anything*

Height=4

May 18, 2004 - 7:52 pm UTC

Reviewer: Reader from US

select height,blocks,lf_rows,lf_blks,br_rows,distinct_keys from index_stats
HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS DISTINCT_KEYS
---------- ---------- ---------- ---------- ---------- -------------
4 158981 42478952 154898 154897 5666
Block size = 8k

Thanks


Tom Kyte

Followup  

May 19, 2004 - 7:46 am UTC


hows about showing the entire index_stats view -- but with 42million entries, it doesn't seem unreasonable yet.

May 19, 2004 - 9:05 pm UTC

Reviewer: READER from US

PRE_ROWS_LEN HEIGHT BLOCKS LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS PRE_ROWS

0 4 158981 42478952 154898 1061973800 7996 154897 847 4117920 8028 0 0 5666 90747 1245364124 1066091720 86 7497.16766678433 3753.08383339216 0


Thanks


Tom Kyte

Followup  

May 20, 2004 - 10:18 am UTC

you have no real "whitespace" (0 deleted leaf rows). You are 86% utilized (pretty packed).

It is what it is, the only real way to reduce the height would be to partition it into N smaller indexes.

but -- since each key retrieved 7k plus rows -- that really doesn't matter - so what if it takes 4lios to get to the leaf, if it is going to have to range scan for 7k rows after that. even reducing the height of the index by 1 or 2 would remove only 1 or 2 lios from the range scan (which on average is going to have 3753 LIOs anyway)

this looks fine.

Range Scan --LIO's

May 21, 2004 - 8:24 am UTC

Reviewer: Reader from US

Tom,

When it is doing a Range scan to find a particular Row Why will it do 3753 LIO's ?
Is it true that while doing a Range Scan Oracle reads only one block at a time.
Why can't it do multi-block reads at this point .

Thanks for clarifying .




Tom Kyte

Followup  

May 21, 2004 - 10:57 am UTC

3753.08383339216

because the estimated block gets per access said it would.

indexes are stored in blocks, blocks all over the place. Leaf blocks are not stored "next to eachother" (cannot be). to get from leaf block 1 to leaf block 2 -- you have to do an IO of another block somewhere else.

Also, there is the table access by index rowid to be considered as well (each entry on the index could be pointing to a different block in the table itself).

indexes on LMT

August 15, 2007 - 11:37 am UTC

Reviewer: Sudip Sarkar from Texas

Hi Tom,
My DBA says "Now that all data is on a SAN, the need for separation of data and index is no longer required from a hardware perspective.
And, with locally managed tablespaces with automatic extent sizing and automatic segment space management, fragmentation is supposed to be a thing of the past. "
Is this true that we dont need to create a separate tablespaces for Indexes anymore?

Thanks and Regards


Tom Kyte

Followup  

August 20, 2007 - 7:18 pm UTC

SAN - spam - nam - ugh

SAN has nothing to do with it
NAS has nothing to do with it


It was never true, never, that table data and index data needed to be separate. It was always a myth.

It had nothing to do with fragmentation either.

It has nothing to do with extents and how they are allocated.

It never did.


You always and forever should have used tablespaces to make YOUR LIFE EASIER, BETTER, MORE HAPPY.

Do whatever is right for you.


Use tablespaces to administer, book keep, organize. that is all.

Fragmented Index

April 02, 2009 - 1:41 am UTC

Reviewer: A reader

Hi Tom

We have a large table of about 100 millions records. It was determined that the ninety percent of the records of the table were not useful. The tables has a unique sid as its primary key/unique key. Now when I delete the table and its depended table the keys would like the below. There are large gaps in the unique key.

Before
Unique Key
1
2
3
4
.
.

After
Unique Key
1
7
19
35
.
.

Before Delete Statistics
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENESS COMPRESSION PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOGGING BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE LAST_ANALYZED DEGREE INSTANCES PARTITIONED TEMPORARY GENERATED SECONDARY BUFFER_POOL USER_STATS DURATION PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME PARAMETERS GLOBAL_STATS DOMIDX_STATUS DOMIDX_OPSTATUS FUNCIDX_STATUS JOIN_INDEX
ACC_CUST_START_END NORMAL COS ACC TABLE NONUNIQUE DISABLED USERS_IDX 2 255 134217728 1048576 1 2147483645 0 0 NO 3 506265 460494 1 58 26812095 VALID 97659470 19531894 3/28/2009 2:41 1 1 NO N N N DEFAULT NO YES NO
ACC_IDX1 NORMAL COS ACC TABLE NONUNIQUE DISABLED USERS_IDX 2 255 268435456 1048576 1 2147483645 0 0 NO 3 565090 16077089 1 3 60398500 VALID 97321855 19464371 3/28/2009 2:44 1 1 NO N N N DEFAULT NO YES NO
ACC_IDX2 NORMAL COS ACC TABLE NONUNIQUE DISABLED USERS_IDX 2 255 268435456 1048576 1 2147483645 0 0 NO 3 538750 7240761 1 12 87569530 VALID 97224330 19444866 3/28/2009 2:46 1 1 NO N N N DEFAULT NO YES NO
ACC_IDX3 NORMAL COS ACC TABLE UNIQUE DISABLED USERS_IDX 2 255 67108864 1048576 1 2147483645 0 0 NO 2 199720 97169825 1 1 1586450 VALID 97169825 19433965 3/28/2009 2:40 1 1 NO N N N DEFAULT NO YES NO
ACC_IDX4 NORMAL COS ACC TABLE NONUNIQUE DISABLED USERS_IDX 2 255 134217728 1048576 1 2147483645 0 10 NO 3 443750 13348 33 112 1496360 VALID 97432125 19486425 3/28/2009 2:48 1 1 NO N N N DEFAULT NO YES NO
ACC_IDX5 NORMAL COS ACC TABLE NONUNIQUE DISABLED P_IDX 2 255 20971520 2097152 1 2147483645 0 10 NO 3 222000 6208 35 172 1072645 VALID 77145765 15429153 3/28/2009 2:49 1 1 NO N N N DEFAULT NO YES NO
ACC_IDX6 NORMAL COS ACC TABLE NONUNIQUE DISABLED USERS_IDX 2 255 67108864 1048576 1 2147483645 0 0 NO 3 268480 11345 23 124 1415675 VALID 97274285 19454857 3/28/2009 2:49 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX1 NORMAL COS ITEMS TABLE NONUNIQUE DISABLED P_IDX 2 255 67108864 2097152 1 2147483645 0 0 NO 2 142045 57942560 1 1 4678395 VALID 58706975 11741395 3/28/2009 4:47 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX2 NORMAL COS ITEMS TABLE NONUNIQUE DISABLED USERS_IDX 2 255 33554432 1048576 1 2147483645 0 10 NO 2 50370 16819865 1 1 557860 VALID 16819905 3363981 3/28/2009 4:45 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX3 NORMAL COS ITEMS TABLE NONUNIQUE DISABLED USERS_IDX 2 255 1048576 1048576 1 2147483645 0 10 NO 0 1 102 1 1 72 VALID 103 103 3/28/2009 4:45 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX4 NORMAL COS ITEMS TABLE NONUNIQUE DISABLED USERS_IDX 2 255 16384 1048576 1 2147483645 0 10 NO 0 1 33 1 1 32 VALID 33 33 3/28/2009 4:45 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX5 NORMAL COS ITEMS TABLE NONUNIQUE DISABLED USERS_IDX 2 255 1048576 1048576 1 2147483645 0 40 NO 2 585 144929 1 1 6809 VALID 144948 144948 3/28/2009 4:45 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX6 NORMAL COS ITEMS TABLE UNIQUE DISABLED USERS_IDX 2 255 134217728 1048576 1 2147483645 0 0 NO 3 279335 76396450 1 1 1805805 VALID 76396450 15279290 3/28/2009 4:43 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX7 NORMAL COS ITEMS TABLE NONUNIQUE DISABLED P_IDX 2 255 20971520 2097152 1 2147483645 0 10 NO 3 236505 171166 1 4 845735 VALID 76257745 15251549 3/28/2009 4:48 1 1 NO N N N DEFAULT NO YES NO

After Delete Statistics
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENESS COMPRESSION PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOGGING BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE LAST_ANALYZED DEGREE INSTANCES PARTITIONED TEMPORARY GENERATED SECONDARY BUFFER_POOL USER_STATS DURATION PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME PARAMETERS GLOBAL_STATS DOMIDX_STATUS DOMIDX_OPSTATUS FUNCIDX_STATUS JOIN_INDEX
ACC_CUST_START_END NORMAL COS ACC TABLE NONUNIQUE DISABLED P_IDX 2 255 2097152 2097152 1 2147483645 0 10 NO 3 74980 254243 1 37 9579550 VALID 16273950 3254790 3/25/2009 16:37 1 1 NO N N N DEFAULT NO YES NO
ACC_IDX1 NORMAL COS ACC TABLE NONUNIQUE DISABLED P_IDX 2 255 2097152 2097152 1 2147483645 0 10 NO 2 74750 1689094 1 7 12703050 VALID 16237870 3247574 3/25/2009 16:36 1 1 NO N N N DEFAULT NO YES NO
ACC_IDX2 NORMAL COS ACC TABLE NONUNIQUE DISABLED P_IDX 2 255 2097152 2097152 1 2147483645 0 10 NO 3 77200 705438 1 21 14876310 VALID 16293015 3258603 3/25/2009 16:37 1 1 NO N N N DEFAULT NO YES NO
ACC_IDX3 NORMAL COS ACC TABLE UNIQUE DISABLED P_IDX 2 255 2097152 2097152 1 2147483645 0 10 NO 2 37205 16481695 1 1 1114500 VALID 16481695 3296339 3/25/2009 16:37 1 1 NO N N N DEFAULT NO YES NO
ACC_IDX4 NORMAL COS ACC TABLE NONUNIQUE DISABLED P_IDX 2 255 2097152 2097152 1 2147483645 0 10 NO 2 57040 9367 6 41 385830 VALID 16370660 3274132 3/25/2009 16:37 1 1 NO N N N DEFAULT NO YES NO
ACC_IDX5 NORMAL COS ACC TABLE NONUNIQUE DISABLED P_IDX 2 255 2097152 2097152 1 2147483645 0 10 NO 2 23925 3498 6 61 214405 VALID 10723425 2144685 3/25/2009 16:37 1 1 NO N N N DEFAULT NO YES NO
ACC_IDX6 NORMAL COS ACC TABLE NONUNIQUE DISABLED P_IDX 2 255 2097152 2097152 1 2147483645 0 10 NO 2 37040 7774 4 62 485965 VALID 16618285 3323657 3/25/2009 16:37 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX1 NORMAL COS ITEMS TABLE NONUNIQUE DISABLED P_IDX 2 255 67108864 2097152 1 2147483645 0 0 NO 2 131430 14005580 1 1 2123965 VALID 14291660 2858332 3/25/2009 16:41 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX2 NORMAL COS ITEMS TABLE NONUNIQUE DISABLED USERS_IDX 2 255 33554432 1048576 1 2147483645 0 10 NO 2 50345 16853370 1 1 559325 VALID 16853370 3370674 3/25/2009 16:40 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX3 NORMAL COS ITEMS TABLE NONUNIQUE DISABLED USERS_IDX 2 255 1048576 1048576 1 2147483645 0 10 NO 0 1 102 1 1 72 VALID 103 103 3/25/2009 16:40 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX4 NORMAL COS ITEMS TABLE NONUNIQUE DISABLED USERS_IDX 2 255 16384 1048576 1 2147483645 0 10 NO 0 1 33 1 1 32 VALID 33 33 3/25/2009 16:40 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX5 NORMAL COS ITEMS TABLE NONUNIQUE DISABLED USERS_IDX 2 255 1048576 1048576 1 2147483645 0 40 NO 2 585 144929 1 1 6809 VALID 144948 144948 3/25/2009 16:40 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX6 NORMAL COS ITEMS TABLE UNIQUE DISABLED USERS_IDX 2 255 134217728 1048576 1 2147483645 0 0 NO 3 254560 31011190 1 1 1091925 VALID 31011190 6202238 3/25/2009 16:40 1 1 NO N N N DEFAULT NO YES NO
MTRI_IDX7 NORMAL COS ITEMS TABLE NONUNIQUE DISABLED P_IDX 2 255 20971520 2097152 1 2147483645 0 10 NO 3 207535 165018 1 4 700995 VALID 31252925 6250585 3/25/2009 16:42 1 1 NO N N N DEFAULT NO YES NO

I am seeing a performance degradation after the delete.

Thanks a lot for the valuable help to the community.

How do I improve the performance?


Tom Kyte

Followup  

April 02, 2009 - 9:45 am UTC

you do understand that no one could make any sort of comment regarding this right?

so what if there are gaps. big deal.

I see a ton of information (wholly unreadable, but lots of it)

And then a "it runs slow"

with no information about what "it" is, what "it" does, how fast "it" used to be, how slow "it" is now, what you have done yourself to research "it" and so on...



April 02, 2009 - 2:30 pm UTC

Reviewer: A reader

Sorry about the above email. I didnt know what columns were relevant to answer the question.

I have started rerunning the processes to gather the trace files to know where the access paths have changed.

Index rebuild,fragmentation

April 30, 2012 - 7:08 am UTC

Reviewer: Bhaskar from India

Hi Tom,

I have read your book as well as all your posts regarding index rebuild.

From all of them now i'm confirmed the index rebuild is not a permanent solutio n of a slow running query or well mannerd index.

Now i have below questions

1. What actually happens when we rebuild an index?Does we re-create an index from the old one?

2. Then new one would be the same as old one only extra time we spent and some unnecessary redo,undo got generated.

3. Also in OS we usually have fragmentation so does the same thing happen to index also? I mean if we do massive deletion every day on differnt data condition is there any chance massive de fragmentation will happen?

I think i'm stating something wrong in point 3 but i have no clear idea how massive deletion in every day will cause fragmentation in index.

4. As we know index is a balanced tree will index rebuild can cause index depth reduction ? I know in index copression sometimes it may do.

can please help me in clearing all these concepts.
Thanks in advance.

Tom Kyte

Followup  

April 30, 2012 - 8:42 am UTC

1) in general - an index rebuild will read the old index IF POSSIBLE (not always possible) and write a new, compact structure from that.

if the index was set unusable and more data was loaded - that won't be possible and the index rebuild will read the existing table - sort the data and build a new index from that.

2) well, it would not be unnecessary - it would be necessary redo. But you would also have the extra IO that took place, you would have the extra CPU that was used, you would have the increased query response times during the rebuild due to this, you would have (pre-11g) a lock taken before and right at the end of the rebuild contending with your users.

3) huh? I don't know what you mean. If you are talking about 'disk fragmentation' that you would run a disk defragger on - it has no bearing or relevance to storage management within an index.

If you find yourself doing massive deletions every day - please try to think of a way NOT to do that. Delete is the single most expensive DML you can do (well, short of an update that moves a row - like a partition key update - that is a delete+insert)

Look into partitioning for example and use truncate or drop

4) a rebuild could cause a temporary reduction in the height of an index, yes.

index rebuild,fragmentation

April 30, 2012 - 7:12 am UTC

Reviewer: Bhaskar from India

Sorry in point 3 it should be fragmentation.

index rebuild,fragmentation

May 02, 2012 - 12:51 am UTC

Reviewer: Bhaskar from India

Hi Tom,

Thanks for the information.

"1) in general - an index rebuild will read the old index IF POSSIBLE (not always possible) and write a new, compact structure from that. "

As mentioned below for point 1 only if index is in unusable state then index rebuild will read the data from table and in in all othere cases ir will read teh old index for rebuild.
Also as you mentioned it will compact the structure - does it mean it will remove holes craeted in the index?
Also can you please explain how it will compact the structure?

"3) huh? I don't know what you mean.."

Actually i was thinking if we delete some entries from an index the space owned by those entires will no longer be used so there will be hole.

I know if an index is created on a sequenced column and we delete some sequences the space owned by those deleted sequences will create a hole because
we will not be able to regain those spaces.Is it correct?

So will rebuild be in the perfect choice for this above scenarion?

Also say we have an index in name column which contains data like "ram","shyam","nick".Now if we delete "shyam" the space owned by "shyam" entry how will
be used again if we put a new value "john" after deleting "shyam".

Thanks in advance.
Tom Kyte

Followup  

May 02, 2012 - 1:28 pm UTC

1) if the index is valid and usable - a rebuild will read the existing index to create a new one.

It writes out a perfectly compact - filled to pctfree - set of blocks. There will be not extraneous space.


3) you just described what I call a "sweeper" index.

search this site for "sweeper"

a coalesce might be a better option than a rebuild.


For data that is monotonically increasing/decreasing (sequences, dates, timestamps for example) - where you delete a lot but NOT ALL of the old data - you may end up with an index where the left hand side is very sparsely populated but the right hand side is very dense. these indexes may need to be coalesced or rebuilt from time to time in order to correct this.

For data that arrives randomly - like NAME - you typically do not need to perform this operation since the entire breadth of the index is constantly being used.

index rebuild,fragmentation

May 02, 2012 - 1:26 am UTC

Reviewer: Bhaskar from India


In addition to above for point 3 i have read in a documentation that deleted space will not be automatically reclaimed if an exact match key is inserted.

In my scenarion if we delete "shyam" and do not put values "shyam" and put new value "john" then index on ename column will have a blank space created
by "shyam".
So if we rebuild the index then this deleted space will be reclaimed. Is it right Tom?

Thanks in advance.
Tom Kyte

Followup  

May 02, 2012 - 1:29 pm UTC

that is not true at all.

we can reuse "shyam" to store "john"

May 08, 2012 - 6:56 am UTC

Reviewer: Bhaskar from INDIA

Thnaks Tom.

Index deleted space reuse

May 09, 2012 - 6:36 am UTC

Reviewer: Bhaskar from INDIA

Hi Tom,

Thanks for all the suggestions.
Still i have one doubt perhaps it's my wrong concept but can you please help me in understanding the below issue.

"we can reuse "shyam" to store "john" "

So in this case in if we delete row with value "John" data block which was storing the value "John" will be empty.
The word "Shyam" comes after "John" by binary value.
Then If we don't insert "John" but "Shyam" how that particular block which was previously containing value "John" will now have the value "Shyam"

Thanks in advance.
Tom Kyte

Followup  

May 10, 2012 - 3:16 am UTC

let's say the index leaf block was:


Isabel, John, Larry, Paul, Sally, Tom


And that block was 'full'

You delete John, there is now more room on it. So, you go to insert Shyam. Shyam has to go in between Sally and Tom.

There is room on the leaf block for that now, we'll put Shyam right in between the two.

May 10, 2012 - 4:28 am UTC

Reviewer: Bhaskar from India

Hi Tom,

Thanks.
Then you are shyam will be put in the deleted space of john?
Tom Kyte

Followup  

May 10, 2012 - 8:54 am UTC

not necessarily in the same exact place - we might move the bytes around on the block. But that space *can* and *will* be reused.

May 11, 2012 - 12:55 am UTC

Reviewer: Bhaskar from INDIA

Thanks Tom.

Doubt

August 31, 2012 - 12:10 pm UTC

Reviewer: A reader

so without reorg you are saying that oracle will reuse the space.
in that case why do we need to deal high water mark of a block

Please advise


Tom Kyte

Followup  

September 10, 2012 - 6:46 pm UTC

I never said that, please point to where I said that.

the space *will be reused*.

you said it

December 15, 2012 - 8:20 am UTC

Reviewer: A reader

Here you said it..

Followup May 10, 2012 - 8am Central time zone:

not necessarily in the same exact place - we might move the bytes around on the block. But that space *can* and *will* be reused.


Tom Kyte

Followup  

December 18, 2012 - 7:44 am UTC

sorry, I misread you, i thought you meant the space would not be reused - that should have been obvious from me emphatically stating that the space would be reused.


You worry about high water marks sometimes if you are

a) doing full scans, we always scan to the high water mark.
b) just finished a purge of data that dramatically reduced the space needed by this segment and you could reuse that space elsewhere
c) do a ton of indexed reads into this table with lots of free space - you'll be able to cache the table blocks better in the cache if they were more 'dense'

index stats

January 02, 2013 - 2:33 am UTC

Reviewer: A Reader

Tom,

Table t11 and indexes on table t11 are detailed below.


SQL> desc t11;
 Name                                                                Null?    Type
 ------------------------------------------------------------------- -------- ---------------------------------------------
 PARAMETERINSTANCEID                                                          NUMBER
 PARAMINSTANCE2PARAMSETVERSION                                                NUMBER
 PARAMINSTANCE2PARAMDEFINITION                                                NUMBER
 PARAMINSTANCE2PARENTPARAM                                                    NUMBER
 SEQUENCE                                                                     NUMBER
 X_CTCV_CONV_ID                                                               VARCHAR2(50 CHAR)
 X_CONV_RUN_NO                                                                NUMBER

 
 SQL> select dic.index_name, dic.table_name, dic.column_name, dic.column_position, di.index_type    from dba_ind_columns dic, dba_indexes di
  2   where  di.table_name  ='t1' and DIC.index_NAME = di.index_name;

INDEX_NAME      TABLE_NAME                     COLUMN_NAME                    COLUMN_POSITION INDEX_TYPE
--------------- ------------------------------ ------------------------------ --------------- ----------
PI_UK           t11                   PARAMINSTANCE2PARAMSETVERSION                1 NORMAL
PI_UK           t11                   PARAMINSTANCE2PARAMDEFINITION                2 NORMAL
PI_PK           t11                   PARAMETERINSTANCEID                          1 NORMAL
PI_PAD_FK_I     t11                   PARAMINSTANCE2PARAMDEFINITION                1 NORMAL
PI_PI_FK_I      t11                   PARAMINSTANCE2PARENTPARAM                    1 NORMAL

 SQL> select sum(bytes/1024/1024/1024) size_gb  from dba_segments where segment_name = 't11';

   SIZE_GB                                                                      
----------                                                                      
 371.40625                                                                      

SQL> select sum(bytes/1024/1024/1024) size_gb  from dba_segments where segment_name = 'PI_UK';

   SIZE_GB                                                                      
----------                                                                      
411.938477                                                                      

SQL> select sum(bytes/1024/1024/1024) size_gb  from dba_segments where segment_name = 'PI_PK';

   SIZE_GB                                                                      
----------                                                                      
243.816406                                                                      

SQL> select sum(bytes/1024/1024/1024) size_gb  from dba_segments where segment_name = 'PI_PAD_FK_I';

   SIZE_GB                                                                      
----------                                                                      
470.986328                                                                      

SQL> select sum(bytes/1024/1024/1024) size_gb  from dba_segments where segment_name = 'PI_PI_FK_I';

   SIZE_GB
----------
.010742188


we have 02 indexes here more than the table size...

table t11 is range partitioned.
since all table data is sitting in single partition so is the index partition. 


INDEX_STATS for PI_UK is as follows.
SQL> exec print_table('select  *   from bde_index_stats where partition_name=''PI_P01''');
HEIGHT                        : 5
BLOCKS                        : 53992320
NAME                          : PI_UK
PARTITION_NAME                : PI_P01
LF_ROWS                       : 1120039324
LF_BLKS                       : 53804613
LF_ROWS_LEN                   : 213287053602
LF_BLK_LEN                    : 7396
BR_ROWS                       : 53804612
BR_BLKS                       : 187204
BR_ROWS_LEN                   : 825251980
BR_BLK_LEN                    : 8028
DEL_LF_ROWS                   : 10727072
DEL_LF_ROWS_LEN               : 235895058
DISTINCT_KEYS                 : 1120039324
MOST_REPEATED_KEY             : 1
BTREE_SPACE                   : 399441791460
USED_SPACE                    : 214112305582
PCT_USED                      : 54
ROWS_PER_KEY                  : 1
BLKS_GETS_PER_ACCESS          : 6
PRE_ROWS                      : 0
PRE_ROWS_LEN                  : 0
OPT_CMPR_COUNT                : 0
OPT_CMPR_PCTSAVE              : 0



Question/comments
a) By re-partitioning( at the moment table   is range partitioned but all the data flowing into 1st partition.. range is so high )  the table would it help in reducing the index height  it is 5 at the moment. Would reducing height by 1 or 2 would be  really helpful?

b) PCT_USED value of index_stats is 54, - this signifies the index is not compact.. also there are over 10million DEL_LF_ROWS - so would you recommed coalsce or index rebuild in this case?

c) we have BTREE_SPACE around 400gb  and USED_SPACE around 214gb .. again it points to index not compact !
 

regards


b) 

Tom Kyte

Followup  

January 04, 2013 - 2:00 pm UTC

a) depends - do you do range scans or mostly just single row fetches from this index.

a single row fetch would do ~32% less IO if you reduced the index from 5 to 3. It would do ~16% less IO if you reduced the index from 5 to 4.

if you fetch lots of rows - say 20, then reducing from 5 to 3 would reduce IOs by about ~8%, from 5 to 4 about ~4%. If you fetch 200 rows, then about 0.9% and 0.4%.

it depends on where you spend your IO's...


b) probably, this index is probably what I call a sweeper index (search this site for that phrase to read about that)

c) see (b)

..index stats

January 04, 2013 - 4:16 pm UTC

Reviewer: A Reader

Thanks Tom,

I will look for sweeper indexes which you refrred to .
next thing which is puzzling me is.
a) Index LF_rows count is 1/8 of the count(*) in the table.
index is on 02 column c1,c2 -- c1 being the partition key.
select count(*) from t -- 8 billion
select sum(lf_rows) from index_stats where index_name = 'that index' -- 1 billion + 11

select count(*) from t where c1 is NULL -- 11
select count(*) from t where c1 is null and c2 is null -- 0

why index rows and table rows are matching ( i have accounted for NULLs as above )

b) if table T has index t_idx on ( c1,c2 ) column of t
so size of index t_idx ( when no nulls on c1 and c2) > size of index t_idx ( when there are 80% nulls on c1 and c2) . this would be True? -- as nulls entried wount be keyed in index.

Tom Kyte

Followup  

January 14, 2013 - 10:05 am UTC

a) because index stats only ever has but one row. It'll be for the last partition unless you tell us what partition to do.

ops$tkyte%ORA11GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30)
  6  )
  7  PARTITION BY RANGE (dt)
  8  -- subpartition by hash(x)
  9  (
 10    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 12    PARTITION junk VALUES LESS THAN (MAXVALUE)
 13  )
 14  /

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t select to_date( '12-mar-2003','dd-mon-yyyy')+mod(rownum,3), user_id, username from all_users;

48 rows created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create index t_idx on t(dt,x) local;

Index created.

ops$tkyte%ORA11GR2> analyze index t_idx validate structure;

Index analyzed.

ops$tkyte%ORA11GR2> select partition_name, lf_rows from index_stats;

PARTITION_NAME                    LF_ROWS
------------------------------ ----------
JUNK                                   16

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> analyze index t_idx partition(part1) validate structure;

Index analyzed.

ops$tkyte%ORA11GR2> select partition_name, lf_rows from index_stats;

PARTITION_NAME                    LF_ROWS
------------------------------ ----------
PART1                                  16

ops$tkyte%ORA11GR2> analyze index t_idx partition(part2) validate structure;

Index analyzed.

ops$tkyte%ORA11GR2> select partition_name, lf_rows from index_stats;

PARTITION_NAME                    LF_ROWS
------------------------------ ----------
PART2                                  16

ops$tkyte%ORA11GR2> analyze index t_idx partition(junk) validate structure;

Index analyzed.

ops$tkyte%ORA11GR2> select partition_name, lf_rows from index_stats;

PARTITION_NAME                    LF_ROWS
------------------------------ ----------
JUNK                                   16



you'd have to run it for each partition, save the results after each run and then add them up.


b) probably - not guaranteed - but probably... Since 80% of the rows would not be in the second index...

(but think about a case where it might not be true :) )


ops$tkyte%ORA11GR2> create table t1 ( x varchar2(80), y varchar2(80) );

Table created.

ops$tkyte%ORA11GR2> insert into t1 select <b><fill in the blanks!></b> from all_objects;

72998 rows created.

ops$tkyte%ORA11GR2> create index t1_idx on t1(x,y);

Index created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table t2 ( x varchar2(80), y varchar2(80) );

Table created.

ops$tkyte%ORA11GR2> insert into t2 select <b><fill in the blanks!></b> from all_objects;

73000 rows created.

ops$tkyte%ORA11GR2> create index t2_idx on t2(x,y);

Index created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select count(x||y), count(*) from t1;

COUNT(X||Y)   COUNT(*)
----------- ----------
      72998      72998

ops$tkyte%ORA11GR2> select count(x||y), count(*) from t2;

COUNT(X||Y)   COUNT(*)
----------- ----------
      14600      73000

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> analyze index t1_idx validate structure;

Index analyzed.

ops$tkyte%ORA11GR2> select name, btree_space, used_space from index_stats;

NAME                           BTREE_SPACE USED_SPACE
------------------------------ ----------- ----------
T1_IDX                             1231416    1097553

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> analyze index t2_idx validate structure;

Index analyzed.

ops$tkyte%ORA11GR2> select name, btree_space, used_space from index_stats;

NAME                           BTREE_SPACE USED_SPACE
------------------------------ ----------- ----------
T2_IDX                             2926824    2588084



..index stats

January 10, 2013 - 1:44 am UTC

Reviewer: A Reader

Sorry Tom,
Just a typo above
the line
...why index rows and table rows are matching ( i have accounted for NULLs as above )

is corrected to

why index rows and table rows are *NOT* matching ( i have accounted for NULLs as above )


..Index Stats

January 20, 2013 - 11:02 pm UTC

Reviewer: A Reader

Sorry Tom
for not making the things clear in the begining.

when you said :

....a) because index stats only ever has but one row. It'll be for the last partition unless you tell us what partition to do. 


we did calculated for each of the parition and stored those stats into a a table bde_index_stats.

The given output is for the 1st partition.

SQL> exec print_table('select  *   from bde_index_stats where partition_name=''PI_P01''');
HEIGHT                        : 5
BLOCKS                        : 53992320
NAME                          : PI_UK
PARTITION_NAME                : PI_P01
LF_ROWS                       : 1120039324
LF_BLKS                       : 53804613
LF_ROWS_LEN                   : 213287053602
LF_BLK_LEN                    : 7396
BR_ROWS                       : 53804612
BR_BLKS                       : 187204
BR_ROWS_LEN                   : 825251980
BR_BLK_LEN                    : 8028
DEL_LF_ROWS                   : 10727072
DEL_LF_ROWS_LEN               : 235895058
DISTINCT_KEYS                 : 1120039324
MOST_REPEATED_KEY             : 1
BTREE_SPACE                   : 399441791460
USED_SPACE                    : 214112305582
PCT_USED                      : 54
ROWS_PER_KEY                  : 1
BLKS_GETS_PER_ACCESS          : 6
PRE_ROWS                      : 0
PRE_ROWS_LEN                  : 0
OPT_CMPR_COUNT                : 0
OPT_CMPR_PCTSAVE              : 0


and other partitions have NO data ( except the maxva partition which has 11 rows) . so I have included that output above.

Here is the count partition_name, lf_rows from table bde_index_stats;

PARTITION_NAME LF_ROWS
PI_P01   1,120,039,324
PI_P02   0
PI_P03   0
PI_P04   0
PI_P05   0
PI_P06   0
PI_P07   0
PI_P08   0
PI_P09   0
PI_P10   0
PI_PMAXVALUE         11


So could you please suggest on the earlier question ..
as mentioned below 

a) Index LF_rows count is 1/8 of the count(*) in the table.
   index is  on 02 column c1,c2 -- c1  being the partition key. 
select count(*) from t -- 8 billion
select sum(lf_rows)  from index_stats where index_name = 'that index' -- 1 billion + 11



regards








Tom Kyte

Followup  

January 21, 2013 - 7:38 am UTC

show your work step by step by step.

this doesn't match up with what you said the first time, show all steps.

cut and paste.

11g differences in log function?

January 22, 2013 - 9:06 am UTC

Reviewer: A reader from CT, USA

Keeping in mind that reorgs are 99% a waste of time, I ran this out of curiousity and got the followingin 11.2.0.3:

SQL> @indexfrag
    decode (sign(ceil(log(br_blk_len/(br_rows_len/br_rows),
                      *
ERROR at line 4:
ORA-00909: invalid number of arguments

Tom Kyte

Followup  

January 23, 2013 - 1:00 am UTC

who ever posted their query originally had a cut and paste error in it - that query (which I never ran or tested) never worked.

log(br_blk_len/(br_rows_len/br_rows),
            lf_blk_len/((lf_rows_len - del_lf_rows_len)/(lf_rows - del_lf_rows)
           +1 - height), -1,'YES','NO') 



is not a valid call to log, they lost some closing parens there.