Index Rebuild – Does it use the Index or the Table ? (Nothing Touches Me) May 15, 2012

A common question that gets asked is does Oracle access the index itself or the parent table during an index rebuild to extract the necessary data for the index ? Thought it might be worth a blog post to discuss.

Now if the index is currently in an UNUSABLE state, then Oracle clearly can’t use the existing index during the index rebuild operation. So we’ll assume both table and index are hunky dory.

OK, to setup the first demo (using 11.2.0.1), we create and populate a table and index with the index being somewhat smaller than the parent table as is most common:

As is common, the table is somewhat larger than the corresponding index.

Now in my first demo, I’m just going to perform a normal offline Index Rebuild. I’ll however trace the session to see what might be happening behind the scenes (the good old alter session set events ‘10046 trace name context forever, level 12′; still does the job). I’ll also flush the buffer cache as well to ensure the trace file shows me which blocks from which object get accessed.

There’s lots of information of interest in the resultant trace file, well, for someone with an unhealthy interest in Oracle indexes anyways :) However, the portion that’s of direct interest in this discussion is to see which object Oracle accesses in order to read the necessary data for the index rebuild. The trace file will contain a relatively extensive section with the following wait events (the following is just a short sample):

We can see that Oracle has accessed the data from the Index itself, using multi-block reads. As the index is the smallest segment that contains the necessary data, Oracle can very efficiently read all the required data (the expensive bit) from the index itself, perform a sort of all the data (as a multi-block read will not return the data in a sorted format) and complete the rebuild process relatively quickly. Note the table is locked throughout the entire index rebuild operation preventing DML operations on the table/index and so for an offline index rebuild, Oracle can access the Index segment without complication.

I’m going to repeat the same process but this time perform an Online index rebuild operation:

This time, the much larger BOWIE parent table has been accessed. So with an Online rebuild, Oracle is forced to use the parent table to access the data for the rebuild operation due to the concurrency issues associated with changes being permitted to the underlying table/index during the rebuild process. So although an online index rebuild has availability advantages, it comes at the cost of having to access the parent table which can result in much additional I/O operations. So if you don’t have availability concerns, an offline index rebuild is probably going to be the more efficient option.

In fact, Oracle can be quite clever in deciding which object to access with an offline rebuild …

In this next example, I’m going to create another table/index, only this time the index is somewhat larger than the parent table. This scenario is less common but certainly possible depending on circumstances:

In this case, the smaller alterative index is actually accessed. So it might not be the table or the index being rebuilt that gets accessed, but the smallest segment that contains the data of interest which in this case is another index entirely.

My final little demo brings me back to the subject of secondary indexes on Index Organized Tables (IOTs) I’ve been recently discussing. In this example, I create an IOT and a much smaller secondary index:

In this case, we see that the much larger IOT PK segment is accessed and not the smaller secondary index. When rebuilding the secondary index of an IOT, Oracle has no choice but to access the parent IOT PK segment itself as of course the secondary index doesn’t contain all the necessary information required for the index rebuild operation. The physical guess component within the secondary index might be stale and the only way for Oracle to determine the correct current address of all the rows is to access the IOT PK segment. This is another disadvantage of secondary indexes associated with IOTs, even offline index rebuilds must access the potentially much larger IOT PK segment in order to ensure the correctness of the physical guess components of the logical rowids.

So the general answer of whether an index rebuild accesses the table or index is that it depends and that it could very well be neither of them …

Related

do you know how Oracle determines which object is the bigger one? When I use your first example (with the big table and the small index) and set the blocks attribute in user_tables with dbms_stats.set_table_stats to a tiny value (100) the rebuild is still accessing the index to gather the relevant data. In the 10046 trace I see some recursive queries on obj$ but there is no information about the sizes – or I don’t see it …

select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#

where this information is extracted.

That said, and in answer to your overall question, no I don’t specifically know how Oracle uses this data and derives its decision on what segment to use. I’ve seen it access the segment header (where it can also determine segment size via the extend map) so it may not be as simple as just looking and trusting the segment stats. An index rebuild is an expensive operation so it will be worth doing the necessary homework beforehand to get it right.

Since the decision (for “offline” rebuilds) is cost-based, you might want to engineer one more example where Oracle decides to do an index full scan on the existing index to rebuild it without doing a sort.

That’s a good idea as I’ve only seen the FFS in my travels. The only problem unfortunately is that the CBO is not being so agreeable :) I’ve just had a play with the examples above this evening and no matter how I fudge the statistics, both segment and system, Oracle will only accept the FFS. Interestingly, the FFS access plan cost is being ignored and is getting or rather inheriting the cheaper cost of a range scan. It’s the “brood parasite” of the Oracle world :) Been unsuccessful in both 10.2 and 11.2 databases.

If I get time tomorrow, I have another play as you’ve got me curious now !!

You may have a problem. The only note that I could find claiming to have produced an index full scan is one I wrote for 9.2.0.4 – but I think that I may have been wrong since I can’t reproduce the effect on versions either side of it (8.1.7.4 and 9.2.0.8) (or any other version, for that matter).

I can get the optimizer to show that it thinks the index full scan is the optimum path, and even to claim that it’s going to use index pre-fetching – and then it does the index fast full scan, showing the cost of the index full scan in the plan output.

You have to be a little careful about checking any claims about index FFS vs. index full scan, of course, since index prefetching on a newly rebuilt index could result in “db file scattered read” waits rather than the “db file sequential read” waits and “db file parallel read” waits that you’re likely to see on a well-exercised index.

Thanks for the detail here. I’ve just hit the issue of rebuilding a 17GB secondary index on a 48GB IOT in order to change the compression.
I expected it to be quick and use the index but was disappointed when it scanned the entire IOT and then had to sort/merge the output.
Surely O should give us the option of doing a fast rebuild with stale physical guesses as an alternative to doing a proper thorough job?
The sort/merge should be unnecessary if it worked with the already sorted index.