Re: Query rewrite help needed

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 case "size truly doesn't matter". It is the performance. Or so I've been told :-). During my decades as a "C" programmer, I'd find it common for people to get caught up in "concise" or "elegant" coding. The overall result was lack of maintainability -- who could unravel and understand the silly thing? My advice is to leave it alone with respect to "conciseness" or "elegance". The way it is written now is rather clear and understandable. Making it more concise will make it more difficult to understand, maintain, and will very likely hurt performance as the CBO will be less able to unravel a decent execution plan from it. Just remember, when the CBO gets confused, it can always fall back on FULL table scans, and I'm guessing that may not be optimal here... :-)

---

However, if you have indexing on the SEG_DEP_DATE_TIME and FLIGHTNO columns
in the OP_FL_PLAN table, then the WHERE clauses in both queries are
constructed not to use them (unless you've created function-based indexes,
which I hope is not the case).  I'd suggest considering rewriting the
predicate:

    and substr(a.flight_no, 1, 2) = :flight_no

to instead read:

    and a.flight_no like :flight_no || '%'

in order to allow any index on FLIGHT_NO to be utilized, if the CBO wishes.
Barring being able to change the query (which I assume is possible based on
your original question in this email), you might alternatively consider a
function-based index on SUBSTR(FLIGHT_NO,1,2), but why waste resources on an
extra index of dubious value when you can just simply change the text of the
query?

In the same vein, I'd suggest rewriting:

    and trunc(a.seg_dep_date_time) = :seg_dep_date

as:

    and a.seg_dep_date_time >= :seg_dep_date
    and a.seg_dep_date_time < :seg_dep_date + 1

This is another case of a less-concise coding construct performing
(potentially) better than its more concise variation.

Of course, I don't know whether your data conditions warrant indexes on
those columns, but as I'm guessing that they may be useful, since the
OP_FL_PLAN table seems to be the "driving" table in this query, all other
tables being "lookups" or "references"...

Hope this helps...

-Tim

----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Monday, March 10, 2003 4:33 AM




> 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


> the query using DECODE statement or CASE statement or self joins to return

> the same result set?  If so, please let me know the same.  Any help in

this


> regard is very much appreciated.

>

> Thanks and Regards,

>

> Ranganath

>

>

> 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,

> a.AC_TYPE,

> a.AC_GRP, b.exp_lat_date_time,

> b.imp_toa_date_time,

> b.spl_code from

> op_fl_plan a, op_fl_service_times b,

> op_restriction d,

> op_restriction_rq e

> where a.op_fl_plan_key = b.op_fl_plan_key

> and a.op_fl_plan_key = e.op_fl_plan_key

> and b.op_fl_service_times_key = d.op_fl_service_times_key

> and a.ac_type <> 'JET'

> and b.exp_lat_date_time >= :eldt

> and b.imp_toa_date_time <= :itdt

> and b.spl_code in(:spl_code1, :spl_code2)

> and b.service = :srv and a.origin = :o and a.destination = :d

> and substr(a.flight_no, 1, 2) = :flight_no

> and a.ac_grp in(:ac_grp)

> and trunc(a.seg_dep_date_time) = :seg_dep_date

> and e.spl_code in(:spl_code1, :spl_code2)

> and d.spl_code not in(:spl_code1, :spl_code2)

> and d.position in('E', 'I')

> union

> 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,

> c.AC_TYPE,

> a.AC_GRP, b.exp_lat_date_time,

> b.imp_toa_date_time,

> b.spl_code from

> op_fl_plan a, op_fl_service_times b, op_ac_type c,

> op_restriction d,

> op_restriction_rq e

> where a.op_fl_plan_key = b.op_fl_plan_key

> and a.op_fl_plan_key = e.op_fl_plan_key

> and b.op_fl_service_times_key = d.op_fl_service_times_key

> and a.op_fl_plan_key = c.op_fl_plan_key

> and a.ac_type = 'JET'

> and b.exp_lat_date_time >= :eldt

> and b.imp_toa_date_time <= :itdt

> and b.spl_code in(:spl_code1, :spl_code2)

> and b.service = :srv and a.origin = :o and a.destination = :d

> and substr(a.flight_no, 1, 2) = :flight_no

> and a.ac_grp in(:ac_grp)

> and trunc(a.seg_dep_date_time) = :seg_dep_date

> and e.spl_code in(:spl_code1, :spl_code2)

> and d.spl_code not in(:spl_code1, :spl_code2)

> and d.position in('E', 'I')

> WARNING: The information in this message is confidential and may be

legally


> privileged. It is intended solely for the addressee.  Access to this

message


> by anyone else is unauthorised.  If you are not the intended recipient,

any


> disclosure, copying, or distribution of the message, or any action or

> omission taken by you in reliance on it, is prohibited and may be

unlawful.


> Please immediately contact the sender if you have received this message in

> error. Thank you.

> --

> Please see the official ORACLE-L FAQ: http://www.orafaq.net

> --

> Author: Krishnaswamy, Ranganath

>   INET: Ranganath.Krishnaswamy_at_blr.hpsglobal.com

>

> Fat City Network Services    -- 858-538-5051 http://www.fatcity.com

> San Diego, California        -- Mailing list and web hosting services

> ---------------------------------------------------------------------

> To REMOVE yourself from this mailing list, send an E-Mail message

> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> the message BODY, include a line containing: UNSUB ORACLE-L

> (or the name of mailing list you want to be removed from).  You may

> also send the HELP command for other information (like subscribing).

>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).