Re: Inconsistant query results - Please help

On Sat, 5 Jul 2003 23:31:51 +0800, "Bliss" <bliss_is_ignorance_at_hotmail.com> wrote:

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

You have a concatenated index. You only have a criterion on the non-leading column *and* you must be using the Rule Based Optimizer (which you shouldn't do): the index is present, so the index will be used. If you would query on both columns the index would likely not be used.
>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?

Definitely not.
And you should rather question your table design with null allowed columns only.
>
>Thanks in advance
>
>Regards,
>Bliss
>
>

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sat Jul 05 2003 - 11:33:18 CDT