Tim, AFAIK, when doing a rebuild the whole index is read using fast full scan, the branch blocks are just ignored. And sorting does occur, since fast full scan doesn't guarantee all keys to be returned in an order. (yes the keys come in ordered "chunks" because they are stored in ordered way inside leaf blocks, that might help the ordering alghoritm to complete faster).
The benefit comes from that indexes are usually smaller in size than tables because they index only subset of a row, thus there are less IO operations needed when scanning data. Tanel. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, September 08, 2003 4:49 PM Gunnar, Using ALTER SESSION to increase SORT_AREA_SIZE to some obscene number (i.e. several hundred Mb? A Gb?) may help, as long as you are running 8i or below or (if running 9i) the parameter WORKAREA_SIZE_POLICY is set to MANUAL. If WORKAREA_SIZE_POLICY is set to AUTO, then you may have to bump PGA_AGGREGATE_TARGET to something enormous instead... Are you making use of ALTER INDEX REBUILD to rebuild an index, or are you dropping and then creating? ALTER INDEX REBUILD runs much faster than a CREATE INDEX for two reasons: 1) the REBUILD reads the previous index¹s leaf nodes so there is less I/O since it is smaller and 2) there is no sort operation needed since the previous index¹s leaf entries are already sorted= -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
