Re: re Rebuilding Indexes in Oracle Apps -- an update
Jared, I don't see how index skip scans could benefit more from a rebuild than from coalesce (providing the index height remains the same). Skip scan doesn't scan the whole index like FFS does, it just does several scans for each value set in beginning of concatenated index (+some more mechanisms). Maybe I'm missing something here, what did you have in mind? Tanel. If the index is based simply on the unique key, and for some reason you are using index_ffs on it, then rebuilding will cut down the number of scanned blocks. It would be interesting to see how skip scans are affected by this as well. Jared Hemant K Chitale [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/01/2003 12:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: re Rebuilding Indexes in Oracle Apps -- an updateRichard et al,{for those who've been following the thread on Rebuilding Indexes ...}I've just been reading the AskTom thread on rebuilding indexesat http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:6601312252730and picked on the important line"Coalesce... reclaim the free space from mostly empty index leaf blocks that will not be reused otherwise due to your increasing sequence. "Richard has also pointed COALESCE as a better option.COALESCE would be a better option than REBUILD for Indexes onmonotonically increasing sequences where older values are purged periodically.Hemant K ChitaleOracle 9i Database Administrator Certified ProfessionalMy personal web site is : http://hkchital.tripod.com-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Hemant K ChitaleINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California-- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: re Rebuilding Indexes in Oracle Apps -- an update
Now that I think about it, you're probably right. I'll test it anyway, I like to see numbers. :) Jared Tanel Poder [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/04/2003 10:04 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: re Rebuilding Indexes in Oracle Apps -- an update Jared, I don't see how index skip scans could benefit more from a rebuild than from coalesce (providing the index height remains the same). Skip scan doesn't scan the whole index like FFS does, it just does several scans for each value set in beginning of concatenated index (+some more mechanisms). Maybe I'm missing something here, what did you have in mind? Tanel. If the index is based simply on the unique key, and for some reason you are using index_ffs on it, then rebuilding will cut down the number of scanned blocks. It would be interesting to see how skip scans are affected by this as well. Jared Hemant K Chitale [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/01/2003 12:34 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: re Rebuilding Indexes in Oracle Apps -- an update Richard et al, {for those who've been following the thread on Rebuilding Indexes ...} I've just been reading the AskTom thread on rebuilding indexes at http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:6601312252730 and picked on the important line Coalesce... reclaim the free space from mostly empty index leaf blocks that will not be reused otherwise due to your increasing sequence. Richard has also pointed COALESCE as a better option. COALESCE would be a better option than REBUILD for Indexes on monotonically increasing sequences where older values are purged periodically. Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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).
RE: re Rebuilding Indexes in Oracle Apps -- an update
COALESCE would be a better option than REBUILD for Indexes on monotonically increasing sequences where older values are purged periodically. Unless you happen to be doing index_ffs on that particular index, in which case a rebuild *may* be in order. Rebuilding the index may cause insert performance problems for a time due to block splits. If the index is a composite index with some other non-unique value(s) making up the index, a higher than normal number of block splits may be in your future. I haven't tested the composite index yet, but this seems reasonable. If the index is based simply on the unique key, and for some reason you are using index_ffs on it, then rebuilding will cut down the number of scanned blocks. It would be interesting to see how skip scans are affected by this as well. Jared Hemant K Chitale [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/01/2003 12:34 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: re Rebuilding Indexes in Oracle Apps -- an update Richard et al, {for those who've been following the thread on Rebuilding Indexes ...} I've just been reading the AskTom thread on rebuilding indexes at http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:6601312252730 and picked on the important line Coalesce... reclaim the free space from mostly empty index leaf blocks that will not be reused otherwise due to your increasing sequence. Richard has also pointed COALESCE as a better option. COALESCE would be a better option than REBUILD for Indexes on monotonically increasing sequences where older values are purged periodically. Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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).
RE: re Rebuilding Indexes in Oracle Apps -- an update
Richard et al, {for those who've been following the thread on Rebuilding Indexes ...} I've just been reading the AskTom thread on rebuilding indexes at http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:6601312252730 and picked on the important line Coalesce... reclaim the free space from mostly empty index leaf blocks that will not be reused otherwise due to your increasing sequence. Richard has also pointed COALESCE as a better option. COALESCE would be a better option than REBUILD for Indexes on monotonically increasing sequences where older values are purged periodically. Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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).