RE: index full scan over an index fast full scan in an analytic f
Correct, I did not read the post carefully, thought he was saying Full scan does not return rows in order, so was stating the fact that rows come sorted using Index Full Scan. My fault! Waleed -Original Message- Sent: Saturday, October 25, 2003 5:34 AM To: Multiple recipients of list ORACLE-L Hi! You can't have ascending nor descending indesc fast full scans. This (asc/desc) only works with range or regular full scans. Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Saturday, October 25, 2003 4:59 AM > But the fact is, the access path is still a valid path if the user needs the > data returned ordered on the indexed columns. > Index_asc or Index_desc should do the job without extra cost. > > Waleed > > -Original Message- > Sent: Friday, October 24, 2003 9:39 PM > To: Multiple recipients of list ORACLE-L > function? > > > Tanel > > Tanel Poder wrote: > > As an addition to Vladimir's response: > > I cannot provide you with detailed information -- can only give pointers > to the documentation -- otherwise it would look suspicious :) > > > Full scan will search from index root block using branch blocks to first > > leaf block. And since all leaf blocks have pointers to next and previous > > leaf block in index, sequentially reading only leaf blocks is sufficient > for > > returning all values in index, in order (keys are ordered inside leaf > blocks > > as well). > > > > FFS will scan from index header block (note that index segment header and > > index root block are different ones) up to segment high water mark using > > multiblock reads and ignoring contents of root, branch, bitmap, extent > map, > > freelist group blocks. Rows are returned as they've read from blocks, thus > > no order can be guaranteed. > > "Rows are returned as they've read from blocks, thus no order can be > guaranteed." > > Not rows, but blocks returned as is in order they being read. Keys (rows) > are ordered inside leaf blocks -- as you wrote above. So, inside the blocks > the order is consistent but blocks are 'mixed' whilst read. > > Things (parameters etc.) are changing, as Cary pointed out, principles are > not. > -- > Vladimir Begun > The statements and opinions expressed here are my own and > do not necessarily represent those of Oracle Corporation. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Vladimir Begun > 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: Khedr, Waleed > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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 informa
Re: index full scan over an index fast full scan in an analytic f
Hi! You can't have ascending nor descending indesc fast full scans. This (asc/desc) only works with range or regular full scans. Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Saturday, October 25, 2003 4:59 AM > But the fact is, the access path is still a valid path if the user needs the > data returned ordered on the indexed columns. > Index_asc or Index_desc should do the job without extra cost. > > Waleed > > -Original Message- > Sent: Friday, October 24, 2003 9:39 PM > To: Multiple recipients of list ORACLE-L > function? > > > Tanel > > Tanel Poder wrote: > > As an addition to Vladimir's response: > > I cannot provide you with detailed information -- can only give pointers > to the documentation -- otherwise it would look suspicious :) > > > Full scan will search from index root block using branch blocks to first > > leaf block. And since all leaf blocks have pointers to next and previous > > leaf block in index, sequentially reading only leaf blocks is sufficient > for > > returning all values in index, in order (keys are ordered inside leaf > blocks > > as well). > > > > FFS will scan from index header block (note that index segment header and > > index root block are different ones) up to segment high water mark using > > multiblock reads and ignoring contents of root, branch, bitmap, extent > map, > > freelist group blocks. Rows are returned as they've read from blocks, thus > > no order can be guaranteed. > > "Rows are returned as they've read from blocks, thus no order can be > guaranteed." > > Not rows, but blocks returned as is in order they being read. Keys (rows) > are ordered inside leaf blocks -- as you wrote above. So, inside the blocks > the order is consistent but blocks are 'mixed' whilst read. > > Things (parameters etc.) are changing, as Cary pointed out, principles are > not. > -- > Vladimir Begun > The statements and opinions expressed here are my own and > do not necessarily represent those of Oracle Corporation. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Vladimir Begun > 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: Khedr, Waleed > 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: index full scan over an index fast full scan in an analytic f
But the fact is, the access path is still a valid path if the user needs the data returned ordered on the indexed columns. Index_asc or Index_desc should do the job without extra cost. Waleed -Original Message- Sent: Friday, October 24, 2003 9:39 PM To: Multiple recipients of list ORACLE-L function? Tanel Tanel Poder wrote: > As an addition to Vladimir's response: I cannot provide you with detailed information -- can only give pointers to the documentation -- otherwise it would look suspicious :) > Full scan will search from index root block using branch blocks to first > leaf block. And since all leaf blocks have pointers to next and previous > leaf block in index, sequentially reading only leaf blocks is sufficient for > returning all values in index, in order (keys are ordered inside leaf blocks > as well). > > FFS will scan from index header block (note that index segment header and > index root block are different ones) up to segment high water mark using > multiblock reads and ignoring contents of root, branch, bitmap, extent map, > freelist group blocks. Rows are returned as they've read from blocks, thus > no order can be guaranteed. "Rows are returned as they've read from blocks, thus no order can be guaranteed." Not rows, but blocks returned as is in order they being read. Keys (rows) are ordered inside leaf blocks -- as you wrote above. So, inside the blocks the order is consistent but blocks are 'mixed' whilst read. Things (parameters etc.) are changing, as Cary pointed out, principles are not. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Khedr, Waleed 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).