Re: Query tuning experts: Any hope for tuning query with 5+ joins and a sort?

Mon, 4 Nov 2002 23:18:38 +1000, Richard Foote said (and I quote):
>
> I think you've missed the point. It's not that the sort is particularly
> efficient, it's because using an index to read a large portion of a table is
> so terribly inefficient.

Unique indexes are not that inefficient, non-unique can be.

The thing to remember here is that it's OK in the pristine environment of a benchmark to grab as much CPU/IO as possible to get max speed off a given operation. In isolation, fully dedicated. As such, it's quite kosher for a full scan with a sort to be faster, given sufficient hardware.

Problem is that in normal operation of a production system, the hardware is being shared with a gazzilion other things. We fire off an operation that grabs as much as possible to go fast and we got an immediate problem in our hands. Particularly if we're forcing a sort into TEMP, while the monthly processing is doing its big sorts. You get the picture.

My approach to these things is always: does it still deliver all those advantages in a real life situation? If there is the slightest doubt, then it's time for caution.

Heck, I just had a very well known Oracle consultancy firm walk into a client of mine and recommend we go for RULE-based optimizer, given all the crap the CBO has been causing ( and to them also, when they tried to "fix" it...).

It's one of those things: not all is glitter in the world of performance in Oracle.

> big snip
> I'll let the above example speak for itself.

Impressive. What I don't get is why the stats for the 10% full scan are not returning the same number of blocks visited. 10% or not, it should have been the same full scan.
Also, the index is obviously not unique ("id between 1 and 1000" cannot return 128000 rows otherwise). I was talking about unique indexes. There is a difference for this purpose.

>
> Give it a go and see for yourself ;)

Will do. In 8i, I don't have 9r2 yet loaded anywhere... I'll do a few tries with/without unique idx and with large/mid/small volumes. Stay tuned.

-- 
Cheers
Nuno Souto
nsouto_at_optusnet.com.au.nospam