Can anybody point me to some documentation about what goes on during a CREATE INDEX and a ALTER INDEX REBUILD? I am trying to figure out if ALTER INDEX REBUILD reads the old index to create the "rebuilt" index, or if the REBUILD goes back to the table to create the "rebuilt" index. I have an idea, but I need definitive proof.
For example, if I have a table (XYZ) that has 1 million rows and an index (XYZ_PK) that is a unique index on that table. If I issue CREATE UNIQUE INDEX xyz_pk ON xyz(x) of course every row in XYZ will be read. However, if I issue ALTER INDEX xyz_pk REBUILD, does Oracle read XYZ_PK and re-organize the blocks or does it read XYZ to build the new index?
Use the ALTER INDEX ... REBUILD statement to reorganize or compact an existing index or to change its storage characteristics. The REBUILD statement uses the existing index as the basis for the new one. All index storage statements are supported, such as STORAGE (for extent allocation), TABLESPACE (to move the index to a new tablespace), and INITRANS (to change the initial number of entries).
ALTER INDEX ... REBUILD is usually faster than dropping and re-creating an index, because this statement uses the fast full scan feature. It reads all the index blocks using multiblock I/O then discards the branch blocks. A further advantage of this approach is that the old index is still available for queries while the rebuild is in progress.
Thus no change to the statistics when the index has been rebuilt.
When you rebuild them then you must know why you're rebuilding them.
You want them to stay "compact" as you planned or make them airy so the levels don't increase that fast...and then add pctfree in that clause............
I'm a JOLE(JavaOracleLinuxEnthusiast)
--- Everything was meant to be---