Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: should you seperate indexes from tables in seperate datafiles?

Re: should you seperate indexes from tables in seperate datafiles?

From: Tanel Poder <tanel.poder.003_at_mail.ee>
Date: Tue, 15 Jul 2003 18:31:22 +0300
Message-Id: <25929.337834@fatcity.com>


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.. Received on Tue Jul 15 2003 - 10:31:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US