On Sun, May 18, 2003 at 05:02:39PM +1200, Mark Kirkwood wrote:
> Being able to "segment" tables for admin purposes (like archiving) might
> be beneficial.
>
> I guess this could be orthogonal to clustering or partitioning schemes
> that mininize table/index access for queries.
Segmenting tables is what inheritence can buy you now (although somewhat
suboptimally). You can create subtables of your super-table and just
remember which table is supposed to contain which tuples. By adding CHECK
constraints you can even make the system error out on you if you make a
mistake.
Segmenting tables for archiving is only useful if the planner rarely uses
them. If the planner is still going to generate a seq. scan on the "archive"
every time you query the table, then it's not really archived in any sense.
Which bring another question. If I have a CHECK constraint on a table saying
'id < 5000' and I do a query 'WHERE id = 6000', is the query optimiser
clever enough to notice that there can be no matches. Preliminary testing
indicates no (although I see it's 7.2).
> (Oracle has sort of rolled these ideas together with their partitioning)
My guess is because segmenting without the planner cleverness is a bit
useless.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
> - Samuel P. Huntington