Problem with Distince and Index - Inconsitant output

[Bliss]

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

[Chris Leonard]

Re: Problem with Distince and Index - Inconsitant output

For what it's worth, I could not reproduce your problem. You may want to
provide us with more specific information, if possible.

Here is a spool of the session where I tried to reproduce the issue. I
tried the same commands on 8.1.7.4 and 9.2.0.1, and the output did not
change.

SQLcreate table tj (tj_date date, tj_truck_id number(5), tj_truck_no
varchar2(10), tj_km number(9));

Table created.

SQLbegin
2 for i in 1 .. 30 loop
3 insert into tj values (sysdate, i, 'Truck ' || to_char(i), i*100);
4 end loop;
5 update tj set tj_truck_id = null where mod(tj_truck_id , 5) = 0;
6 commit;
7 end;
8 /

PL/SQL procedure successfully completed.

SQLselect distinct (tj_truck_no) from tj where tj_truck_id is null;

TJ_TRUCK_N
----------
Truck 10
Truck 15
Truck 20
Truck 25
Truck 30
Truck 5

6 rows selected.

SQLcreate index tj_idx on tj(tj_date, tj_truck_id);

Index created.

SQLset autotrace on explain
SQL/

TJ_TRUCK_N
----------
Truck 10
Truck 15
Truck 20
Truck 25
Truck 30
Truck 5

6 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOS E
1 0 SORT (UNIQUE)
2 1 TABLE ACCESS (FULL) OF 'TJ'


--
Cheers,
Chris

_______________ _______________ _____

Chris Leonard, The Database Guy

http://www.databaseguy.com

Brainbench MVP for Oracle Admin

http://www.brainbench.com

MCSE, MCDBA, OCP, CIW
_______________ _______________ _____


"Bliss" <bliss_is_ignor ance@hotmail.co mwrote in message
news:3f004f11_1 @news.tm.net.my ...

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