Hi! > > During an indexed query on a single table the index will be > > accessed, then the table, then the index,then the table, > > then the index,then the table then the index,then the table.
Actually, if you think a little more, then you see that physical IO doesn't occur like you described. If you got non-unique index range scan first time for example, you got index-access for index header block, then access to branch block in different location on disk (first branch block can be in completely different location on disk, dependent on extent sizes, number of datafiles in tablespace, datafile sizes and extensibility, even cardinality of index + other factors). Then next level branch block is probably in different location on disk again etc..) So, when you eventually get to leaf block (another physical IO), you have a number of entries (with rowids) there which match your where condition, and a physical IO has to be done for each block where we got matching rows (here comes the clustering factor into play - if matching rows are spread all over different blocks, we got lots of IOs to do). So, it's more like index-index-index-index IO + table-table-table-table IO. Next time there's hopefully no physical IO needed for index root block and maybe some other blocks as well, dependion on IO rate and buffer cache size etc.. And I believe, when index range scan is done, Oracle can optimize it's IO to batches, not having to initiate separate IO requests for every single row scanned.... but other wiser people might want to comment on it.. Tanel. -- 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).
