SQL query taking 35 mins -
how can improve performance of below query? takes 35 minutes.
-- select select rpt_ft_md_flows.fecdata, rpt_ft_mc_contr.l2v, rpt_ft_md_flows.codcont, sum(rpt_ft_md_flows.impmocmp) rpt_ft_md_flows, rpt_ft_mc_contr -- joins rpt_ft_md_flows.idgruemp = rpt_ft_mc_contr.idgruemp , rpt_ft_md_flows.fecdata = rpt_ft_mc_contr.fecdata , rpt_ft_md_flows.codcont = rpt_ft_mc_contr.codcont , rpt_ft_md_flows.idempr = rpt_ft_mc_contr.idempr , rpt_ft_md_flows.idcent = rpt_ft_mc_contr.idcent , rpt_ft_md_flows.codprod = rpt_ft_mc_contr.codprod , rpt_ft_md_flows.idcontr = rpt_ft_mc_contr.idcontr , rpt_ft_md_flows.idscontr = rpt_ft_mc_contr.idscontr , -- filters rpt_ft_md_flows.fecdata = '31-may-2014' , rpt_ft_md_flows.idgruemp = '0022' , rpt_ft_md_flows.acumtemp = 'mth' , rpt_ft_md_flows.idescena = '01' , rpt_ft_md_flows.codcont='ccpp' --group group rpt_ft_md_flows.fecdata, rpt_ft_mc_contr.l2v, rpt_ft_md_flows.codcont rpt_ft_mc_contr: 39 million rowsrpt_ft_md_flows: 145 million rows
here result of explain command:
explain command
execution plan ---------------------------------------------------------- plan hash value: 2459895390 ------------------------------------------------------------------------------------------------------------------------------------------------ | id | operation | name | rows | bytes | cost (%cpu)| time | pstart| pstop | tq |in-out| pq distrib | ------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | select statement | | 268 | 29212 | 30846 (8)| 00:09:16 | | | | | | | 1 | px coordinator | | | | | | | | | | | | 2 | px send qc (random) | :tq10002 | 268 | 29212 | 30846 (8)| 00:09:16 | | | q1,02 | p->s | qc (rand) | | 3 | hash group | | 268 | 29212 | 30846 (8)| 00:09:16 | | | q1,02 | pcwp | | | 4 | px receive | | 268 | 29212 | 30846 (8)| 00:09:16 | | | q1,02 | pcwp | | | 5 | px send hash | :tq10001 | 268 | 29212 | 30846 (8)| 00:09:16 | | | q1,01 | p->p | hash | | 6 | hash group | | 268 | 29212 | 30846 (8)| 00:09:16 | | | q1,01 | pcwp | | |* 7 | hash join | | 39m| 4127m| 30586 (7)| 00:09:11 | | | q1,01 | pcwp | | | 8 | px block iterator | | 39m| 1893m| 10659 (4)| 00:03:12 | 1 | 16 | q1,01 | pcwc | | |* 9 | table access full | rpt_ft_mc_contr | 39m| 1893m| 10659 (4)| 00:03:12 | 833 | 848 | q1,01 | pcwp | | | 10 | px receive | | 145m| 8184m| 19806 (9)| 00:05:57 | | | q1,01 | pcwp | | | 11 | px send broadcast local| :tq10000 | 145m| 8184m| 19806 (9)| 00:05:57 | | | q1,00 | p->p | bcst local | | 12 | px block iterator | | 145m| 8184m| 19806 (9)| 00:05:57 | 66 | 66 | q1,00 | pcwc | | |* 13 | table access full | rpt_ft_md_flows | 145m| 8184m| 19806 (9)| 00:05:57 | 66 | 66 | q1,00 | pcwp | | ------------------------------------------------------------------------------------------------------------------------------------------------ predicate information (identified operation id): --------------------------------------------------- 7 - access("rpt_ft_md_flows"."codcont"="rpt_ft_mc_contr"."codcont" , "rpt_ft_md_flows"."fecdata"="rpt_ft_mc_contr"."fecdata" , "rpt_ft_md_flows"."idgruemp"="rpt_ft_mc_contr"."idgruemp" , "rpt_ft_md_flows"."idempr"="rpt_ft_mc_contr"."idempr" , "rpt_ft_md_flows"."idcent"="rpt_ft_mc_contr"."idcent" , "rpt_ft_md_flows"."codprod"="rpt_ft_mc_contr"."codprod" , "rpt_ft_md_flows"."idcontr"="rpt_ft_mc_contr"."idcontr" , "rpt_ft_md_flows"."idscontr"="rpt_ft_mc_contr"."idscontr") 9 - filter("rpt_ft_mc_contr"."idgruemp"='0022' , "rpt_ft_mc_contr"."fecdata"=to_date(' 2014-05-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 13 - filter("rpt_ft_md_flows"."idescena"='01' , "rpt_ft_md_flows"."fecdata"=to_date(' 2014-05-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') , "rpt_ft_md_flows"."idgruemp"='0022' , "rpt_ft_md_flows"."acumtemp"='mth')
alright reason or i'm going try query. lets start cleaning can used. first off start obliterating ancient join syntax. add aliases. aliasing when give title nickname can reference on place way don't clutter code repeated table names everywhere. gives this.
select f.fecdata, c.l2v, f.codcont, sum(f.impmocmp) rpt_ft_md_flows f inner join rpt_ft_mc_contr c on f.idgruemp = c.idgruemp , f.fecdata = c.fecdata , f.codcont = c.codcont , f.idempr = c.idempr , f.idcent = c.idcent , f.codprod = c.codprod , f.idcontr = c.idcontr , f.idscontr = c.idscontr f.fecdata = '31-may-2014' , f.idgruemp = '0022' , f.acumtemp = 'mth' , f.idescena = '01' , f.codcont='ccpp' group f.fecdata, c.l2v, f.codcont so lets start these join predicates. know claim these business rules necessary join 2 tables together? if table structure boned. there should 0 reason have 9 join predicates bring 2 tables together. increasing selectivity of query? if required table structure needs fixed.
next table doesn't seem taking advantage of indexes. if don't have should have sort of clustered index on these tables ease searching.
you want add other indexes table reducing many amount of data has scan/seek through results. there no reason query should have join 184 million rows return 268 rows.
Comments
Post a Comment