Problem with Distince and Index - Inconsitant output

Hi All,

I have a table Truck_Journey with the following structure

Tj_Date          Date
Tj_Truck_Id   Number(5)
Tj_Truck_No Varchar2(10)
Tj_Km            Number(9)

If I give a
select distinct (tj_truck_no) from truck_journey where tj_truck_id is null; it works and shows all the truck numbers where the truck id is null

If I create a non-unique index on tj_date + tj_truck_id then the above query does not show any records. When I run an explain plan, it shows that it's doing a index scan.

My queries are
1)According to the documentation (as I understand it), using a "is null" in the select should do a full table scan. So how come it's doing an index scan?

2)Whatever the scan methodology used, why is it showing an incorrect output after adding the index? Does this have to do anything with any optimization parameters or something?

Thanks in advance

Regards,
Bliss Received on Mon Jun 30 2003 - 16:53:30 CEST