Re: Problem with Distince and Index - Inconsitant output
Date: Tue, 1 Jul 2003 11:35:29 -0500
Message-ID: <mLiMa.19$C5.26259_at_news.uswest.net>
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" <bliss_is_ignorance_at_hotmail.com> wrote in message news:3f004f11_1_at_news.tm.net.my...Received on Tue Jul 01 2003 - 18:35:29 CEST
> 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
>
>
>
>