Re: help on date column

From: Michael Serbanescu <mserban_at_postoffice.worldnet.att.net>
Date: 1997/09/26
Message-ID: <342B4807.6FA9@postoffice.worldnet.att.net>#1/1

Try this:

1. Drop the index on column SDATE.
2. UPDATE table_a SET sdate=TO_DATE('31-DEC-2999','DD-MON-YYYY') WHERE sdate IS NULL;
3. ALTER TABLE table_a MODIFY (sdate DEFAULT TO_DATE('31-DEC-2999', 'DD-MON-YYYY'));
4. Re-create the index on column SDATE.

To use the index, your query should be:

SELECT col1, col2 FROM table_a
WHERE sdate=TO_DATE('31-DEC-2999','DD-MON-YYYY');

(...WHERE TO_CHAR(sdate,'DD-MON-YYYY')='31-DEC-2999', which seems the natural thing to do, will not make use of the index on SDATE, as you know).

Hope this helps.

Michael Serbanescu

---

Meng Kiat TAN wrote:
>
> Hi,
> I have created an index for a date column in a table. This
> column is nullable. How do I form up a query so that this
> query will make use of the index ? I need to select
> all the rows which have the date field null.
>
> select col1, col2 from table_a where sdate is null;
>
> This does not make use of the index. So is there a way
> to avoid doing a full table scan?
>
> Thanks & regards!
Received on Fri Sep 26 1997 - 00:00:00 CDT