Range partitioned tables and Nulls

Hello people,

We have a large amount of data that is distributed as follows:

   61% Less than or equal today's date
   8% Greater than or equal today's date    31% Null

Our application frequently issues the query "find the records greater than today's date". There is an index on the date column, however the optomizer prefers to do a full table scan (CBO is enabled and up to date statistics are provided). This is a classic situation that lends itself to using table partitioning, therefore the table is partitioned as follows:

 partition by range (exp_date)
(

   partition p1

      values less than (to_date('2001-07-01','yyyy-mm-dd')),    partition p2

      values less than (to_date('9999-12-31','yyyy-mm-dd')),    partition p3

      values less than (maxvalue)
)

Unfortunately, for the purposes of partitioning nulls are treated as being at the end and are therefore placed in the last partition. As a result the last partition is scanned even though it only contains null dates when doing a "greater than" query.

Does anyone have any suggestions on how to resolve this problem? (It is a pitty that Oracle does not do a scan within p2 and an index seek within p3 as nulls are not stored in the index - or can this be done by the optomizer? Index partitioning options?)

One option would be to alter the query to select from a date range (today's date and 9999-12-31), unfortunately altering the app is not an option.

Any help much appreciated.

Allan. Received on Mon Jul 16 2001 - 20:21:19 CDT