I've been reading up on table elimination in Oracle, and there seems to be a lot more support for it in 11g. However, even using 11g, I must have tried twenty different ways to write the latest view in order for it to work properly, but it just _refuses_ to do so.

1) Making a separate latest view for every attribute with primary/foreign keys on the view set to RELY.
= No table elimination (beats me why not).

2) Use subselects in the columns part of the query.
= Table eliminaiton works, but joins are not performed efficiently, and if you want two columns from an attribute, then it scans the attribute twice.

3) Using a WITH-statement to materialize a view that select data from a historized attribute, then use that instead in the query in combination with above.
= Same problems as above.

4) Left join on ID and subselect to get latest FromDate.
= Does not compile, subselect in a left join not allowed in Oracle.

5) Left join on both ID and FromDate in a precalculated view.
= No table elimination (limitation when using composite PKs).


The only two options that I found that work is:

A) Materialize the latest information for every attribute in its own table. Left join these using only ID-columns.
= Poor solution with respect to size, good solution with respect to performance.

B) Write queries by hand instead of using a latest view.
= Flexible, best performance, but takes a lot of work and probably hard to maintain.


I'm a bit disappointed with Oracle right now. Even PostgreSQL gets this right. I am continuing with approach 1) in order to see if there's something I've misunderstood about RELY. That's the sql code I sent you the other day. According to some posts I read yesterday, table elimination should work on non-enforced constraints as long as you've set RELY on them.