473,320 Members | 1,914 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Problem with Distince and Index - Inconsitant output

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


Jul 19 '05 #1
1 3090
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

Jul 19 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

15
by: Hemant Shah | last post by:
Folks, We have an SQL statement that was coded in an application many years ago (starting with DB V2 I think). When I upgraded to UDB 8.2, the optimizer does not use optimal path to access the...
6
by: Edd Dawson | last post by:
Hi. I have a strange problem involving the passing of command line arguments to a C program I'm writing. I tried posting this in comp.programming yesterday but someone kindly suggested that I'd...
3
by: geoffblanduk_nospam | last post by:
Given a set of intervals {i1, i2, i3, ...} a list is produced; the base items (0) are placed one at a time into the stream and after the ix one a interval item (1, 2, 3, etc) of the correct type is...
0
by: michael | last post by:
Hi. I have a problem using the CollectioEditor. In my custom control I have a public property that returns ItemList. ItemList is inherited from CollectionBase and contains very simple items...
9
by: HC | last post by:
Hello, all, I started out thinking my problems were elsewhere but as I have worked through this I have isolated my problem, currently, as a difference between MSDE and SQL Express 2005 (I'll just...
9
by: tiwarinitin.3108 | last post by:
An interactive program that reads 3 list of numbers, which are stored in three seperate files, and creates one sorted list. Each file should contain not more than 15 numbers.
1
by: Bliss | last post by:
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) ....
3
by: mearvk | last post by:
I am unable to get Xerces to write out attributes from a struct which I am able to print out and verify is correct. The structs are defined below as is subsection of the output. int...
87
by: pereges | last post by:
I have a C program which I created on Windows machine. I have compiled and executed the program on windows machine and it gives me the consistent output every time i run it. for eg. input a = 2,...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.