Skip to Main Content
  • Questions
  • Rebuilding indexing--space consumption

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Chad.

Asked: June 03, 2003 - 4:17 pm UTC

Last updated: October 31, 2022 - 4:03 am UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

We have a situation where when we go to rebuild certain indexes within the database, it fails with a "Unable to extend temp segment...". What I don't understand is, before this program runs at night, we double check to make sure there is plenty of room in the "Indexes" tablespace, but it continues to fail with the above error. We are rebuilding these indexes sequentially and are NOT using the "online" clause. For instance, the last time this occurred we had over 1.5gb in the "indexes" tablespace and the largest index that we attempted to rebuild was only 500mb. This program just loops through a rebuild statement based off of a cursor that selects the indexes to rebuild for a given table. If there are 6 indexes on a table, should we build in a "pause" so that Oracle can coalesce the tablespace before moving on?

Please explain if there are any other variables that would come into play when Oracle starts to rebuild an index.

Thank you, as always, for any input you may have.

and Tom said...

why are you rebuilding indexes?

what is the goal behind that?

do you have metrics that prove you are doing more good then harm?

did you know that rebuilding indexes can have a massive negative impact on your performance?


Anyway, the space is not reclaimed until AFTER the call has completed. if you rebuild 5 indexes in a single plsql call -- you need room for 10 indexes (5 original, 5 new)

But -- i would recommend you just stop -- just don't do it.


but, if you persist. do this

declare
l_job number;
begin
for x in ....select out indexes
loop
dbms_job.submit
( l_job,
'execute immediate ''alter index ' || index name || ' rebuild'';' );
end loop;
commit;
end;
/


and use alter system set job_queue_processes = N where N is the "number of concurrently running index rebuilds you want"

But -- I would just stop doing it.

Rating

  (42 ratings)

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

Comments

A reader, June 04, 2003 - 9:47 am UTC

HI Chad,
Since the index is failing in temp segment, you should ensure that there is enough space available in temporary tablespace of the user along with the Index tablespace.However as tom said, there may not be any need to rebuild indexes.

Tom Kyte
June 04, 2003 - 10:11 am UTC

It is that when you rebuild (as opposed to coalesce -- should have mentioned that, if you are just trying to "reclaim space" -- coalesce them, that happens IN PLACE) we create TEMP extents in the real tablespace.

That way if the system fails in the middle of a rebuild -- SMON will see lots of orphaned TEMP extents and clean them up.

At the end of the rebuild we turn these temp extents into REAL extents and the old real extents into TEMPORARY ones -- they are not in temp really, they are temporary extents in your permanent tablespace -- they are the index data (we don't use true temp for sorting during a rebuild, the data is sorted)

Their problem is that if you:

begin
execute immediate 'alter index i1 rebuild';
execute immediate 'alter index i2 rebuild';
execute immediate 'alter index i3 rebuild';
execute immediate 'alter index i4 rebuild';
execute immediate 'alter index i5 rebuild';
end;
/

the temporary extents we made are not freed until AFTER the statement executes and the statement here is begin/end

So, that tablespace holding i1..i5 needs to have room for two i1's, two i2's and so on. That is what is happening here.

the solution is of course "stop doing that"

Thanks !!

A reader, June 04, 2003 - 10:52 am UTC


VERY GOOD ANALYSIS

Reader, June 04, 2003 - 11:40 am UTC

If i used 'alter index i1 rebuild tablespace new;', then, oracle does not need space for two indexes in the 'old' tablespace. if the index size is 500MB, we need that much space at the minimum in tablespace 'new'. after this index is rebuilt in tablespace 'new', then, 500 MB space is freed from the 'old' tablespace. I hope i understood this.

Tom Kyte
June 04, 2003 - 12:46 pm UTC

if you are still rebuilding on a schedule, you didn't understand....


coalesce if you want (in place operation)

rebuild, not.

A case to rebuild?

Peter, June 04, 2003 - 11:51 am UTC

We have a DW system. In our daily batch dataload + summarisation we disable bitmap indexes before the load (of course!), load the data and then the bitmaps are rebuilt using ALTER INDEX bm1 REBUILD. For partitioned and sub-partitioned indexes we just rebuild the (sub) partition.

Perhpas the rebuild is the wrong thing to do!

And yes we do run out of temp tablespace sometimes!

Tom Kyte
June 04, 2003 - 12:48 pm UTC

bitmaps are the exception -- as are text indexes.



data warehousing is a bit different as well. Many times you direct path with skip index maintanence and then rebuild.

If the indexes are "healthy", no need to rebuild.


bitmaps can break down from lots of dml.
text indexes are a special kind of bitmap.

b*trees are very very robust.

Explanation for why we rebuild.

Chad Burney, June 04, 2003 - 3:58 pm UTC

The reason why we are rebuilding indexes is because we found that our nightly batch jobs run significantly faster after the rebuilds were done. We have databases that are associated with a "Banking" environment(OLTP & Batch), and every month we must purge millions of rows from some of our largest history tables. I don't have specific metrics on our testing, but what we have found was that after purging and rebuilding indexes, most of our "nightly queues" ran 40% faster.

I have read many of your articles on why people shouldn't rebuild tables and indexes and have found them very interesting and informative. What I have found difficult is trying to explain your reasoning to my management--since they always come back with "so why did our testing show a 40% improvement". Even if I explain to them that this 40% improvement will quickly fade, they respond with "then we will just rebuild them again". As you can see I am fighting a "no win" argument.


Tom Kyte
June 04, 2003 - 4:50 pm UTC

there is the missing point -- purge.

have you considered "coalesce" to achieve the same without doubling the space needed?

but you might have missed one of key points in my discussions:

o unless you have metrics that show you are doing more good them harm, STOP until you have them.


You have a justifiable reason here -- a purge (i would use partitioning probably -- avoids the need to reorg)

you should consider a coalesce instead.

Best of all

krish parmar, June 05, 2003 - 3:09 am UTC

What if instead of getting into business of
rebuilding an existing indexes and increasing room
for it, is RE-creaing an index as it created is a better
solution ?
I my self feel re-creating is best of all.
do you ?


Tom Kyte
June 05, 2003 - 7:58 am UTC

rebuild is better over drop/create.

0% chance of "losing" an index with a rebuild.

100% chance of some day "losing" an index with a drop/create as they are two separate statements that each commit. Someday -- the drop will succeed, the create will fail and no one will notice -- until performance goes down the tubes later that day OR worse - data integrity gets thrown out the window.

For krish

Connor, June 05, 2003 - 6:56 am UTC

You have to be joking ?!

Recreate means:

a) there is a period of time when there is NO index...if its a unique index, then bingo, you might have a corruption

b) you need to store the ddl somewhere or regen it totally every time

Ugh!

index rebuild

Chris Major, June 05, 2003 - 8:26 am UTC

Tom, you are asking, why people are rebuilding indexes? This is from Oracle University's "Oracle9i: SQL Tuning Workshop", page 6-5:

"You should reorganize your indexes regulary to guarantee optimal storage efficiency and performance."

So you (your company) are telling people to do this.

Tom Kyte
June 05, 2003 - 8:45 am UTC

I also say "Question Authority", "test it for yourself", "keep metrics, prove you are doing 'good'"

Myths, Conventional Wisdom, "of course it must be true" are very hard to defeat soundly.

It is all about proofs.

Nothing is 100% true all of the time -- except maybe that "Nothing is 100% true all of the time"

Coalesce vs Rebuild?

Chad, June 05, 2003 - 9:26 am UTC

This might be a stupid question, but I will ask it anyway. What is the major differences between coalescing vs. rebuilding--I now know one is "the space needed". What I don't understand is why rebuild when we can just coalesce. Does this get rid of the "brown leaves" within the index?

Tom Kyte
June 05, 2003 - 9:53 am UTC

define "brown leaves" -- but anyway...

coalesce just takes leaf blocks and merges them together -- putting the one now empty block onto the freelist for the index to be reused elsewhere (space reclaimed).

rebuild -- well, it rebuilds it. You need 2x the space -- index goes back to what the index would look like if you dropped and created it.

"Brown leaves"?

Chad, June 05, 2003 - 12:35 pm UTC

I am sorry, I once heard someone refer to index entries pointing to non-existant(purged) rows as "brown leaves". Maybe I should never have picked up that phrase.

Thank you for all of your help. It definitely sounds like what we should be doing is coalescing--since then the index doesn't then have to go through the entire "growth process" again.

Tom Kyte
June 05, 2003 - 1:33 pm UTC

I've heard it too -- it is just "at which stage do them become brown" that is ill defined.



How about bulk inserts

yogesh, June 05, 2003 - 1:54 pm UTC

Agreed that coalesce is better than rebuild... but this is true in case of reclaiming the space .. if the scenario is every day some 50000 records are being added in the table ... so monthly 30*50000=1500000 ... and existing record count is 100000000 ...

Now in every insert index is also getting changed / rebuild (may be I'm wrong here) !! so in this case how / why rebuild is helpful ..

Tom Kyte
June 05, 2003 - 3:26 pm UTC

i don't believe in general it would be for a b*tree index.

Great

Reader, June 05, 2003 - 5:58 pm UTC

If I have a table say, orders with order number as a column. Once I complete delivering orders to customers, I delete those rows from the table. I have a B tree index on the order number. My order number always increases and therefore, I would never use the same order number again. Assume that my business is growing and I am deleting thousands of rows every week after fulfilling the orders. Is the index on order number a candidate for coalesce or rebuild, say after a month or so? Thanks.

Tom Kyte
June 05, 2003 - 6:43 pm UTC

you delete your orders? :)

But anyway -- if you delete all of them -- meaning that if orders 1 .. 100 were on the first leaf block and you eventually delete 1..100 -- that leaf block will be put on the freelist all by itself.

It would be if you deleted MOST of 1..100 and left some, and would never delete them, that you would consider coalescing over time to combine blocks with mostly free space and release some others back to the freelist.

Our Rebuilding Procedure?

Chad, June 06, 2003 - 2:26 pm UTC

I was finally able to get the procedure we use to rebuild our indexes. Would you say this is comprable to what you suggested?

declare
cursor theList is select index_name, tablespace_name
from all_indexes
where (owner='OSIBANK')
and (table_name in ('ACCTBALHIST','ACCTRATEHIST','ACCTRCVB','ACCTSTATISTICHIST','ACCTYTDHIST','ACCTAVAILAMTHIST','ACTV','ACTVSUBACTV','CARDTXN','FILEWAREHOUSE','FILERECORD','ACCTCARDTXNHOLD','ACCTACCTSTATHIST','ACCTSUBACCT','ACCTINTHIST','RTXN','RTXNAGREEMENT','RTXNFUNDTYP','RTXNSTATHIST','RTXNBAL'));
theSegmentName all_indexes.index_name%TYPE;
theSegmentTablespace all_indexes.tablespace_name%TYPE;
theCommand varchar2(255);
theCursor integer;
ret_val number;
begin
-- Open the cursor
open theList;

-- Loop over the list of troublesome indices
loop
-- Get the characteristics of an index
fetch theList into theSegmentName, theSegmentTablespace;
exit when theList%NOTFOUND;

theCommand := 'ALTER INDEX OSIBANK.' || theSegmentName || ' REBUILD TABLESPACE ' || theSegmentTablespace;

-- Use an Oracle-provided package to execute the DDL and clean up
theCursor := DBMS_SQL.OPEN_CURSOR;
BEGIN
-- DBMS_OUTPUT.PUT_LINE('... Rebuilding ' || theSegmentName || '...');
DBMS_SQL.PARSE(theCursor, theCommand, DBMS_SQL.NATIVE);
ret_val := DBMS_SQL.EXECUTE(theCursor);
EXCEPTION
WHEN OTHERS THEN
--DBMS_OUTPUT.PUT_LINE('... Unable to rebuild ' || theSegmentName || '...moving on');
DBMS_OUTPUT.PUT_LINE(theSegmentName);
END;
DBMS_SQL.CLOSE_CURSOR(theCursor);
end loop;

-- Close the cursor
close theList;
end;
/


Tom Kyte
June 06, 2003 - 2:56 pm UTC

no, i think you might want to try coalesce still :)

Rebuild or coalesce?

Tracy Tupman, November 06, 2003 - 11:37 am UTC

If you have a table containing 20 million rows and you delete 15 million of them and then you intend to maintain that table at around five million rows presumably it is better to rebuild the indexes on the table after deleting the 15 million otherwise you are never really going to reclaim the space taken up by the indexes, are you? Even if you coalesce until you are blue in the face the overall 'size' of an index will be as it was when the table had 20 million rows and thus a lot of space will be wasted. If it had 10,000 blocks when there were 20 million rows, it will still have 10,000 when it goes down to 5 million. Correct or not - that is the question? Coalescing may squeeze some of the index entries into fewer blocks and so put more blocks back on the freelist but there will still be just as many blocks allocated. Is that right and if so, is there any other way of really reclaiming the free space other than doing a rebuild or dropping and recreating? (That's the second question.)

Tom Kyte
November 06, 2003 - 5:14 pm UTC

wasted is in the eye of the beholder isn't it? i assume the table is going to grow again -- so, the space is just "reserved"

but, if I were to delete 3/4's of a 20million row table -- I might WELL be looking at other approaches

a) partitioning to avoid the delete
b) create table as select rows to keep, drop old table, rename new

for example.

free space is a reletive term here.

This is great stuff.

Mark, November 18, 2003 - 3:02 pm UTC

What caught my eye on this thread was a brief discussion of what happens to a b-tree index when deleting table rows from the left, if you will. That is, if my PK column is fed by a sequence and always increases, never repeats values, then I imagine new data being inserted on the right and old data being deleted from the left. And, as you reminded us, only after ALL rows in a block are gone does Oracle put the block back on the freelist.

Well, were do new index entries go now, and does it matter? I picuture my index always splitting to the right since I have ever increasing values, but my available blocks are back on the left. I would really like to know the scoop on this.

Just for what it's worth, I recently attended an Oracle Education course "Oracle9i Perf Tuning". They brand new books suggest we rebuild all indexes when the ratio of del_lf_rows/lf_rows > 0.30, or when the index height is > 4.


Tom Kyte
November 21, 2003 - 11:00 am UTC

I wish they would RECOMMEND COALESCE, but anyway...

new index entries go on the right hand side. when we populate a pkey via an increasing sequence -- we detect that and do a "90/10" split. when the block on the right fills up -- we put 90% of the rows to the left and 10% to the right and continue on.

As blocks empty on the far left -- they go onto the free list for the index. As we add blocks on the right -- we get them from the free list. so a block that was on the left yesterday might be on the right tomorrow.

Did it ever ? (make sense)

Gab, May 07, 2004 - 1:49 pm UTC

Hello Tom,

Did it ever make to periodically rebuild indexes? I am in a shop where senior dba contiue to advocate the daily rebuild of indexes and I am trying to understand if that is because in older versions of Oracle (6 or 7) was there a need for that.

(i'm convinced that is not necesary but I don't have a say...)

Thank you,

Tom Kyte
May 07, 2004 - 2:40 pm UTC

no, it doesn't.

Rebuild may be better

Angus McTavish aged 76, December 16, 2004 - 5:57 am UTC

If we accept that reorganising indexes is sometimes required via a rebuild or coalesce, rebuild may well be the better option despite all the claims in favour of coalesce. The main downside is that extra space is needed during the rebuild, but it's only temporary.
Take the ascending sequence index as an example. If older data is regularly removed from the table the index will grow over time even though the table may not be.
The following is from a live system where performance measurements have shown the benefit of occasional index reorganisation:

angus:LIVE@sun1> @co angus@sun1
Enter password: ********
Connected.
angus:LIVE@sun1> @blocks
Segment: PK_SELECTIONS_ID
Type : index
Total number of blocks = 38340
Total number of unused blocks = 3113
Number of used blocks with free space = 9704
angus:LIVE@sun1> alter session set sort_area_size=100000000;
angus:LIVE@sun1> alter index PK_SELECTIONS_ID coalesce;
angus:LIVE@sun1> @blocks
Segment: PK_SELECTIONS_ID
Type : index
Total number of blocks = 38340
Total number of unused blocks = 3113
Number of used blocks with free space = 16058
(more blocks moved to the freelist but no space reclaimed, i.e. the index is no smaller)
angus:LIVE@sun1> @co angus@sun1
Enter password: ********
Connected.
angus:LIVE@sun1> alter session set sort_area_size=100000000;
angus:LIVE@sun1> alter index PK_SELECTIONS_ID rebuild online;
angus:LIVE@sun1> @blocks
Segment: PK_SELECTIONS_ID
Type : index
Total number of blocks = 12780
Total number of unused blocks = 1497
Number of used blocks with free space = 0
(index reduced in size by over two-thirds)

Here are some stats taken from tracing the two sessions:

Coalesce:
Commit cleanouts 54,240
DB Block Changes 359,790
DB Block Gets 303,625
redo entries 183,665
redo size 280,760,300

Rebuild Online:
Commit cleanouts 65
DB Block Changes 303
DB Block Gets 3,873
redo entries 11,452
redo size 92,741,636

The main benefit is that rebuild online really does reclaim space and generates far less redo, which may be of great importance on a busy OLTP system.

Tom Kyte
December 16, 2004 - 8:35 am UTC

sigh


i give up.


seriously give up.


I don't think I've ever said:

o never ever under any circumstances do a rebuild
o if you believe you need rebuilds, stop -- use coalesce instead


I have said:

a) rebuilds are rarely needed and the cases when they are are pretty visible (easy to see)

b) consider using coalesce over time instead of a rebuild

c) sometimes a rebuild is called for.




and your "example" is just a bunch of numbers on a screen.


Your rebuilds going down by 2/3'rds? so what, come back in a month and tell us how many resources you used to get that 2/3'rds BACK -- you do realize that is a temporary sort of thing right -- the index is just going to get "fat" again.

You realize the 2/3rds the other one kept is in the freelists -- it is free space, it just belongs to that index (which is going to need it again and again and again and again)

And that the "index is no smaller" is not correct really -- the index is in fact smaller, the space allocated to the index is the same, the index itself is smaller (range scans are smaller)

Don't give up

Angus, December 16, 2004 - 9:35 am UTC

sigh.

I give up too.

I didn't say that you claimed that you should "never ever under any circumstances do a rebuild".(Read what I actually put.)


However, there are numerous places in Ask Tom where you point out the benefits of coalesce over rebuild - I'm simply trying to redress the balance. And you haven't commented at all on the fact that coalesce generates loads more redo than rebuild.

Basically all I'm saying is:

Coalesce - generates tons of redo, does not really free space.

Rebuild - generates much less redo, may genuinely free up space, not just for this index but for any other segment in the tablespace because the unused space is actually given back.

And there may well be cases where an index will remain permanently smaller so it's worth chucking the spare space back in the pot where any segment in the tablespace can get at it.

Don't give up - yours is an excellent site. (Just read what people are writing a little more carefully.)
;)

Tom Kyte
December 16, 2004 - 9:43 am UTC

Coalesce - generates tons of redo, does not really free space.
^^^^^^^^^^^^^^^^^^^^^^^^^^

hence the beauty of it. The *temporary* gain in space -- stress temporary, because it is probably going right back into that index -- is offset by

a) someone else grabbing it and this index running out of space.
b) the cost of reallocating the space to the index over time as it needs it.

I read everything there. I just disagree with portions of the conclusions (that freeing the space is a good thing (tm)) and don't really have the test case you used to simulate with.





Key compression

A reader, January 07, 2005 - 2:45 pm UTC

I created a composite index on 7 columns and forgot to use the COMPRESS keyword to enable key compression.

Would it be faster to drop and recreate the index with the COMPRESS keyword or do a ALTER INDEX REBUILD COMPRESS? Why?

Thanks


Tom Kyte
January 08, 2005 - 4:01 pm UTC

it would be safer and more correct to rebuild an existing index. You won't "lose it" by accident, you can still use the data whilst it is happening, and in most cases -- we can use the existing index in place to avoid full scanning the original table and sorting it.

index coalesce

Sanji, January 27, 2005 - 7:05 am UTC

I fully subscribe to concept of coalescing against rebuilding, but am left with a doubt.
Here is a transcript from Note:30405.1 (metalink)
"when data is inserted into a Leaf block, and there is no room for the insert, a very expensive operation called a split occurs. The split creates a new Leaf block and possibly new Branch blocks as well to maintain the balance of the tree. The split operation is by far the most expensive operation that is done in the maintenance of B* trees so we go to great lengths to avoid them."

What if a coalesce operation merges 2 index blocks into one and frees the other (which apparently gets attached to the freelist). The occupied block is 65% full and there is an insert that causes further leaf blocks to split coz it cannot accomodate in this particular block.

Or am i missing a point here ?

Rgds
Sanji

Tom Kyte
January 27, 2005 - 9:16 am UTC

it is not "coalesce against rebuild", it is rather don't forget -- COALESCE exists and is an option and is done online without lots of extra work that an online rebuild does.

taking your example a step further, if you REBUILD an index, you'll actually rewrite the entire branch structure (the width of the index), a coalesce doesn't touch them.

so a rebuild is more painful to re-split.


coz?

index reorganization

Sanji, January 28, 2005 - 12:14 am UTC

Let be it coalesce, rebuild or drop/ recreate. What I intended to confirm is that any of the index reorganization techniques would free blocks and/or attach to the freelist, whatever the case may be. The newly organized block, say in case of coalesce, might not be able to accommodate further (index) data and might induce block splitting, be it 90/10 or 50/50. Coalesce certainly would help in terms of white space/ browning, eliminating(almost) fragmentation.
But it's the block split doubt that i wanted to clear.

Just a confirmation.

Rebuilding undoubtedly is an expensive proposition and not a very sensible/ justifiable procedure. I agree to that.

Rgds
Sanji

Tom Kyte
January 28, 2005 - 7:18 am UTC

of course, if you take index blocks that are half full (half empty) and you put them together so you have a totally full block -- index splits will most likely be the result in the near future.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6601312252730 <code>

was a discussion on this.


rebuilding is not entirely evil.

rebuilding all indexes on a schedule just "because" is.

Coalesce and redo

Tracy Tupman, May 04, 2005 - 4:58 am UTC

Coalescing indexes may be a good thing, sometimes (there is plenty of discussion of coalesce and rebuild on this site), but why does coalesce with nologging still generate a load of redo?
We have a production site where there are instances when a coalesce is useful, however on a large index it can generate enough logging activity to cause performance issues during the coalesce. Here is an example on one of the smaller indexes. Coalescing a 63,000 block index generated almost 100mb of redo in a little under 3 minutes despite specifying nologging:
TUP@prod> set serveroutput on
TUP@prod> declare
2 free_blocks number;
3 m_tot_blocks number;
4 m_tot_bytes number;
5 m_unused_blocks number;
6 m_unused_bytes number;
7
8 m_last_file_id number;
9 m_last_block_id number;
10 m_last_block number;
11
12
13 begin
14 dbms_space.unused_space(
15 segment_owner => 'TUP',
16 segment_name => 'IX_TRANS_ID',
17 segment_type => 'INDEX',
18 total_blocks => m_tot_blocks,
19 total_bytes => m_tot_bytes,
20 unused_blocks => m_unused_blocks,
21 unused_bytes => m_unused_bytes,
22 last_used_extent_file_id => m_last_file_id,
23 last_used_extent_block_id => m_last_block_id,
24 last_used_block => m_last_block,
25 partition_name => NULL);
26 DBMS_SPACE.FREE_BLOCKS('TUP','IX_TRANS_ID','INDEX',0,free_blocks);
27 dbms_output.put_line('Total number of blocks = '||m_tot_blocks);
28 dbms_output.put_line('Total number of unused blocks = '||m_unused_blocks);
29 dbms_output.put_line('Number of used blocks with free space = '||free_blocks);
30 end;
31 /
Total number of blocks = 63950
Total number of unused blocks = 7091
Number of used blocks with free space = 23

PL/SQL procedure successfully completed.

TUP@prod> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic# = b.statistic#
4 and a.name like '%redo%'
5 and b.sid = 234;

NAME VALUE
---------------------------------------------------------------- ------------
redo synch writes 1
redo synch time 1
redo entries 2
redo size 532
redo buffer allocation retries 0
redo wastage 0
redo writer latching time 0
redo writes 0
redo blocks written 0
redo write time 0
redo log space requests 0
redo log space wait time 0
redo log switch interrupts 0
redo ordering marks 0

14 rows selected.

TUP@prod> ALTER INDEX IX_TRANS_ID COALESCE NOLOGGING;

Index altered.

TUP@prod> declare
2 free_blocks number;
3 m_tot_blocks number;
4 m_tot_bytes number;
5 m_unused_blocks number;
6 m_unused_bytes number;
7
8 m_last_file_id number;
9 m_last_block_id number;
10 m_last_block number;
11
12
13 begin
14 dbms_space.unused_space(
15 segment_owner => 'TUP',
16 segment_name => 'IX_TRANS_ID',
17 segment_type => 'INDEX',
18 total_blocks => m_tot_blocks,
19 total_bytes => m_tot_bytes,
20 unused_blocks => m_unused_blocks,
21 unused_bytes => m_unused_bytes,
22 last_used_extent_file_id => m_last_file_id,
23 last_used_extent_block_id => m_last_block_id,
24 last_used_block => m_last_block,
25 partition_name => NULL);
26 DBMS_SPACE.FREE_BLOCKS('TUP','IX_TRANS_ID','INDEX',0,free_blocks);
27 dbms_output.put_line('Total number of blocks = '||m_tot_blocks);
28 dbms_output.put_line('Total number of unused blocks = '||m_unused_blocks);
29 dbms_output.put_line('Number of used blocks with free space = '||free_blocks);
30 end;
31 /
Total number of blocks = 63950
Total number of unused blocks = 7091
Number of used blocks with free space = 3582

PL/SQL procedure successfully completed.


TUP@prod> select a.name,b.value
2 from v$statname a,v$sesstat b
3 where a.statistic# = b.statistic#
4 and a.name like '%redo%'
5 and b.sid = 234;

NAME VALUE
---------------------------------------------------------------- ------------
redo synch writes 8
redo synch time 173
redo entries 101,644
redo size 93,146,348
redo buffer allocation retries 525
redo wastage 0
redo writer latching time 0
redo writes 0
redo blocks written 0
redo write time 0
redo log space requests 2
redo log space wait time 62
redo log switch interrupts 0
redo ordering marks 0

14 rows selected.

Is there any way to really stop all this logging?



Tom Kyte
May 04, 2005 - 9:08 am UTC

<quote>
but why does coalesce with nologging
still generate a load of redo?
</quote>

because you are modifying a data structure, not building a new one.

Same reason why a

insert /*+ append */ into table select.....

will generate gobs of redo if the table has indexes on it -- because the data has to be merged into the index.


coalesce is in effect "updating" the index. as it moves leaf entries from block A to block B -- it must be able to repeat that if the instance fails (the blocks are in the cache, if the instance failed in the middle, we need to be able to put things back they way they were)

A rebuild can "skip most of the redo", but at the cost of 2x (or more, or less) the space (and the need to schedule a backup real soon)

Coalesce and redo

Tracy Tupman, May 04, 2005 - 10:30 am UTC

So is nologging superfluous when added to an index coalesce?

One interesting thing I noted was that when I ran an index coalesce on a very large index and then killed the session before it had completed (because of all the redo generation and the impact on log writer), the coalescing that it had done before the session was killed was not 'undone', as evidenced by running a DBMS_SPACE.FREE_BLOCKS command on the index before starting the coalesce and again after killing the session part way through the coalesce.


Tom Kyte
May 04, 2005 - 10:55 am UTC

correct, coalesce is like "update"

and yes, coalesce is an ONLINE operation meaning it does a bit, commits, a bit, commits .... it doesn't lock the entire index as it works it way through.

How to compute temp tablespace usage when creating indexes

Steven, May 04, 2005 - 10:32 pm UTC


Hi Tom,
when creating index on big table,it would use temp tablespace to sort . Is there a way we compute how much temp tablespace we will allocate for sort?


Thanks in advance.



Tom Kyte
May 05, 2005 - 7:17 am UTC

Jonathan Lewis recently answered this in the newgroups:

</code> http://asktom.oracle.com/~tkyte/index_temp.html <code>

will get you to the thread.

Will rebuilds lower the high water mark at all?

A reader, July 14, 2005 - 10:42 pm UTC

We have a situation where someone decided they don't need a series of indexes totalling 30GB in a datwarehouse. Now the customer wants to reclaim the disk from the index tablespace and corresponding disk it was sitting on. This would require lowering the high water mark to shrink the datafiles, or, just drop all the indexes and rebuild them in a smaller tablespace (probably too time consuming) Is there any way to do this? What if all indexes were rebuilt, would that lower the high water mark? I would guess not but you will know I'm sure. Any suggestions?

Tom Kyte
July 15, 2005 - 7:36 am UTC

if you are using locally managed tablespaces, the rebuild generally would move them to the "front" of the file -- but that is not "assured or promised"

you can query dba_extents to see the "furtherest out allocated extent" and another query to generate the shrink command

</code> https://www.oracle.com/technetwork/issue-archive/2014/14-sep/o54asktom-2279184.html <code>

"whats at the end of a file"

when you see what is at the end of the file, you can:

a) shrink file to be as small as it can be
b) move (rebuild) that last object in the same tablespace, that'll force it to reuse space "in the front"
c) goto a until you are happy with how small the file is.

Good discussion...

Mark, December 13, 2005 - 9:17 am UTC

...but I have another point to throw out there.

We recently 'fixed' INI_TRANS settings on indexes related to 3 of our busiest tables, based on another thread on this site that suggested that indexes INI_TRANS should be 'at least, if not more that the table' the index is created for.

INI_TRANS changes apply only to NEW BLOCKS acquired by the segment. ALTER TABLE TNAME MOVE is required to fix the existing blocks on tables, and ALTER INDEX INAME REBUILD for indexes.

So, in this case, it appears that REBUILD would be a good thing and we changed the INDEX settings then REBUILD each of them.

Any comments Tom?

Regards,
Mark

Tom Kyte
December 13, 2005 - 9:51 am UTC

if you wanted to change the initrans on existing stuff, yes.

Rebuilding and Coalesce

Kirklin, December 14, 2005 - 3:26 pm UTC

Although searching for information about an Oracle Text index rebuild error (DRG-11513)I stopped and read every review entry. While some of the details are presently beyond my Oracle skill level, all of the discussions were very insightful. Some were even quite humorous! I look forward to continued use of the site as a means of gaining greater proficiency in Oracle Development. Thanks for providing a site to stimulate ongoing growth.

When to coalesce then?

Ben, February 23, 2006 - 9:13 pm UTC

I fully understand the "why's" about coalesce vs rebuild, BUT how do I know when either one is needed? I just recently took over as the sole dba in our company and was left with no documentation on what the previous dba was consistently performing. We are running a 9i db and after my initial education on Oracle dba tasks, via Fund I and II classes, I discovered that none of tablespaces are locally managed. Our compatible parameter is still sitting at 8.1.0 and the only statistics that we are gathering are by means of the dbms_utility.analyze_schema procedure once a week. We have a 450G db and one of our largest tables has about 10G of indexes. I have a sneaking suspiction that we desperately need to rebuild/coalesce some indexes. I did see, in the thread that someone mentioned a formula for rebuilding, but I have yet to see anything that tells me how to find out if I need to coalesce. Is there a rule of thumb or process to determine if coalesce is needed?

Tom Kyte
February 24, 2006 - 7:54 am UTC

there really isn't a formula for determining an index needs rebuilding, not one that I've ever seen that makes sense anyway.


The type of index that is most frequently in need of a rebuild OR coalesce would be what I term a "sweeping index". The sweeper is an index on a monotomically increasing field (a sequence, a date, a timestamp). The table it is one always inserts "higher and higher" values - and the applications tend to delete "older values" over time.

This will have the index be in a state whereby the right hand side is nicely packed, very dense - but the left hand side is very sparse - much white space.

Now, if you always just "select * from t where indexed_column = :x" - this index is OK, the time to access a given key value won't likely change much before/after a rebuild/coalesce.

If you however

-- get oldest record
select * from (select * from t order by index_column) where rownum = 1;

-- get the oldest records in order
select /*+ first_rows */ * from t order by index_column;

etc - that is, range scan starting from the left to the right - you will likely find that you spend an inordinate amount of time scanning the almost empty left hand side.

This index would be a candidate for a coalesce (always online, enterprise edition only feature, minimal impact) or a rebuild (can be online in enterprise, needs approximately two times the space).

So, it would be

o measure the IO's performed by the most popular queries using the index in question (statspack, whatever)

o try the coalesce

o measure again - did it change? if not

o try the rebuild

o measure again - did it change? if not, don't bother these steps again, obviously the index was not in need of a reorg



Myth : space is never reused in Index

deba, June 13, 2007 - 2:43 am UTC

Hi Tom,

I would like to congratulate you for your book "Expert Oracle Database Architecture". This is excellent book
By the way, I have one confusion regarding topic "MYTH: SPACE IS NEVER REUSED IN INDEX" ( page 482 ).

I have the done the same things what you have shown in your book. But output shows something different. It proves
that space is getting increased. Could you please explain this ?

SQL> create table ddas (x number constraint ddas_pk primary key );

Table created.

SQL> insert into ddas values ( &1 ) ;
Enter value for 1: 1
old 1: insert into ddas values ( &1 )
new 1: insert into ddas values ( 1 )

1 row created.

SQL> /
Enter value for 1: 2
old 1: insert into ddas values ( &1 )
new 1: insert into ddas values ( 2 )

1 row created.

SQL> /
Enter value for 1: 99999
old 1: insert into ddas values ( &1 )
new 1: insert into ddas values ( 99999 )

1 row created.

SQL> commit;

Commit complete.

SQL> execute dbms_stats.gather_table_stats('STOWNER','DDAS',cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> analyze index ddas_pk validate structure;

Index analyzed.

SQL> select height,blocks,lf_rows,lf_blks,btree_space,name from index_stats;

HEIGHT BLOCKS LF_ROWS LF_BLKS BTREE_SPACE
---------- ---------- ---------- ---------- -----------
NAME
------------------------------
1 8 3 1 7996
DDAS_PK


SQL> begin
2 for i in 2..9999
3 loop
4 delete from ddas where x = i;
5 insert into ddas values ( i+1 );
6 end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> analyze index ddas_pk validate structure;

Index analyzed.

SQL> select height,blocks,lf_rows,lf_blks,btree_space,name from index_stats;

HEIGHT BLOCKS LF_ROWS LF_BLKS BTREE_SPACE
---------- ---------- ---------- ---------- -----------
NAME
------------------------------
2 40 9890 34 279892
DDAS_PK

Thanks
Deba
Tom Kyte
June 13, 2007 - 8:10 am UTC

and where is your commit.

You have simply shown that space in the index will not be reused in the same transaction.

You changed my example, the commit was relevant.

Myth : space is never reused in Index

deba, June 14, 2007 - 12:32 am UTC

Hi Tom,

Sorry for the wrong posting. I intentionally removed that commit. What I want to know is that space is getting reused across the transaction but not in the same transaction. But ideally it should have been reused within same transaction. Becasue this delete and insert both are coming from same transaction ( session ) and Oracle also knows that. Just I would like to know for what reason Oracle is not reusing the space within transaction.

Thanks
Deba
Tom Kyte
June 14, 2007 - 7:11 am UTC

but ideally it cannot be by design.

Has to do with read consistency, non-blocking reads and the fact that an index is a hugely complex data structure.

WRT Followup June 4, 2003 - 10am US/Eastern:

kg, January 01, 2009 - 6:51 pm UTC

you say "we create TEMP extents in the real tablespace"
Is there any way we can find put temp segments created during rebuild in permanent tablespace?

You say "At the end of the rebuild we turn these temp extents into REAL extents and the old real extents
into TEMPORARY ones -- they are not in temp really, they are temporary extents in your permanent
tablespace -- they are the index data (we don't use true temp for sorting during a rebuild, the
data is sorted)"

I can see temporary segments for one of the index rebuild but in temporary tablespace of user WIP.

Then why would require 2x space during rebuild in permanent tablespace ?

SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;


SID_SERIAL
--------------------------------------------------------------------------------
USERNAME OSUSER SPID
------------------------------ ------------------------------ ------------
MODULE
------------------------------------------------
PROGRAM MB_USED
------------------------------------------------ ----------
TABLESPACE STATEMENTS
------------------------------- ----------
TEMP 1

154,4890
WIP oraebs 18445
SQL*Plus
(P015) 200.390625
TEMP 1

174,92
WIP oraebs 18436
SQL*Plus
(P011) 208.40625
TEMP 1

185,245
WIP oraebs 18443
SQL*Plus
(P014) 196.382813
TEMP 1

226,127
WIP oraebs 18424
SQL*Plus
(P005) 256.5
TEMP 1




Tom Kyte
January 05, 2009 - 10:30 am UTC

... Is there any way we can find put temp segments created during rebuild in
permanent tablespace?
...

don't know what you mean by that.


... I can see temporary segments for one of the index rebuild but in temporary
tablespace of user WIP.
...

during an index rebuild we normally read the old index and write the new index (no sorting needed). We would never hit temp.


However, if the index being rebuilt cannot use the old index structure (eg: you are doing an index rebuild AFTER a table move, the rowids in the old index are useless - therefore we need to full scan the table, find the index keys and rowids, sort them ** THIS NEEDS TEMP SORT SPACE **, and then write the new index.


So, I think you are rebuilding an index that requires a table scan and sort. We'll full scan table, fill up temp with sort data AND THEN create temp extents in the permanent tablespace and copy the temp sort data there and then turn the temp extents in the permanent tablespace into permanent extents.


That is why the 2x again - you would have

a) the old useless index
b) the sort space in temp
c) the temporary extents after (b) is done as we copy the index from temp to permanent space

regarding temp space consumed

kg, January 08, 2009 - 11:35 am UTC

don't know what you mean by that.

... Tom here I wanted to know how much space is consumed by temp extents created in real tablespace.
Guess from your answer "b)" space consumed in temporary tablespace.


Thanks for answering other one.
You are correct ,index rebuild was part of table reorg activity .
Can we estimate temp space required for sort ,if 60-70% of table data is purged ?

Tom Kyte
January 08, 2009 - 11:51 am UTC

temp space required is heavily dependent on PGA settings.

could be zero if pga settings are high

could be many times the size of the index if they are low and we are forced to do a multi-pass sort into temp.


if you are worried about hitting "out of temp", but don't want to over allocate temp, you could

a) allow temp to autoextend up to some ultimate maximum size
b) use resumable so that when temp is exhausted, your statement pauses, does not fail. You can decide to add more temp then OR kill it.

regarding temp space consumed .. contd

kg, January 09, 2009 - 1:17 pm UTC

Thanks Tom
Actually i am worried about temp consumed in permanent tablespace .
from your b) point i assumed a temp usage(again it depends on PGA settings) .

Why the existing index space is not released when index is rebuild by doing FTS of table?


Tom Kyte
January 09, 2009 - 3:06 pm UTC

the temp space in the permanent tablespace is the size of the resulting index.


when you build an index it will use TRUE temp to sort the data and it will allocate temporary extents in the permanent tablespace to write the index to. When the index create is done, the TRUE temp is released, the temporary extents in the permanent tablespace are converted into permanent extents.

I'm not sure what you mean in your last paragraph at all?

regarding temp space consumed .. contd

kg, January 11, 2009 - 5:26 am UTC

Thanks Tom

As a part of table move when index is rebuild ,it does FTS of a table.
In this scenario the existing unusable index keeps on occupying space till the index rebuild is complete.
why the existing index space is not released when the rebuild is gonna done by accessing base tables.

Tom Kyte
January 12, 2009 - 8:37 pm UTC

because if a statement fails, the database should be left "as it was"

regarding temp space consumed --end

kg, January 13, 2009 - 7:24 am UTC

Thanks Tom for clarification

Index rebuild

Chinni, May 25, 2009 - 4:36 am UTC

Hi Tom,
I am reading your latest book - chapter 11 Indexes. Could you please clarify this??
<< Start of Extract - Page 528, Apress.Expert.Oracle.Database.Architecture.9i.and.10g.Programming.Tech >>

This shows the space in the index was reused. As with most myths, however, there is a
nugget of truth in there. The truth is that the space used by that initial number 2 (in between
1 and 9,999,999,999) would remain on that index block forever. The index will not ¿coalesce¿
itself. This means if I load a table with values 1 to 500,000 and then delete every other row (all
of the even numbers), there will be 250,000 ¿holes¿ in the index on that column. Only if I reinsert
data that will fit onto a block where there is a hole will the space be reused. Oracle will
make no attempt to ¿shrink¿ or compact the index. This can be done via an ALTER INDEX
REBUILD or COALESCE command. On the other hand, if I load a table with values 1 to 500,000
and then delete from the table every row where the value was 250,000 or less, I would find the
blocks that were cleaned out of the index were put back onto the freelist for the index. This
space can be totally reused.

<< Extract End >>

1. Could you kindly elaborate this?

... Only if I reinsert data that will fit onto a block where there is a hole will the space be reused. Oracle will
make no attempt to ¿shrink¿ or compact the index. ...

2. I understand, from your ppts , that we should not rebuild the indexes in all cases but in some special cases we can. Could you help us to understand those special cases??

Thanks
Chinni
Tom Kyte
May 26, 2009 - 9:37 am UTC

1) As depicted in that chapter, a b*tree index is a "tree data structure", the leaf blocks contain data sorted by index key.


If you insert the values from 1 to 500,000 then on leaf block #1 (the furthest "left" in the index tree) you would expect to find the key values 1, 2, 3, ... N (where N is the most index keys we can fit on the index leaf block).

Now, if you delete the even keys, leaf block #1 would be left with 1, 3, 5, 7, ..... N (assuming N was odd). And leaf block #2 would have odd keys as well - monotonically increasing in value - all of the way over to the right hand side of the index tree where the last values would be "... 499,995, 499,997, 499,999"

Now, if you were populating this column via a sequence - the sequence.nextval you would expect would be 500,001 - can that value possibly fit on leaf block #1? (no, it cannot, only low values like 1, or 50, or 42 could fit on leaf block #1, leaf block #1 only has "low numbers"). Could it fit on leaf block #2? No - same reason. The only block it could fit on - because the index data is stored sorted by index key - would be the right most block. That 50% of free space you just made - will never be reused IN THIS PARTICULAR CASE - BECAUSE YOU POPULATE WITH AN INCREASING VALUE. (This is like an index on a surrogate key - a good reason for not using surrogates when a true natural key exists - or attributes populated by SYSDATE/SYSTIMESTAMP)


Now, forget that preceding paragraph. If you were populating this column with numbers randomly distributed between 1 and 500,000 - then the next insert you dod would of course be able to reuse that 50% free space easily - we'd find the block that new row should live on in the index and place it there. The index space would be reused naturally (this is like an index on LAST_NAME - your last_names are randomly distributed and arrive randomly).


consider:

ops$tkyte%ORA10GR2> create table t
  2  as
  3  select level+0.1 l
  4    from dual
  5  connect by level <= 500000
  6  /

Table created.

<b>so, we have 500,000 rows - 1.1, 2.1, 3.1, ... 500000.1</b>

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index t_idx on t(l);
Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> analyze index t_idx validate structure;
Index analyzed.

ops$tkyte%ORA10GR2> select lf_rows, del_lf_rows, lf_blks, (lf_rows-del_lf_rows)/lf_blks avg_rows_per_block from index_stats;

   LF_ROWS DEL_LF_ROWS    LF_BLKS AVG_ROWS_PER_BLOCK
---------- ----------- ---------- ------------------
    500000           0       1184         422.297297

<b>and we are using 1,184 blocks - about 422 leaf rows/block</b>

ops$tkyte%ORA10GR2> delete from t where mod(l-0.1,2) = 0;
250000 rows deleted.

ops$tkyte%ORA10GR2> commit;
Commit complete.

<b>get rid of 'even' rows (removing the .1 I added)</b>

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

Index analyzed.

ops$tkyte%ORA10GR2> select lf_rows, del_lf_rows, lf_blks, (lf_rows-del_lf_rows)/lf_blks avg_rows_per_block from index_stats;

   LF_ROWS DEL_LF_ROWS    LF_BLKS AVG_ROWS_PER_BLOCK
---------- ----------- ---------- ------------------
    500000      250000       1184         211.148649

<b>lots of free space...</b>

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t
  2  select (2*level)+0.2 l
  3    from dual
  4  connect by level <= 250000;

250000 rows created.

<b>note that this inserts 2.2, 4.2, 6.2, ... 500000.2

They are not exactly the same numbers as before, just in the range general range.  This is why I used .1 above - to make the numbers have a certain width in the index structure (we need more space to store 1.1 than to store 1.0 - so by making all of the numbers have the same number of digits - we know the space we just freed up is big enough - AND the numbers are not exactly the same - showing they don't have to be *exactly* the same - they just need to fall into the same 'sort' range to go onto a block)</b>

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

Index analyzed.

ops$tkyte%ORA10GR2> select lf_rows, del_lf_rows, lf_blks, (lf_rows-del_lf_rows)/lf_blks avg_rows_per_block from index_stats;

   LF_ROWS DEL_LF_ROWS    LF_BLKS AVG_ROWS_PER_BLOCK
---------- ----------- ---------- ------------------
    500000           0       1184         422.297297

<b>index did NOT grow at all - we reused all of that space...  Now get rid of odd numbers...</b>


ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> delete from t where mod(l-0.1,2) = 1;

250000 rows deleted.

ops$tkyte%ORA10GR2> commit;

Commit complete.

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

Index analyzed.

ops$tkyte%ORA10GR2> select lf_rows, del_lf_rows, lf_blks, (lf_rows-del_lf_rows)/lf_blks avg_rows_per_block from index_stats;

   LF_ROWS DEL_LF_ROWS    LF_BLKS AVG_ROWS_PER_BLOCK
---------- ----------- ---------- ------------------
    500000      250000       1184         211.148649

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t
  2  select (level+500000)+0.3 l
  3    from dual
  4  connect by level <= 250000;

250000 rows created.

<b>and this time, pretend we were refilling the table with a sequence - so we'll start at 500,000 this time and go up...</b>

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

Index analyzed.

ops$tkyte%ORA10GR2> select lf_rows, del_lf_rows, lf_blks, (lf_rows-del_lf_rows)/lf_blks avg_rows_per_block from index_stats;

   LF_ROWS DEL_LF_ROWS    LF_BLKS AVG_ROWS_PER_BLOCK
---------- ----------- ---------- ------------------
    749906      249906       1716         291.375291


<b>see how that insert caused the index to grow - that is because we put all of the new values on the "right hand" side - we could not put them on the "left hand" side since they were large values and the index has to store the data sorted...</b>






2) hopefully, the above just demonstrated that.

indexes on last_name - data arrives randomly, you fire someone with the last name starting with K, you hire someone with a name that starts with K tomorrow - space is always going to be reused. These indexes will always be 20-40% empty on average (due to a leaf block filling up and splitting) but that space is effectively used and reused over and over - these indexes will grow to a certain size and stop growing. If you rebuild them, the only thing that will happen is they'll spend the next couple of hours/days/weeks getting BIG again (you will NOT reclaim any space on an ongoing basis - they will get to their steady state size and stay there)


indexes on sequence/date populated columns where you delete SOME but not ALL of the old rows - they will create space on leaf blocks that cannot be reused by subsequent inserts (the values are always increasing, they cannot reuse space on leaf block #1, only low values could do that and we don't insert those ever again). These indexes might need to be coalesced (not rebuilt) from time to time in order to "re-densify" the left hand side. If you purge large contiguous ranges of data however (not sparsely like I did) then this does NOT apply - consider:


ops$tkyte%ORA10GR2> delete from t where l <= 500000;

249999 rows deleted.

ops$tkyte%ORA10GR2> commit;

Commit complete.

<b>I just emptied out the "left hand side", the leaf blocks are empty - no stragglers this time</b>

ops$tkyte%ORA10GR2> insert into t
  2  select (level+750000)+0.4 l
  3  from dual
  4  connect by level <= 500000;

500000 rows created.

<b>add 500,000 more rows, all the values are larger than the existing values in the table</b>

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

Index analyzed.

ops$tkyte%ORA10GR2> select lf_rows, del_lf_rows, lf_blks, (lf_rows-del_lf_rows)/lf_blks avg_rows_per_block from index_stats;

   LF_ROWS DEL_LF_ROWS    LF_BLKS AVG_ROWS_PER_BLOCK
---------- ----------- ---------- ------------------
    771523       21522       1750            428.572

<b>and the index did not really grow - we just moved the empty leaf blocks from the left and put them on the right hand side</b>



So, the indexes that would be candidates for COALESCING might be indexes on sequence and date populated fields whereby you delete some but not all of the old ranges of data.

John Carew, March 21, 2010 - 8:31 pm UTC

Hello Sir;

In above, someone mentioned that, he is deleting a lot of rows everyday and
you suggested him to coalesce rather than rebuild.
You also mentioned that coalesce will put the now empty blocks to freelist.

What I want to ask is,
1-)What is the advantage of using coalesce interms of performance?
2-)When we delete a lot of rows, it is likely that most of the blocks will appear in freelist.
So, so why coalesce?
3-)I saw the illustration of coalesce with pictures from the oracle docs.
The nowempty block removed from the tree after coalesce.
Before coalesce 4 leaf blocks, after coalesce 3 leaf blocks.
Does the free leaf block put into end of the tree?
I mean.
where does the freelist stored in index architecture?

Tom Kyte
March 22, 2010 - 9:20 am UTC

1)coalese is 100% online.

a rebuild - even an online rebuild - is not 100% online. a rebuild locks the index during the rebuild by default. an online rebuild locks the index at the beginning and ending - causing locking issues (it need to have zero transactions to start and zero to end - it will pause things, coalesce does not)

rebuild needs approximately 2x the space, you have two copies of the index at some point.

also, rebuild drops the old segment - think about what happens if you

a) start a query, it is using index I
b) rebuild I - it drops the old I
c) the query you started in (a) is still running - but the index segment it was using and HAS to use (it cannot switch index structures in the middle of an operation like that) doesn't exist anymore. You will hit ora-8103's many times. Coalesce will not.

2) if you delete all of the leaf rows on a leaf block, yes, they will. But typically - your deletes won't do that.

If you delete where last_name like 'A%' - you wouldn't need to coalesce, because you would delete a ton of contiguous values.

If you delete where status = 'fired' - you might need to coalesce an index on last_name since you probably just deleted one record with 'A', one with 'B' and so on....


3) the empty leaf block is put on the freelist and isn't in the index structure anymore. It is still part of the index segment, you just won't see it in the leaf blocks or branch blocks - it'll only be on the freelist.

think of a freelist as list of blocks being pointed to. They just exist in the segment.

A reader, May 17, 2010 - 6:23 pm UTC

Thanks sir;

Does coalescing an index has an impact on the response time of my sql queries?
Tom Kyte
May 24, 2010 - 9:17 am UTC

yes, it might make them faster, it might not make them faster, during the coalesce operation itself it might make them slower, it might not make them slower

that is - it depends...

Probably, it will make no difference to most queries, and it may have a dramatic impact on a few queries (in a positive way)

Decrese the size of index

Mansi, April 19, 2022 - 1:13 pm UTC

Hi There,

Isn't it, rebuilding the index will help in claiming space.
One index in our system was of 690 GB and the size decresed to 510 after rebuild.
Connor McDonald
April 20, 2022 - 4:32 am UTC

Rebuilding *might* make an index small, it might make it bigger.

SQL> create table t (r1 int, r2 int);

Table created.

SQL>
SQL> create index ix1 on t ( r1);

Index created.

SQL> create index ix2 on t (r2 );

Index created.

SQL>
SQL> insert into t
  2  select rownum r1, rownum r2
  3  from dual
  4  connect by level <= 100000;

100000 rows created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T');

PL/SQL procedure successfully completed.

SQL> select index_name, leaf_blocks
  2  from user_indexes
  3  where table_name = 'T';

INDEX_NAME                     LEAF_BLOCKS
------------------------------ -----------
IX1                                    199
IX2                                    199

SQL>
SQL> update t
  2  set r1 = null
  3  where rownum <= 50000;

50000 rows updated.

SQL>
SQL> alter index ix1 rebuild;

Index altered.

SQL> alter index ix2 rebuild;

Index altered.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T');

PL/SQL procedure successfully completed.

SQL> select index_name, leaf_blocks
  2  from user_indexes
  3  where table_name = 'T';

INDEX_NAME                     LEAF_BLOCKS
------------------------------ -----------
IX1                                    112         <<<< smaller
IX2                                    222      <<<< bigger


Index Rebuild

Mansi, April 21, 2022 - 2:53 pm UTC

Why idx2 size has increased? Change is made only r1 and its idx1 Index.
Help yo understand this.
Connor McDonald
April 27, 2022 - 2:33 am UTC

The index starts with pctfree = 10.

When load an index with ascending data, we let the blocks fill completely, because we assume you'll always be ascending.

When you rebuild it - we put that 10% free space back in.


Index Rebuild

Minh, October 28, 2022 - 3:24 pm UTC

Hi,

I have heard of the following practice being implemented.

Someone wants to keep the size of their index tablespaces constant. So they have two set of index tablespaces, all with the same size, no autoextend. When one tablespace is almost full, they rebuild the indexes to another tablespace.

I don't know how many percent of space they save and whether or not the indexes will grow indefinitely if they don't recreate it sometimes.

Will this situation be justified if they have many "sweeping indexes"?
Connor McDonald
October 31, 2022 - 4:03 am UTC

Rebuilding/reorging something from one tspace to another makes sense because typically when you go to "fresh" tablespace you build from the "bottom up".

But thats a different question to whether they should be rebuilding at all.

There are very few times when rebuilds are needed.


More to Explore

DBMS_JOB

More on PL/SQL routine DBMS_JOB here