Oracle, speed up query

From: Louis <luvieneq_at_hotmail.com>
Date: 22 Jul 2004 08:03:46 -0700
Message-ID: <eab80d68.0407220703.3f0c5e65@posting.google.com>

Hi everyone,

I would like to say that i'm really new to Oracle. I started to read about it 1 month ago. I am currently writting an Excel application to query some data from an Oracle database. I use Excel 2002, OO4O 8.0.6.3.9 and Oracle 8.1.7.2.0
I just want to retrieve data, nothing more.

My question is about getting some help to speed up my query. I can't change the database so the optimization has to be in my code.

This is my tables:

WORKORDER
So far, 4-5k entries, but it will get higher as time goes This is where I get most of my information. Some fields are number(keys?) and I have to get the description in the other tables.
.laborcode
.eqnum
...

LABOR
small, 30-40 entries
.laborcode
.name

EQUIPMENT
15k entries so far, and will increase a little. Information about equipment. description, number, ...
.eqnum
.description

LOCATIONS
100-200 entries. Same thing, I use this table to get the name of the location because I have the number in the workorder table
.location
.description

COMPAGNIES
.compagny
.description

I retrieve a lot of information in the workorder table, but I need to join with other table to get the description instead of using a code. For example, in workorder.eqnum I have the number of the equipment, but I need to get the description of the equipement in equipment.eqnum. So I do an outter join AND workorder.eqnum = equipment.eqnum(+) and I add equipment.description in my select.

Query :

SELECT workorder.wonum as NUMBT, workorder.status as STATUT, workorder.reportedby as DEMANDEUR, labor.name as RESPONSABLE, workorder.description as DESCRIPTIONCOURTE, workorder.eqnum as NUMEQUIPEMENT, equipment.itemnum as NUMECRI, equipment.modelnum AS NUMMODEL,

companies.name as DESCRIPTIONFABRIQUANT,
equipment.description as DESCRIPTIONANG,
equipment.description2 as DESCRIPTIONFRA,
workorder.location as NUMEMPLACEMENT, locations.description as

EMPLACEMENT,

TO_CHAR(workorder.actlabhrs, '000.00') as TEMPSMO,
TO_CHAR(workorder.actlabcost, '000000.00') as COUTMO,
TO_CHAR(workorder.actmatcost, '000000.00') as COUTMATERIEL,
workorder.worktype as TYPEBT, labor.craft as METIER, 

workorder.statusdate as DATESTATUT,
workorder.ACTFinish as DATEFIN, workorder.reportdate as DATERAPPORT"

FROM workorder, labor, locations, equipment, companies WHERE workorder.supervisor = labor.laborcode(+)

AND workorder.location = locations.location(+)
AND workorder.eqnum = equipment.eqnum(+)
AND equipment.manufacturer = companies.company(+)

if(condition) AND workorder.supervisor=ComboBox_Supervisor.Text if(condition) AND workorder.location=ComboBox_Location.Text (From the GUI, the user can select some more narrowed search)

Is there a way to speed up my query. I can get around 3k result in a little less than 1 min, but it seems pretty slow. I'm sure you guys will need more information to answer, but I don't know what to add. Tell me how to get the information and I will post it here.

Thanks a lot!

Louis Received on Thu Jul 22 2004 - 10:03:46 CDT