Re: Optimizer internals

Mark Lewis wrote:
> On Thu, 2006-06-15 at 14:05 -0400, John Vincent wrote:
>> Now I've been told by our DBA that we should have been able to wholy
>> satisfy that query via the indexes.

> DB2 can satisfy the query using only indexes because DB2 doesn't do
> MVCC.

You can get pretty much the same effect with materialized views.
Create a table that LOOKS like the index (just those columns),
with a foreign key relationship to the original table (cascade delete),
and have the after-insert trigger on the main table write a row to the derived table.
Now (index and) query the skinny table.

Advantage of these tables: you can cluster them regularily,
because it doesn't hard-lock the main table.

--
Engineers think that equations approximate reality.
Physicists think that reality approximates the equations.
Mathematicians never make the connection.