plinehan wrote:

>
>>> Indeed - but why one would want to use a "see-saw" strategy in
>>> the first place baffles me.
>
>> It has the advantage that results are returned in index order,
>> which is nice if you're doing something like
>
>> select first 1 first_name, last_name
>> from everybody_in_the_USA
>> order by last_name;
>
> Surely this could be done by the simple expedient of getting all
> the necessary record pointers first, and then sorting that small
> list and then getting those records in the order of the
> sorted small list?

Programmers who are used to systems that retrieve records in index
order don't bother making their "first" or "limit" queries selective.
That's why the example gets everybody in the USA, then stops after
returning the first one.

>
> I thought the literature about databases was full of the
> OLTP/OLAP conflict - separating such tables onto different
> disks would, I thought, have helped? But, you're saying RAID...

Yes - and serious data analysis may require an entirely different
way of storing information from a typical relational database.
However, the ability to put certain tables on specific devices
is only a tiny step ... not sufficient to support major analytics,
and likely to cause problems that a hardware solution does not.
Once you get your data optimally spread for one query, the next
query that arrives may well not fit the design.

>
> One final point about the advantage of separation and then
> I'll shut up, never to be heard mentioning this topic
> again.
>
> Oracle (the kitchen sink implementation to beat all kitchen
> sink implementations) does have one good thing going for it,
> it is very well instrumented compared to other RDBMS's.
>
> Would separating the data from different tables onto different
> disks not give the tuning engineer a better chance to be able
> to figure out where their database was spending time during
> a (unacceptably) long-running query?

Probably not, though I wouldn't try to guess what information
is needed to tune an Oracle database for optimal performance.
That's a whole career path. It's also a great out when someone
asks an Oracle support person a query is so slow ... You haven't
tuned the database correctly.


Good luck,


Ann