Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Query rewrite help needed

Re: Query rewrite help needed

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Mon, 10 Mar 2003 07:34:05 -0800
Message-ID: <F001.00564846.20030310073405@fatcity.com>


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 c.op_fl_plan_key = 
a.op_fl_plan_key)
         else (select x.ac_type from op_fl_plan x where x.op_fl_plan_key = 
a.op_fl_plan_key)
     END 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')

I agree with Tim that performance and readability take precedence over sql size, but if the above works it should maintain readability or even improve it since the case statement implements the verbal description of the intent. At 05:43 AM 3/10/2003 -0800, you wrote:
>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).

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com


This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited. If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately. We honour similar requests relating to the privacy of email communications.

Cette communication par courrier electronique est une communication privee a l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie. Les renseignements contenus dans ce courriel sont confidentiels et si vous n'etes pas le destinataire prevu, vous etes avise, par les presentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit. Si vous n'etes pas specifiquement autorise a recevoir ce courriel ou si vous croyez l'avoir recu par erreur, veuillez en aviser l'expediteur original immediatement. Nous respectons les demandes similaires qui touchent la confidentialite des communications par courrier electronique.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.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).
Received on Mon Mar 10 2003 - 09:34:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US