REPOST: Encoding NULLs as sentinels/extents

[Silly keyboard caused error in first post... apologies]

Hi gurus,

    Through dim distant memory, I seem to recall being warned at uni never to encode a null in a field as a value.... strangely enough, on my current project we have a huge number of date range fields that are, (you guessed it!), currently encoding NULLs as "infinity" (ie. from here till eternity). Hence to find those records that were / are / will be current for a specific date we have to do something like.....

SELECT *
FROM HISTORY_TABLE H
WHERE <PARAM_DATE> >= H.DATERANGE_START AND (( <PARAM_DATE> <= H.DATERANGE_END OR H.DATERANGE_END IS NULL);

Now my question is - would it be more efficient to use the minimum or maximum values in the
appropriate date field (under Oracle I believe the max is the year 9999 or something) rather than the check against NULL?

SELECT *
FROM HISTORY_TABLE H
WHERE <PARAM_DATE> IS BETWEEN H.DATERANGE_START AND H.DATERANGE_END);

Would that enable the indexes, acceleration plans to be used etc etc?

Thanks for any advice,

Lachlan Pitts
Technical Consultant
Softworks Australia Pty Ltd

Project e-mail: PittsLR_at_pwh.housing.qld.gov.au Received on Thu Nov 26 1998 - 03:07:23 CST