Re: Temporal Data (long)

A little left of plumb wrote:
>
> Temporal Data
>
> My apologies in advance for the lack of brevity. I really tried hard for
> clarity instead...

< severely snipped for brevity >

> The obvious approach to making life easier for the end-user is to
> encapsulate the SQL in a view. The only reasonable approach is to create
> a view that can return values for any K1, K2 combination:
>
> Create or Replace View TEST_VIEW as
> Select T1.K1, T1.K2, T1.D1, T1.D2, T1.D3
> From TEST T1,
> (Select K1, K2, Max(KT) MKT
> From TEST
> Group By K1, K2) T2
> Where T1.K1 = T2.K1 and
> T1.K2 = T2.K2 and
> T1.KT = T2.MKT;
>
> and then the users can get current values simply and naturally by selecting
> from the view:
>
> Select *
> From TEST_VIEW
> Where K1 = 'A';
>
> The problem with this is that the view's inner parenthesized select
> statement doesn't see the K1 = 'A' part of the where clause and therefore
> does a full table scan, at least according to Oracle's Explain Plan.

< more snippage >
> Am I missing something obvious? Is there a better way to write the
> function, or either flavor of view? Is there a better way to approach the
> whole problem?
>
> Any ideas, thoughts, pointers, etc would be greatly appreciated.

Not my field (any more) but I have seen this done using _two_ dates - effective date and inactive date - with the inactive date on current rows being set to the maximum allowable value rather than left null. The select for the "current record" view then becomes

     Select T1.K1, T1.K2, T1.KT1, T1.KT2, T1.D1, T1.D2, T1.D3
       From TEST T1
      Where T1.KT1 <= SYSDATE
	And T1.KT2 > SYSDATE

This gets you out of having to hit the table twice, which I think may be what really throws the optimiser. If you index KT1 and KT2 (can be a single index) I think selects on this view perform OK.

-- 
Phil Edwards                    phil_at_news400.com
Editor, NEWS/400.UK             +44 (0)161 929 0777