Thanks for the reply
Regards,
Bliss
"Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> wrote in message
news:j8vdgvgmv23m6f62r6n3erhjn17d87jo23_at_4ax.com...
> 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 - 23:04:15 CDT
