Index Blocks/Extent Usage

[AJW_ID01]

Thanks for all your thoughts and comments folks. Got this sorted now.

I just did a rebuild during this morning, noparallel, and set extent sizes to 1M - of which there is only one extent allocated following the build!

:-)

T.

[DaPi]

Originally posted by TomazZ
Rebuild will use existing index, which is faster in many situations.

Someone (I think slimdave) told me recently that if the index is marked unusable, then it has to be rebuilt by reading the table.

[abhaysk]

Originally posted by DaPi
Someone (I think slimdave) told me recently that if the index is marked unusable, then it has to be rebuilt by reading the table.

To add, even if Index is usable, & if index is built online, it will scan whole table.

So, wass the point in Rebild with out online clause, and use Index scan, its any way going to hinder the TRANSACTIONs starting hence forth or the other way round is as well true.

Abhay.

[marist89]

Originally posted by slimdave
On a side issue, a more robust process would be to make the index unusable and set skip_unusable_indexes = true, then rebuild them after the batch process completes

Why go through the hassle of making an index unusable, having slow queries until you rebuild it, and rebuild it, and analyze it? Sounds like a lot of work to me. In this situation, I would just:

ALTER INDEX xyz REBUILD ... ONLINE;

[pando]

if we have enterprise edition we can rebuild online otherwise use the poor man´s solution

[AJW_ID01]

At the time the indexes are rebuilt (from 0200 hrs) we have no queries accessing the system. [Usage doesn't resume until around 9 a.m.)

As the indexes are actually bitmap, I believe the option to rebuild 'online' is unavailable.

We used to have the indexes present when running the batch, in the form of b-tree indexes. Unfortunately, as the user daytime queries were so ad-hoc and trying to juggle variants of concatenated indexes to suit everyone was too much of a headache and performance remained dire overall - we elected to transform to bitmaps.

These are much smaller than the b-trees, performance gained substantially from multitudinous bitmap merges for almost everyone. The only downside unfortunately was that the overnight batch with bitmap objects in place upped the batch time from around 6 hours to 6 days or more!!!

Hence the drop and recreate strategy.

Of course, you may not have wanted to know this - but I felt like filling in the gaps in the scenario.

:-)

Right now I'm going to adopt a drop, create in parallel (to avoid long create times but with the detriment of extra space usage), followed by a rebuild noparallel (which is reasonably quick and eliminates the extra space overhead from earlier), concluding with a quick analyze!

Unless of course I'm overlooking something?

Is there a better stategy that I might be missing?

- Tony.

[slimdave]

Originally posted by marist89
Why go through the hassle of making an index unusable, having slow queries until you rebuild it, and rebuild it, and analyze it? Sounds like a lot of work to me. In this situation, I would just:

ALTER INDEX xyz REBUILD ... ONLINE;

Problem is that with those bitmap indexes the insert/update/delete is nightmarishly slow - it's usually better to make 'em unusable and rebuild them after the operation.

On a different note, a rebuild on a usable index will read the index, not the table, except where you use the "compute statistics" clause - that makes it read the table not the old index, i believe.

[abhaysk]

Originally posted by slimdave
On a different note, a rebuild on a usable index will read the index, not the table, except where you use the "compute statistics" clause - that makes it read the table not the old index, i believe.

Try Online clause (b*tree), it also goes for table scan instead.

Abhay.