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

Home -> Community -> Usenet -> c.d.o.server -> Re: Separating data, index objects

Re: Separating data, index objects

From: Richard Foote <richard.foote_at_bigpond.nospam.com>
Date: Thu, 07 Jul 2005 12:37:09 GMT
Message-ID: <Vl9ze.18895$oJ.17695@news-server.bigpond.net.au>


"Joel Garry" <joel-garry_at_home.com> wrote in message news:1120692408.099052.209860_at_g49g2000cwa.googlegroups.com...
> >So even in a *single* user system, separating indexes and tables provides
>>*no* performance benefit. In theory or not.
>
> OK, I don't recall the older threads response to my question.
>
> Given: Newly formatted large disk. Batch job, single user, full table
> scan (ie, end of year report). One table in the tablespace on the
> device. One index, not used here. Low pctfree. Fair-sized multiblock
> read count. Continuously increasing primary key. OLTP system, in
> general.
>
> Are you saying there will be no performance difference based on these
> two possibilities:
>
> 1. The table was loaded by an import, so has all blocks contiguous
> (well, in a few chunks from what I've seen), then the index was created
> in another tablespace.
>
> 2. The table was loaded by a process that added each row and its index
> within discrete transactions, and the index is in the same tablespace.
>
> It would seem to me #1 would have a higher desired information density,
> or at least the multiblock read would have a higher chance of not
> wasting time on an index block.
>

Hi Joel,

I'm not entirely sure what you mean by "at least the multiblock read would have a higher chance of not wasting time on an index block".

How does a multiblock read waste time on an index block ? Are you suggesting that the table would be better of with all it's extents contiguous and not have index extents in between them somehow ?. Perhaps be sure and fit the table in one extent ? If so, I would suggest the overall difference would be minimal.

Perhaps those DBAs out there who maintain single user production databases with only a single table and index per disk might like to comment :)

Cheers

Richard Received on Thu Jul 07 2005 - 07:37:09 CDT

Original text of this message

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