Re: Re: When does Oracle use 'Index Fast Scan'
I know when oracle uses a fast full scan. Its the full scan that does 1 I/O at a time. I rarely see oracle using it and when it does, it generally means my table(s) aren't properly analyzed. From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: When does Oracle use 'Index Fast Scan'
btw, in many cases range scan is faster than a fast full scan. Range scan recursively hits the nodes that are needed and skips the ones that are not. So it reads less blocks. So if you are looking for a 'range' or a specific value, range scan beats fast full scan most of the time. Less Logical and Physical I/Os. test it and hint your queries From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: When does Oracle use 'Index Fast Scan'
One situation I can think of where a (non-fast) full index scan can be helpful is when the index contains all the columns needed for the query, the query requires all the rows of the table, and the query requires the results to be sorted according to the index. This way, fast full index scan may be slower because you need to sort the rows after retrieving the blocks, whereas the non-fast full index scan does not. Regards, Dave [EMAIL PROTECTED] wrote: I know when oracle uses a fast full scan. Its the full scan that does 1 I/O at a time. I rarely see oracle using it and when it does, it generally means my table(s) aren't properly analyzed. From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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: When does Oracle use 'Index Fast Scan'
This is where the access time of your disks (or SAN) makes a difference. If your disks have really fast access time, then a random-access pattern would not cause much performance degradation and so a range scan would not be slow at all, even though it's traversing the b-tree index structure. If you're only striping together disks with relatively slow access time (e.g. using a striped IDE disk array), then you have high throughput but not that fast an access time. In this case, fast full index scan would be much faster than an index range scan because the fast full scan reads the blocks sequentially and a sequential disk I/O requires only positioning the head once (assuming the disk is not fragmented). The rest of the time depends on the throughput. If you stripe together a large enough number of IDE disks, then your throughput is great but your access time is still the access time of a single IDE drive which is not that fast. This is assuming you need to do a physical I/O to obtain the blocks. Of course, if the blocks already reside in the buffer cache, then it's a different story. Regards, Dave [EMAIL PROTECTED] wrote: btw, in many cases range scan is faster than a fast full scan. Range scan recursively hits the nodes that are needed and skips the ones that are not. So it reads less blocks. So if you are looking for a 'range' or a specific value, range scan beats fast full scan most of the time. Less Logical and Physical I/Os. test it and hint your queries From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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: When does Oracle use 'Index Fast Scan'
Another situation where index full scans might be handy, would be where hash joins are disabled and sorted output can be used for fast sort-merge join. Btw, multiblock reads are available for regular index range and full scan under some specific conditions as well - I'm talking about readahead and parameter _non_contiguous_multiblock_read for example. However, in my brief tests I've not managed to see this kind of behaviour yet. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 27, 2004 6:04 PM One situation I can think of where a (non-fast) full index scan can be helpful is when the index contains all the columns needed for the query, the query requires all the rows of the table, and the query requires the results to be sorted according to the index. This way, fast full index scan may be slower because you need to sort the rows after retrieving the blocks, whereas the non-fast full index scan does not. Regards, Dave [EMAIL PROTECTED] wrote: I know when oracle uses a fast full scan. Its the full scan that does 1 I/O at a time. I rarely see oracle using it and when it does, it generally means my table(s) aren't properly analyzed. From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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). -- 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).
Re: When does Oracle use 'Index Fast Scan'
This is where the access time of your disks (or SAN) makes a difference. If your disks have really fast access time, then a random-access pattern would not cause much performance degradation and so a range scan would not be slow at all, even though it's traversing the b-tree index structure. If you're only striping together disks with relatively slow access time (e.g. using a striped IDE disk array), then you have high throughput but not that fast an access time. In this case, fast full index scan would be much faster than an index range scan because the fast full scan reads the blocks sequentially and a sequential disk I/O requires only positioning the head once (assuming the disk is not fragmented). The rest of the time depends on the throughput. If you stripe together a large enough number of IDE disks, then your throughput is great but your access time is still the access time of a single IDE drive which is not that fast. This is assuming you need to do a physical I/O to obtain the blocks. Of course, if the blocks already reside in the buffer cache, then it's a different story. Regards, Dave [EMAIL PROTECTED] wrote: btw, in many cases range scan is faster than a fast full scan. Range scan recursively hits the nodes that are needed and skips the ones that are not. So it reads less blocks. So if you are looking for a 'range' or a specific value, range scan beats fast full scan most of the time. Less Logical and Physical I/Os. test it and hint your queries From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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: When does Oracle use 'Index Fast Scan'
i found numerous cases(I dont have them in front of me) when fast full scan incurred far more logical I/Os than an index range scan. I found this particularly for oltp type get 10 records transactions. However, I forced an index_ffs once and it increased my logical I/Os by 30% but decreased my response time by about 50%. now this is non-scalable. Must be my disk access speeds as you said. It was for a batch process. unfortunately I didnt hold onto my cases. any comments on the 'full scan'. I rarely have seen oracle make good use of this type of index scan. From: David Hau [EMAIL PROTECTED] Date: 2004/01/27 Tue AM 11:14:27 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' This is where the access time of your disks (or SAN) makes a difference. If your disks have really fast access time, then a random-access pattern would not cause much performance degradation and so a range scan would not be slow at all, even though it's traversing the b-tree index structure. If you're only striping together disks with relatively slow access time (e.g. using a striped IDE disk array), then you have high throughput but not that fast an access time. In this case, fast full index scan would be much faster than an index range scan because the fast full scan reads the blocks sequentially and a sequential disk I/O requires only positioning the head once (assuming the disk is not fragmented). The rest of the time depends on the throughput. If you stripe together a large enough number of IDE disks, then your throughput is great but your access time is still the access time of a single IDE drive which is not that fast. This is assuming you need to do a physical I/O to obtain the blocks. Of course, if the blocks already reside in the buffer cache, then it's a different story. Regards, Dave [EMAIL PROTECTED] wrote: btw, in many cases range scan is faster than a fast full scan. Range scan recursively hits the nodes that are needed and skips the ones that are not. So it reads less blocks. So if you are looking for a 'range' or a specific value, range scan beats fast full scan most of the time. Less Logical and Physical I/Os. test it and hint your queries From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web
Re: Re: When does Oracle use 'Index Fast Scan'
my question pertains to regular 'index full scans' NOT index fast full scans. any ideas? I rarely ever find this to be an optimal index access method for anything. From: Tanel Poder [EMAIL PROTECTED] Date: 2004/01/27 Tue AM 11:19:27 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Another situation where index full scans might be handy, would be where hash joins are disabled and sorted output can be used for fast sort-merge join. Btw, multiblock reads are available for regular index range and full scan under some specific conditions as well - I'm talking about readahead and parameter _non_contiguous_multiblock_read for example. However, in my brief tests I've not managed to see this kind of behaviour yet. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 27, 2004 6:04 PM One situation I can think of where a (non-fast) full index scan can be helpful is when the index contains all the columns needed for the query, the query requires all the rows of the table, and the query requires the results to be sorted according to the index. This way, fast full index scan may be slower because you need to sort the rows after retrieving the blocks, whereas the non-fast full index scan does not. Regards, Dave [EMAIL PROTECTED] wrote: I know when oracle uses a fast full scan. Its the full scan that does 1 I/O at a time. I rarely see oracle using it and when it does, it generally means my table(s) aren't properly analyzed. From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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). -- 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
Re: Re: When does Oracle use 'Index Fast Scan'
Yes, and my reply was about regular index full scans, according to your question. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 27, 2004 6:59 PM my question pertains to regular 'index full scans' NOT index fast full scans. any ideas? I rarely ever find this to be an optimal index access method for anything. From: Tanel Poder [EMAIL PROTECTED] Date: 2004/01/27 Tue AM 11:19:27 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Another situation where index full scans might be handy, would be where hash joins are disabled and sorted output can be used for fast sort-merge join. Btw, multiblock reads are available for regular index range and full scan under some specific conditions as well - I'm talking about readahead and parameter _non_contiguous_multiblock_read for example. However, in my brief tests I've not managed to see this kind of behaviour yet. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 27, 2004 6:04 PM One situation I can think of where a (non-fast) full index scan can be helpful is when the index contains all the columns needed for the query, the query requires all the rows of the table, and the query requires the results to be sorted according to the index. This way, fast full index scan may be slower because you need to sort the rows after retrieving the blocks, whereas the non-fast full index scan does not. Regards, Dave [EMAIL PROTECTED] wrote: I know when oracle uses a fast full scan. Its the full scan that does 1 I/O at a time. I rarely see oracle using it and when it does, it generally means my table(s) aren't properly analyzed. From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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
Re: When does Oracle use 'Index Fast Scan'
Of course you also need to consider the application. Will there be large number of users? Does this query run often, or just occasionally? Scalability comes into play, and a method that requires fewer oracle resources ( latches ) is preferable, if possible. Jared David Hau [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/27/2004 08:54 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: When does Oracle use 'Index Fast Scan' This is where the access time of your disks (or SAN) makes a difference. If your disks have really fast access time, then a random-access pattern would not cause much performance degradation and so a range scan would not be slow at all, even though it's traversing the b-tree index structure. If you're only striping together disks with relatively slow access time (e.g. using a striped IDE disk array), then you have high throughput but not that fast an access time. In this case, fast full index scan would be much faster than an index range scan because the fast full scan reads the blocks sequentially and a sequential disk I/O requires only positioning the head once (assuming the disk is not fragmented). The rest of the time depends on the throughput. If you stripe together a large enough number of IDE disks, then your throughput is great but your access time is still the access time of a single IDE drive which is not that fast. This is assuming you need to do a physical I/O to obtain the blocks. Of course, if the blocks already reside in the buffer cache, then it's a different story. Regards, Dave [EMAIL PROTECTED] wrote: btw, in many cases range scan is faster than a fast full scan. Range scan recursively hits the nodes that are needed and skips the ones that are not. So it reads less blocks. So if you are looking for a 'range' or a specific value, range scan beats fast full scan most of the time. Less Logical and Physical I/Os. test it and hint your queries From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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
Re: Re: When does Oracle use 'Index Fast Scan'
ive found that index_ffs typically incur higher logical I/Os that index range scans. so its not just access speeds. From: David Hau [EMAIL PROTECTED] Date: 2004/01/27 Tue AM 11:54:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' This is where the access time of your disks (or SAN) makes a difference. If your disks have really fast access time, then a random-access pattern would not cause much performance degradation and so a range scan would not be slow at all, even though it's traversing the b-tree index structure. If you're only striping together disks with relatively slow access time (e.g. using a striped IDE disk array), then you have high throughput but not that fast an access time. In this case, fast full index scan would be much faster than an index range scan because the fast full scan reads the blocks sequentially and a sequential disk I/O requires only positioning the head once (assuming the disk is not fragmented). The rest of the time depends on the throughput. If you stripe together a large enough number of IDE disks, then your throughput is great but your access time is still the access time of a single IDE drive which is not that fast. This is assuming you need to do a physical I/O to obtain the blocks. Of course, if the blocks already reside in the buffer cache, then it's a different story. Regards, Dave [EMAIL PROTECTED] wrote: btw, in many cases range scan is faster than a fast full scan. Range scan recursively hits the nodes that are needed and skips the ones that are not. So it reads less blocks. So if you are looking for a 'range' or a specific value, range scan beats fast full scan most of the time. Less Logical and Physical I/Os. test it and hint your queries From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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). -- Please see the official ORACLE-L FAQ: http
Re: When does Oracle use 'Index Fast Scan'
An index fast full scan and an index full scan both need to access all the blocks of an index. The only difference between them is that the index_ffs accesses the blocks in the order of the blocks (and uses multiblock read), whereas the index_fs accesses the blocks in the order of the b tree index. In terms of # logical I/Os, they are exactly the same. OTOH, an index range scan by definition is a _range_ scan, and need to access only a subset of the blocks of an index. Because of this, it'll have a lower # logical I/Os than an index_ffs. Regards, Dave [EMAIL PROTECTED] wrote: ive found that index_ffs typically incur higher logical I/Os that index range scans. so its not just access speeds. From: David Hau [EMAIL PROTECTED] Date: 2004/01/27 Tue AM 11:54:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' This is where the access time of your disks (or SAN) makes a difference. If your disks have really fast access time, then a random-access pattern would not cause much performance degradation and so a range scan would not be slow at all, even though it's traversing the b-tree index structure. If you're only striping together disks with relatively slow access time (e.g. using a striped IDE disk array), then you have high throughput but not that fast an access time. In this case, fast full index scan would be much faster than an index range scan because the fast full scan reads the blocks sequentially and a sequential disk I/O requires only positioning the head once (assuming the disk is not fragmented). The rest of the time depends on the throughput. If you stripe together a large enough number of IDE disks, then your throughput is great but your access time is still the access time of a single IDE drive which is not that fast. This is assuming you need to do a physical I/O to obtain the blocks. Of course, if the blocks already reside in the buffer cache, then it's a different story. Regards, Dave [EMAIL PROTECTED] wrote: btw, in many cases range scan is faster than a fast full scan. Range scan recursively hits the nodes that are needed and skips the ones that are not. So it reads less blocks. So if you are looking for a 'range' or a specific value, range scan beats fast full scan most of the time. Less Logical and Physical I/Os. test it and hint your queries From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California
Re: When does Oracle use 'Index Fast Scan'
i thought an index_fs only read 1 block per i/o? same with an index range scan because they are using random access? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 27, 2004 5:29 PM An index fast full scan and an index full scan both need to access all the blocks of an index. The only difference between them is that the index_ffs accesses the blocks in the order of the blocks (and uses multiblock read), whereas the index_fs accesses the blocks in the order of the b tree index. In terms of # logical I/Os, they are exactly the same. OTOH, an index range scan by definition is a _range_ scan, and need to access only a subset of the blocks of an index. Because of this, it'll have a lower # logical I/Os than an index_ffs. Regards, Dave [EMAIL PROTECTED] wrote: ive found that index_ffs typically incur higher logical I/Os that index range scans. so its not just access speeds. From: David Hau [EMAIL PROTECTED] Date: 2004/01/27 Tue AM 11:54:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' This is where the access time of your disks (or SAN) makes a difference. If your disks have really fast access time, then a random-access pattern would not cause much performance degradation and so a range scan would not be slow at all, even though it's traversing the b-tree index structure. If you're only striping together disks with relatively slow access time (e.g. using a striped IDE disk array), then you have high throughput but not that fast an access time. In this case, fast full index scan would be much faster than an index range scan because the fast full scan reads the blocks sequentially and a sequential disk I/O requires only positioning the head once (assuming the disk is not fragmented). The rest of the time depends on the throughput. If you stripe together a large enough number of IDE disks, then your throughput is great but your access time is still the access time of a single IDE drive which is not that fast. This is assuming you need to do a physical I/O to obtain the blocks. Of course, if the blocks already reside in the buffer cache, then it's a different story. Regards, Dave [EMAIL PROTECTED] wrote: btw, in many cases range scan is faster than a fast full scan. Range scan recursively hits the nodes that are needed and skips the ones that are not. So it reads less blocks. So if you are looking for a 'range' or a specific value, range scan beats fast full scan most of the time. Less Logical and Physical I/Os. test it and hint your queries From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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
Re: When does Oracle use 'Index Fast Scan'
If you look up Logical I/O in the Master Glossary of the Oracle docs, it's defined as: A block read which may or may not be satisfied from the buffer cache. So a logical I/O is always a *block* read. It does not take into acount whether you're doing a multiblock read or not. One reason this is so is because the CBO does not know whether the block is already in the buffer cache or not. If it's already in the cache, then multiblock read is not an issue anymore. The init parameter db_file_multiblock_read_count is what makes the CBO favor fast full index scan over a non-fast scan like full index scan or index range scan. The LIO itself does not take into consideration the aspect of multiblock read. Regards, Dave [EMAIL PROTECTED] wrote: i thought an index_fs only read 1 block per i/o? same with an index range scan because they are using random access? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 27, 2004 5:29 PM An index fast full scan and an index full scan both need to access all the blocks of an index. The only difference between them is that the index_ffs accesses the blocks in the order of the blocks (and uses multiblock read), whereas the index_fs accesses the blocks in the order of the b tree index. In terms of # logical I/Os, they are exactly the same. OTOH, an index range scan by definition is a _range_ scan, and need to access only a subset of the blocks of an index. Because of this, it'll have a lower # logical I/Os than an index_ffs. Regards, Dave [EMAIL PROTECTED] wrote: ive found that index_ffs typically incur higher logical I/Os that index range scans. so its not just access speeds. From: David Hau [EMAIL PROTECTED] Date: 2004/01/27 Tue AM 11:54:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' This is where the access time of your disks (or SAN) makes a difference. If your disks have really fast access time, then a random-access pattern would not cause much performance degradation and so a range scan would not be slow at all, even though it's traversing the b-tree index structure. If you're only striping together disks with relatively slow access time (e.g. using a striped IDE disk array), then you have high throughput but not that fast an access time. In this case, fast full index scan would be much faster than an index range scan because the fast full scan reads the blocks sequentially and a sequential disk I/O requires only positioning the head once (assuming the disk is not fragmented). The rest of the time depends on the throughput. If you stripe together a large enough number of IDE disks, then your throughput is great but your access time is still the access time of a single IDE drive which is not that fast. This is assuming you need to do a physical I/O to obtain the blocks. Of course, if the blocks already reside in the buffer cache, then it's a different story. Regards, Dave [EMAIL PROTECTED] wrote: btw, in many cases range scan is faster than a fast full scan. Range scan recursively hits the nodes that are needed and skips the ones that are not. So it reads less blocks. So if you are looking for a 'range' or a specific value, range scan beats fast full scan most of the time. Less Logical and Physical I/Os. test it and hint your queries From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from
When does Oracle use 'Index Fast Scan'
I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan?
Re: When does Oracle use 'Index Fast Scan'
I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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: When does Oracle use 'Index Fast Scan'
Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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).
