Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!newscon02.news.prodigy.com!newscon06.news.prodigy.com!prodigy.net!newsfeed-00.mathworks.com!lon-transit.news.telstra.net!lon-in.news.telstra.net!news.telstra.net!news-server.bigpond.net.au!53ab2750!not-for-mail From: "Richard Foote" Newsgroups: comp.databases.oracle.server References: <1119523067.163453.156090@g44g2000cwa.googlegroups.com> <1120490229.230731.282800@f14g2000cwb.googlegroups.com> <1120514204.987114@yasure> <1120692408.099052.209860@g49g2000cwa.googlegroups.com> Subject: Re: Separating data, index objects Lines: 47 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-RFC2646: Format=Flowed; Original Message-ID: Date: Thu, 07 Jul 2005 12:37:09 GMT NNTP-Posting-Host: 60.231.165.91 X-Complaints-To: abuse@bigpond.net.au X-Trace: news-server.bigpond.net.au 1120739829 60.231.165.91 (Thu, 07 Jul 2005 22:37:09 EST) NNTP-Posting-Date: Thu, 07 Jul 2005 22:37:09 EST Organization: BigPond Internet Services Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:246761 "Joel Garry" wrote in message news:1120692408.099052.209860@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