Re: Reports 2.5 Slow Query

It would be worth turning on the tracing using 'set autotrace on' before running just the query in SQLPLUS. This will tell you how ORACLE is executing your query and if it is using any indexes. Sometimes faster response can be gained by ANALYZING the tables.

NB. You need to have a PLAN_TABLE set up to use the autotrace.

Rob Dickens
OCP Developer

OCP Tip of the Month and OCP Fact Sheet at http://www.btinternet.com/~rob.dickens/OCP.htm

Delphis Group Limited wrote:

> Hello,
> I am writing a report to show details from 3 joined tables where the key
> field does not exist in a fourth table. I have determined a way to
> achieve this but am not sure if there is a better way.
>
> Here is the query that I use:
>
> select d.pv_no,
> d.pv_type,
> d.pv_marker,
> d.pv_desc,
> a.recovery_s,
> a.recovery_we,
> a.pv_hours_s,
> a.pv_hours_we,
> e.depot_name
> from cos_recovery a, cos_plant_vehicle d, cos_depot e
> where a.pv_no = d.pv_no
> and d.depot_no = e.depot_no
> and a.pv_no not in (select substr(b.job_no,3) from
> cos_job_run_cost b where b.job_cost_type in (10,21,22,23,24,25,26,27,29,
> 30) &p_date_clause2) &p_rec_where
> order by a.pv_no
>
> The only trouble with this is that it is taking such a long, long time.
> However, the cos_job-run_cost table is the only available source for me
> to achieve the desired result. It has over 100,000 records and is making
> the testing of this report a very slow process (not to mention the
> forthcoming training!!).
>
> If you have any better ideas than this please feel free to voice them.
>
> Thanks
>
> R.W. Fairbairn
> --
> Delphis Group Limited
Received on Thu Nov 11 1999 - 17:09:10 CET