plinehan wrote:

>
>> Often placement control is used to avoid disk head contention
>> when doing indexed reads. Typical database access strategies
>> require reading first from the index, then the data area to
>> retrieve a record, then back to the index to identify the
>> next target record, then back to the data area again.
>
> Surely any system would build up a list? What system(s)
> (well-known) use that strategy?

Oracle, MS SQL, MySQL for sure. Postgres probably.

>
> 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;

>
>
> What about when the database is responding to concurrent requests,
> one on one table, one on another? It might be useful then to
> have a possible data-placement strategy - depending on the
> disk/table usage profile of the app?

Really, RAID and a big cache are a much better solution for
that case.

>
>
>> Firebird (and InterBase) handle indexed access differently,
>> first identifying all the qualifying index entries
>> then accessing the data.
>

The other advantage of the "list" strategy is that a query
on a single table can use several indexes at once. That got a
bad reputation on this list because the optimizer wasn't
originally smart enough to stop when it got to a unique value
(e.g. primary key) and would spend a lot of time reading
secondary indexes and building big lists, only to combine
them with the single record found through the primary key.
Happily, Firebird has learned a lot over the past nine
years. When the query includes two terms with moderately
selective indexes reading only the records in the intersection
really is a saving.

> Archive data older than a month on a read-only separate
> tablespace - might help "partition" those doing day-to-day
> work (OLTP - data entry) and managers doing OLAP/DW work,
> reports &c. and avoid contention between the two.

Well, maybe. I have a hard time imagining a well-written
Firebird application on modern hardware being unable to do
end-of-month and analysis simultaneously.

>
>
> See above - also read-only tablespaces incur no transactional
> overhead.

Sure they do. Or more precisely, a read-only table space
in a read-write database has exactly the same transactional
overhead as an inactive section of a table.

>
>

Do keep asking questions...


Cheers,


Ann