Hi List,
I have the below query which is very long and it contains an UNION
clause as the query has to select ac_type from op_ac_type table if
op_fl_plan.op-ac_type = 'JET' while it should select op_ac_type from
op_fl_plan table if op_fl_plan.ac_type = 'JET'.Is it possible to rewrite
Try something like this:
select a.OP_FL_PLAN_KEY,
a.FLIGHT_NO,
a.ORIGIN,
a.DESTINATION,
a.SEG_DEP_DATE_TIME,
a.SEG_ARR_DATE_TIME,
a.DEP_DAY_CHG,
a.ARR_DAY_CHG,
DECODE(a.ac_type, 'JET', c.AC_TYPE, a.AC_TYPE),
a.AC_GRP, b.exp_lat_date_time,
b.imp_toa_date_time,
b.spl_code
from op_fl_plan a,
select a.OP_FL_PLAN_KEY,
a.FLIGHT_NO,
a.ORIGIN,
a.DESTINATION,
a.SEG_DEP_DATE_TIME,
a.SEG_ARR_DATE_TIME,
a.DEP_DAY_CHG,
a.ARR_DAY_CHG,
decode(a.ac_type,'JET',c.AC_TYPE,a.ac_type)
a.AC_GRP,
Ranganath,
Why? Will making the query more concise provide any advantage at all? I
frequently break out such concise queries into multiple UNION'd statements
in order to improve performance, by clarifying unnecessarily concise
(therefore convoluted) logic.
Not to be too cheeky, but in this
Tim,
Following your trunc() line, have you come
across this feature of 9.2 (and some earlier
versions) -
create index t1_i2 on t1(d1);
analyze table t1 compute statistics;
select * from t1
where trunc(sysdate) = '01-Dec-2002';
Execution plan:
---
table access (by index
Title: Re: Query rewrite help needed
See if the following works:
select a.OP_FL_PLAN_KEY
, a.FLIGHT_NO
, a.ORIGIN
, a.DESTINATION
, a.SEG_DEP_DATE_TIME
, a.SEG_ARR_DATE_TIME
, a.DEP_DAY_CHG
, a.ARR_DAY_CHG
, CASE WHEN a.AC_TYPE = 'JET'
then (select c.ac_type from op_ac_type c where
Unfortunately the example loses
a little credibility and impact because
I put:
where trunc(sysdate) = '01-Dec-2002';
rather than
where trunc(d1) = '01-Dec-2002';
I just can't seem to get the cut and paste
to work properly from my laptop to my
email machine :(
Regards
Jonathan
-
From: Jonathan Lewis [mailto:[EMAIL PROTECTED]
Sent: 10 March 2003 15:24
To: Multiple recipients of list ORACLE-L
Subject: Re: Query rewrite help needed
Tim,
Following your trunc() line, have you come
across this feature of 9.2 (and some earlier
versions) -
create index
Notes inline.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Now available One-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )
UK___March 19th
UK___April 8th
