oracle10g - how to make run sql query fast the following query keep on running suggest me how to optimize it -

-

/* formatted on 2015/06/24 15:51 (formatter plus v4.8.5) */

select   (select name             dept            deptid = department) name, sum (received),          sum (notresponded)     (select     trim (regexp_substr (b.dept, '[^,]+', 1, level)                           ) department,                      count (*) received,                        count (*)                      - count                           (case                               when round                                      (  (  to_date                                                (to_char (clssndtm,                                                          'yyyy-mm-dd hh24:mi'                                                         ),                                                 'yyyy-mm-dd hh24:mi'                                                )                                          - to_date                                                (to_char (made,                                                          'yyyy-mm-dd hh24:mi'                                                         ),                                                 'yyyy-mm-dd hh24:mi'                                                )                                         )                                       * 24                                      ) <= 1                                  round                                         (  (  to_date                                                  (to_char                                                          (clssndtm,                                                           'yyyy-mm-dd hh24:mi'                                                          ),                                                   'yyyy-mm-dd hh24:mi'                                                  )                                             - to_date                                                  (to_char                                                          (made,                                                           'yyyy-mm-dd hh24:mi'                                                          ),                                                   'yyyy-mm-dd hh24:mi'                                                  )                                            )                                          * 24                                         )                               else null                            end                           ) notresponded                 reportng a, routing b                a.ticket = b.ticket                  , (1 = 1)                  , a.rptflag = 1                  , a.clssndtm not null                  , companyid = 2682           connect instr (dept, ',', 1, level - 1) > 0                  , trunc (made) between trunc (to_date ('06/01/2015',                                                           'mm/dd/yyyy'                                                          )                                                 )                                       , trunc (to_date ('06/23/2015',                                                           'mm/dd/yyyy'                                                          )                                                 )             group trim (regexp_substr (dept, '[^,]+', 1, level))) group department

firstly, want query readable, otherwise it's impossible see what's going on:

select     (select name dept deptid = department) name,     sum (received),     sum (notresponded)     (select         trim (regexp_substr (b.dept, '[^,]+', 1, level) ) department,         count () received,         count () - count (case when round ( ( to_date (to_char (clssndtm, 'yyyy-mm-dd hh24:mi' ), 'yyyy-mm-dd hh24:mi' ) - to_date (to_char (made, 'yyyy-mm-dd hh24:mi' ), 'yyyy-mm-dd hh24:mi' ) ) * 24 ) <= 1 round ( ( to_date (to_char (clssndtm, 'yyyy-mm-dd hh24:mi' ), 'yyyy-mm-dd hh24:mi' ) - to_date (to_char (made, 'yyyy-mm-dd hh24:mi' ), 'yyyy-mm-dd hh24:mi' ) ) * 24 ) else null end ) notresponded             reportng a,         routing b             a.ticket = b.ticket         , (1 = 1)         , a.rptflag = 1         , a.clssndtm not null         , companyid = 2682         connect instr (dept, ',', 1, level - 1) > 0 , trunc (made) between trunc (to_date ('06/01/2015', 'mm/dd/yyyy' ) ) , trunc (to_date ('06/23/2015', 'mm/dd/yyyy' ) )     group         trim (regexp_substr (dept, '[^,]+', 1, level))     ) group     department

now, there's oracle specific stuff in there don't understand can see there's 2 different major steps here. there's first inner query, may or may not running efficiently. run on own see how goes. i'm going assume it's fine (it may not be).

the outer bit going suboptimal have correlated query in select clause. needs split out join:

select     dept.name,     sum (received),     sum (notresponded)     complex_inner_query     inner join dept on dept.deptid = complex_inner_query.department group     department

that may solve problems, if not need start analyzing inner query find inefficiency.


Comments

Post a Comment

Popular posts from this blog

python - No exponential form of the z-axis in matplotlib-3D-plots -

-
Image
i have similar problem described in how prevent numbers being changed exponential form in python matplotlib figure : i don't want (in special case) weird scientific formatting of axis. problem different have problem @ z -axis. 2-d plots can use ax.get_yaxis().get_major_formatter().set_useoffset(false) . , there no function ax.get_zaxis() what use format z -axis same way? edit: example: from mpl_toolkits.mplot3d import axes3d import numpy np import sys import matplotlib import matplotlib.pyplot pyplot def func(xi, ti): res = 10e3 + np.cos(ti) * np.sin(xi) return res if __name__ == '__main__': timespacing = 20 timestart = 0 timeend = 1 time = np.linspace(timestart, timeend, timespacing) widthspacing = 50 widthstart = 0 widthend = 3 width = np.linspace(widthstart, widthend, widthspacing) reslist = [] matplotlib.rcparams['legend.fontsize'] = 10 fig = pyplot.figure() ax = fig.gca(projection = '3d...
Read more

php - Best Light server (Linux + Web server + Database) for Raspberry Pi -

-
i install web server database on raspberry pi (little computer). computer has 1gb ram. i want know best combination: linux distribution , web server , dbms run local server multiple users minimal latency, use php on server. , best settings performance , not have bugs (memory usage, disable plugin, disable service, etc)? i thought light debian , lighttpd server , sqlite database. solution? i think lighttpd + sqlite great choice. linux distro, debian @ centos or tiny core linux , although i'm not sure of compatibility pi. obviously, can't go wrong raspbian if want use in production , more stable performance, few more pi's , set them in cluster .
Read more

c# - "Newtonsoft.Json.JsonSerializationException unable to find constructor to use for types" error when deserializing class -

-
i developing app in xamarin android. have splash screen serialize class , pass mainactivity using intent . when try deserialize in mainactivity error message : "serializationexception unable find constructor use types" serialization : void loaddata() { currency currency = new currency(this); intent = new intent(this, typeof(mainactivity)); intent.putextra("currency", newtonsoft.json.jsonconvert.serializeobject(currency)); startactivity(intent); } deserialization : cur = newtonsoft.json.jsonconvert.deserializeobject<currency> (intent.getstringextra("currency")); // error here class constructor : public currency(context context) { thiscontext = context; } i began experiencing problem after added parameter context context constructor. has caused this? how can solve it? possible serialize/deserialize class has parameters? you need have empty constructor in currency class in order deserialize it. ...
Read more