alter index ... rebuild question

[KenEwald]

What happens to TEMP when I execute the following statement:

ALTER INDEX MY_INDEX REBUILD PARALLEL ONLINE NOLOGGING

Does TEMP need to be big enough to hold all the keys?

[davey23uk]

will be used for sorting, wont store all of it, but your tablespace will need to be big enough to hold the old and current index

[KenEwald]

So if my index is 30gigs and 50% sparse, I should have 30gigs of temp ready?

[OracleDoc]

Originally posted by KenEwald
So if my index is 30gigs and 50% sparse, I should have 30gigs of temp ready?

A 30 gig index???!!! Dude that's insane! Ever heard of partitioning?

[slimdave]

Originally posted by davey23uk
will be used for sorting, wont store all of it, but your tablespace will need to be big enough to hold the old and current index

I'd guess that it would depend on whether the index is already usable or unusable -- I don't think that temp space is involved in rebuilding a usable index.

[abhaysk]

Originally posted by davey23uk
will be used for sorting, wont store all of it

Then how do you expect your index to be perfectly structured?

Abhay.

[abhaysk]

Originally posted by slimdave
I'd guess that it would depend on whether the index is already usable or unusable -- I don't think that temp space is involved in rebuilding a usable index.

Well, still oracle uses temp ts for sorting.

Abhay.

[abhaysk]

Originally posted by OracleDoc
A 30 gig index???!!! Dude that's insane! Ever heard of partitioning?

What if a unique index ( global nonpartitioned index ) is neccesaary for that partitioned table and that key/s is/are not a part of column/s on which table is partitioned?

Abhay.

[DaPi]

Hi Abhay,

Slim's point is that if the index is usable, the rebuild is based on the index, NOT the table. The required data can be read in order by reading the chain of leaf blocks, so no sorting should be required.

[abhaysk]

Originally posted by DaPi
Hi Abhay,

Slim's point is that if the index is usable, the rebuild is based on the index, NOT the table. The required data can be read in order by reading the chain of leaf blocks, so no sorting should be required.

Well i do understand that point and was of that impression too, but you can check the usage of Temp TS while even rebuilding usable index.

Abhay.