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