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]

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.

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

Table created.

SQL> begin
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.

SQL> select 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.

SQL> create index tj_idx on tj(tj_date, tj_truck_id);

Index created.

SQL> set 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=CHOOSE
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" <bl****************@hotmail.com> wrote in message
news:3f********@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