Re: Oracle Myths

"Mike Ault" <mikerault_at_earthlink.net> wrote in message news:37fab3ab.0205291028.79e0b197_at_posting.google.com...
> While I have no doubt that SELECT and DELETE may have little
> difference, what about UPDATE? What about DROP TABLE? Was the DELETE a
> conditional DELETE or a DELETE of all rows? Again, give us the full
> story.

I did, and the paper was on my web site. I can't *repeat* the full story, since it was a 10 page document.

>Also, where the extents contiguous (i.e. created all at the
> same time with no other segments) or did you make sure and distribute
> them randomly across the tablespace datafiles involved?
>

Both contiguous and non-contiguous, large and small, many hundreds and one or two.

I can't think why you'd assume that what was true for a delete would be different for a drop table -which ultimately just becomes a bunch of deletes on the data dictionary.

> Your statement of experiment lacks even a hint of scientific method,
> thus your proof statement is not valid.
>

It's rather more valid than just shooting off 'it's invalid' statements! Why don't you 1) postulate why contiguity of extents would make a difference and 2) propose a test to prove your hypothesis, and 3) perform said test. I was asked if it made a difference, I knocked up a test, I performed it, the answer came out: it made not a jot of difference.

Oh -while you're at it. Tell Tom, Jonathan, Steve, and the like that they must be wrong, too.

HJR
> Mike
>
> "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
news:<ad0p0k$7pg$1_at_lust.ihug.co.nz>...
> > "William Rice" <ricew_at_operamail.com> wrote in message
> > news:1f1a539b.0205280730.5c8e188d_at_posting.google.com...
> > > "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> > news:<acmdu7$cdt$1_at_lust.ihug.co.nz>...
> > > > "William Rice" <ricew_at_operamail.com> wrote in message
> > > > news:1f1a539b.0205231415.51b83543_at_posting.google.com...
> > > > > "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in
> > message
> > news:<3ce36ec0$0$8513$ed9e5944_at_reading.news.pipex.net>...
> > > > > <SNIP>
> > > > > > >
> > > > > > > Seperate tables and indexes for performance reasons.
> > > > > >
> > > > > > see the huge thread on this earlier at
http://shrinkalink.com/201
> > > > > >
> > > > > > I think my summary of this is that in general seperating data
and
> > indexes
> > > > > > should be done for
> > > > > > 1) management reasons only.
> > > > >
> > > > > While I didnt read the whole thread, I guess what I got from it
was
> > > > > the old statement, benchmark your system and find out...
> > > > >
> > > > > <SNIP>
> > > > >
> > > > > An example of where separating indexes from a table might be
> > > > > beneficial would be if you happen to do lots of full table scans
on a
> > > > > particular table. If you make it the only inhabitant of a
tablespace
> > > > > (or tablespaces if it is partitioned), you avoid having to skip
past
> > > > > all of the extents you are not interested in.
> > > >
> > > >
> > > > Myth alert. The contiguity of extents has absolutely zero bearing on
> > > > performance. None whatsoever. For the simple reason that the blocks
of a
> > > > single extent are not physically contiguous on disk anyway. They are
> > > > scattered all over the physical platter... and therefore, you are
> > forever
> > > > having to skip around the place whether there is one extent of one
> > segment,
> > > > or several thousand extents belonging to several hundred segments.
It
> > makes
> > > > no difference.
> > > >
> > > > Regards
> > > > HJR
> > > >
> > >
> > > I would be interested in seeing any documentation, results from
> > > benchmarks, or anything of that nature that would show this to be the
> > > case.
> >
> > Been there and done that, but I am no longer allowed to post you to the
> > results, I'm afraid. I had a script that created either many hundreds or
> > just a handful of extents, large and small, contiguous and
non-contiguous.
> > The script then performed select all records, then delete all records,
and
> > there was absolutely no variation between any of the runs, except that
which
> > was within normal variation for these sorts of tests.
> >
> > >Even with the volume mangers that do scatter the data around,
> > > they figure out if you are doing a sequential scan, and try to
> > > prefetch efficiently in order to compensate for the fact that the data
> > > is scattered around. If your data is scattered around, you don't get
> > > to benefit from this.
> >
> > Just remember that a single Oracle block is not contiguous on disk.
> > Comprised of many O/S blocks, it's all over the place. Extrapolate from
a
> > single block to an extent, and the truth of the matter will be evident.
> >
> > >
> > > I haven't been able to find the benchmarks I did a couple of years ago
> > > otherwise I would post the results.
> >
> > It seems we are in the same boat!
> >
> > >
> > > I would also be interested in where you go the information that the
> > > data that seems contiguous from a raw device is actually scattered all
> > > over the disk.
> >
> > I didn't say anything about raw devices. For the very good reason that
they
> > are the one thing where contiguity is possible, since there is no file
> > system taking it upon itself to place your data.
> >
> > >I will admit to not having any documentation to the
> > > contrary, because to me this has always been a given documented
> > > otherwise by the disk vendor. I am aware that some bad blocks might
> > > end up being written to another part of the disk, but this would be
> > > the exception, not the general case.
> > >
> >
> > Don't extrapolate past what I actually wrote. Raw is a special case, and
> > with raw you truly can get physical contiguity. Anything else: what you
> > *think* is contiguous actually isn't.
> >
> > Regards
> > HJR
> >
> > > If this is to far off topic for this group, I would be happy to take
> > > the discussion offline.
> > >
> > > Will
Received on Wed May 29 2002 - 14:48:17 CDT