Re: Separating data, index objects

Richard Foote wrote:
> "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.

Well, what I've seen in the past is that the next table extent will be written out, the next index extent will be written out, and each will fill at different rates and create the next at different rates because of a large size difference. So they will be sorta-pseudo-clustered at the beginning, but then not be. Now, people keep saying how the filesystem will put things where it wants, so that's why I specified a new and clean filesystem, so it doesn't have a chance to muddle things up from deletes and adds of files. And no, I'm not advocating trying to put everything in one extent - LMT will do other things anyways. But I have seen that LMT does put the segments next to each other for #1, you can look at it with Tablespace Map and see. And for #2, you can see how it does that too.

So if you have, say, 8K Oracle blocks over the appropriate O/S size (say, hp-ux), and the table and index next's at 64K (and we all somehow know that 64K is what LMT's really write, right? Where is that documented, anyways?). So in case #2, beyond the first few extents, you wind up with several table extents with index extents interspersed in the Oracle block. Now with multiblock read count we hopefully have matched the size that Oracle will ask for with what is out there. So what is out there? 8 table extents then 1 index extent? Whatever it is, I find it difficult to believe it can be read in a full table scan as fast as 8 table extents, over the necessarily larger tablespace, even with Oracle knowing where the next table block is, once you get into many gigs. The disk is simply having to read more stuff, since it won't be able to skip a multiblock read of just one index extent. Or will it, since the next can match the oracle blocksize*mbrc? What if next is bigger on the table and smaller on the index?

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

Well, I'm trying to come up with something repeatedly testable, that reflects the fact that many financials systems do have single user end of period processing that takes many hours and is expected to finish in time windows. That is another type of performance metric than normal OLTP processing, on systems that are tuned for OLTP processing. Many people think they need to perform various incantations to speed things up for this reason, and naturally it is a huge breeding ground for myths, since the reasons for the speed-ups may be due to ancillary effects. In some cases, there may be no speed-up at all...

jg

--
@home.com is bogus.  "They put shackles on my hands and my feet.  They
put you in the back of this car. I passed the Capitol and all the
office buildings I used to cover. And I thought, 'My God, how did it
come to this?' " - Judith Miller, reporter jailed for not revealing the
sources to a story _she never wrote_.