Home » SQL & PL/SQL » SQL & PL/SQL » Improvising the where condition for faster query processing???
Improvising the where condition for faster query processing??? [message #238379] Thu, 17 May 2007 14:23 Go to next message
concorde800
Messages: 52
Registered: May 2007
Member
This query works but takes forever (I must admit the table is large table). However, I am looking to see if I can efficiently rewrite the where clause for the specified date range?

Any suggestions on the formatted code below?


SELECT  
a.zzz_rcrd_chng_id, a.zzz_rcrd_insrt_id, a.zzz_rcrd_updt_id,
       a.zzz_rcrd_dlt_id, a.zzz_rcrd_re_insrt_id, a.zzz_src_archv_flag,
       a.zzz_src_extrct_dttm, a.zzz_src_rcrd_chksm_num, a.zzz_dml_code,
       a.zzz_sessn_run_id, a.zzz_rcrd_create_dttm, a.zzz_rcrd_chng_dttm,
       a.zzz_rcrd_create_uid, a.zzz_src_rcrd_chngd_clmn_btmp,
       a.zzz_rcrd_chng_uid, a.zzz_src_clnt_dim_id, a.zzz_partitn_id,
       a.request, a.datapakid, a.partno, a.record, a.rocancel, a.vbeln,
       a.posnr, a.aedat, a.bukrs, a.bzirk, a.fkart, a.fkdat, a.fktyp,
       a.hwaer, a.kdgrp, a.kunag, a.kunrg, a.kurrf, a.stwae, a.vbtyp,
       a.vkorg, a.vtweg, a.waerk, a.aktnr, a.aubel, a.aupos, a.bonba,
       a.bonus, a.brgew, a.brtwr, a.bwapplnm, a.bwvorg, a.charg,
       a.ean11, a.erdat, a.fareg, a.fbuda, a.fkimg, a.fklmg, a.gewei,
       a.knuma_ag, a.kokrs, a.kostl, a.kursk, a.kursk_dat, a.kvgr1,
       a.kvgr2, a.kvgr3, a.kvgr4, a.kvgr5, a.kzwi1, a.kzwi2, a.kzwi3,
       a.kzwi4, a.kzwi5, a.kzwi6, a.lgort, a.lmeng, a.matkl, a.matnr,
       a.matwa, a.meins, a.mvgr1, a.mvgr2, a.mvgr3, a.mvgr4, a.mvgr5,
       a.mwsbp, a.netwr, a.ntgew, a.pkunre, a.pkunwe, a.posar, a.prodh,
       a.provg, a.prsdt, a.pstyv, a.pvrtnr, a.skfbp, a.smeng, a.spara,
       a.spart, a.stadat, a.stcur, a.umvkn, a.umvkz, a.vdatu, a.vgbel,
       a.vgpos, a.vkbur, a.vkgrp, a.voleh, a.volum, a.vrkme, a.vstel,
       a.wavwr, a.werks, a.aufnr, a.augru_auft, a.j_3aetenr,
       a.j_3afkimg, a.j_3asize, a.j_3avgete, a.ps_posid, a.anzfkpos,
       a.periv, a.zz_auart, a.zz_ernam, a.zzdoc_shp_name1,
       a.zzdoc_shp_city1, a.zzdoc_shp_land1, a.zzdoc_shp_pstcd1,
       a.zzdoc_shp_regio, a.zzdoc_shp_street, a.zzdoc_shp_adr, a.belnr,
       a.fkart_ab, a.fkart_rl, a.fksto, a.kalsm, a.knuma, a.knumv,
       a.konda, a.mrnkz, a.pltyp, a.regio, a.rfbsk, a.zlsch, a.zterm,
       a.arktx, a.autyp, a.j_3aasdt, a."/AFS/FKMGK", a.j_3afklmg,
       a.j_3aknumh, a.ernam, a.j_4krcat, a.kdkg1, a.kdkg2, a.kdkg3,
       a.kdkg4, a.mprok, a.paobjnr, a.posnv, a.shkzg, a.vbelv, a.vgtyp,
       a.timestamp, a.zzvbeln_or, a.zzposnr_or
  FROM stage#emea_bw.bw_2lis_13_vditm_be_cimg a
  WHERE to_date(a.zzz_rcrd_chng_dttm) IN ('01-APR-2007', '02-APR-2007', '03-APR-2007', '04-APR-2007', '05-APR-2007', 
                                          '06-APR-2007', '07-APR-2007')

[Updated on: Thu, 17 May 2007 14:24]

Report message to a moderator

Re: Improvising the where condition for faster query processing??? [message #238380 is a reply to message #238379] Thu, 17 May 2007 14:24 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
Please read & FOLLOW the #1 Sticky post at the top of this forum if you expect answers to your question(s).
Re: Improvising the where condition for faster query processing??? [message #238387 is a reply to message #238379] Thu, 17 May 2007 14:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes: to_date returns a date, '01-APR-2007' and others are strings. It is wrong to compare them.
When you'll use the correct datatype you'll can use BETWEEN operator.

Regards
Michel
Re: Improvising the where condition for faster query processing??? [message #238396 is a reply to message #238387] Thu, 17 May 2007 16:04 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
I should have included the information on the datatype of column in where clause. I am running the query on TOAD 8.6 and database version is 10g.

On the query, a.zzz_rcrd_chng_dttm is defined as DATE datatype (default sysdate) so how do you suppose I use a between operator and capture dates from 1st thru 7th.
Re: Improvising the where condition for faster query processing??? [message #238399 is a reply to message #238379] Thu, 17 May 2007 16:12 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
It would just be a standard between clause:

where value between lowval and highval

BUT, if your field is already a date data type, then you don't need to put a to_date on it.

AND, if your comparison values (your hard coded dates) are text strings, then you DO need to put a to_date around them.

You would want to pay more attention to the time portion of the date than I did in the below:

MYDBA@orcl > create table test(d) as select sysdate+rownum from all_objects where rownum

Table created.

MYDBA@orcl > select d from test order by d;

D
--------------------
18-MAY-2007 17:09:09
19-MAY-2007 17:09:09
20-MAY-2007 17:09:09
21-MAY-2007 17:09:09
22-MAY-2007 17:09:09
23-MAY-2007 17:09:09
24-MAY-2007 17:09:09
25-MAY-2007 17:09:09
26-MAY-2007 17:09:09
27-MAY-2007 17:09:09

10 rows selected.

MYDBA@orcl > select d from test where d between to_date('22-MAY-2007','DD-MON-YYYY')
  2  and to_date('25-MAY-2007', 'DD-MON-YYYY');

D
--------------------
22-MAY-2007 17:09:09
23-MAY-2007 17:09:09
24-MAY-2007 17:09:09

3 rows selected.

Re: Improvising the where condition for faster query processing??? [message #238403 is a reply to message #238399] Thu, 17 May 2007 17:45 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
Thank you. Please could clarify on the timestamp. I think the query takes care of that.
Re: Improvising the where condition for faster query processing??? [message #238404 is a reply to message #238379] Thu, 17 May 2007 18:03 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>I think the query takes care of that.
Here is a free clue.
You are no where close to "query takes care of that".
Re: Improvising the where condition for faster query processing??? [message #238405 is a reply to message #238404] Thu, 17 May 2007 18:24 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
Guess, that's why clues are "freebies". They don't mean a thing
Re: Improvising the where condition for faster query processing??? [message #238406 is a reply to message #238379] Thu, 17 May 2007 18:40 Go to previous message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
WHERE <column_name> BETWEEN <beg_range> AND <end_range>
Previous Topic: Deleteing rows using with clause
Next Topic: how we call shellscripts from PL/SQL programs
Goto Forum:
  


Current Time: Tue Dec 06 02:47:28 CST 2016

Total time taken to generate the page: 0.09035 seconds