Re: Range partitioned tables and Nulls

Allan Taunt wrote:

> 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.

Altering an app is always an option. It is merely a question of what is more painful to management. I'd alter the app.

Daniel A. Morgan Received on Tue Jul 17 2001 - 13:17:40 CDT