5 Replies Latest reply: Apr 29, 2014 8:14 AM by 956354 RSS

    Tunning an SQL query

    956354

      Hello everyone,

       

      I have an SQL query to tunne , because it used to take only 5s to finish and now it takes 5 minutes.

       

      I have oracle 11.2.0.1.0 - 64bit on Windows 2003

       

      Note:

      - I tried to gather statistics for all the tables used to in this query as they were old but nothing changed

      exec dbms_stats.gather_table_stats(ownname=>'TP',tabname=>'TIERS',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade=>true, method_opt=>'for all columns size AUTO');

       

      - I tried to use a hint the most used indexs but nothing chaged also .

       

      I advised the client to use Oracle tunning advisor to see it recommend us to do something that we are missing but still didn't got a respond of him.

       

      I'm thinking I should rebuild the indexs used to see if it can help

       

      Other than that can any body advise me on how to tune this query and is there any thing I can check .

       

      Regards

      Ismail

       

       

      Here is My query :

       

      SELECT cod.code_ref,

      decode (cod.code_ref ,'MAK','A',

                          'MAA','B',

                          'MAB','C',

                          'MAC','D',

                          'MAD','E',

                          'MAE','F',

                          'MAG','G',

      'MAL','L',

                          'MAF','M',

                          'MAH','N',

                          'MAI','O',

                          'MAJ','P',

                           'AUTRES'

                              )ordre

      , cod.valeur classe,AA.valeur  rubrique, null compte,

      decode(bb.exo,NULL,0,bb.exo) solde_deb,

      decode(cc.exo,NULL,0,cc.exo) acquis,

      decode(dd.exo,NULL,0,dd.exo) ces,

      decode(ee.exo,NULL,0,ee.exo) diff_est,

      decode(ff.exo,NULL,0,ff.exo) diff_intc,

      (select tr.description from tp.tiers tr where tr.code = '103') description,

      (select  g.gestionnaire

      from tp.vue_gestionnaire g

      where g.code  ='103' ) gestionnaire

       

       

      FROM ( SELECT a.code_ref, a.valeur,a.ordre

             FROM tp.param_codier a

             WHERE  a.classe IN ( 'DetailMvtActifRubrique')

             and a.flag_actif='O'

             ORDER BY ordre

           ) AA,

           ( SELECT b.groupe5, SUM(a.montant * decode (a.SENS,'D',1,-1)) exo

             FROM tp.histo_compta a, tp.compte_compta b

             WHERE a.compte_compta = b.code

             AND a.portefeuille = '103'

             AND a.statut ='V'

             and b.groupe5 is not null

             AND (  ( a.date_compta <= TO_DATE('31/12/2012','dd/mm/yyyy') AND

                     (a.journal <> 'CLOT'  or a.journal is null  ) )

                   OR

                    ( a.date_compta < TO_DATE('31/12/2012','dd/mm/yyyy') AND a.journal = 'CLOT')

                 )

             GROUP BY groupe5

           )BB,

           (  SELECT c.groupe5, SUM ( a.montant) exo

                FROM   tp.histo_compta a, tp.compte_compta c          --, tp.titre t

               WHERE       a.date_compta > to_date('31/12/2012','dd/mm/yyyy')

                       AND a.date_compta <= to_date('31/12/2013','dd/mm/yyyy')

                       AND a.statut = 'V'

                       AND c.code = a.compte_compta

                       and c.groupe5 is not null

                       AND a.portefeuille = '103'

                       AND nvl(c.groupe3,'VIDE') not in ('EST2','IC')

                       AND a.sens = 'D'

               GROUP BY groupe5

            )CC,

            ( SELECT c.groupe5, SUM ( a.montant) exo

                FROM   tp.histo_compta a, tp.compte_compta c          --, tp.titre t

               WHERE       a.date_compta > to_date('31/12/2012','dd/mm/yyyy')

                       AND a.date_compta <= to_date('31/12/2013','dd/mm/yyyy')

                       AND a.statut = 'V'

                       AND c.code = a.compte_compta

                       and c.groupe5 is not null

                       AND a.portefeuille = '103'

                       AND nvl(c.groupe3,'VIDE') not in ('EST2','IC')

                       AND a.sens = 'C'

               GROUP BY groupe5

           )DD,

           (     SELECT c.groupe5,  SUM (DECODE (a.sens, 'D', 1, -1) * a.montant) exo

                FROM   tp.histo_compta a, tp.compte_compta c          --, tp.titre t

               WHERE       a.date_compta > to_date('31/12/2012','dd/mm/yyyy')

                       AND a.date_compta <= to_date('31/12/2013','dd/mm/yyyy')

                       AND a.statut = 'V'

                       AND c.code = a.compte_compta

                       and c.groupe5 is not null

                       AND a.portefeuille = '103'

                       AND c.groupe3 = 'EST2'

          

           GROUP BY groupe5

                    

                    

           )EE,

            (     SELECT c.groupe5,SUM (DECODE (a.sens, 'D', 1, -1) * a.montant) exo

       

       

                FROM   tp.histo_compta a, tp.compte_compta c          --, tp.titre t

               WHERE       a.date_compta > to_date('31/12/2012','dd/mm/yyyy')

                       AND a.date_compta <= to_date('31/12/2013','dd/mm/yyyy')

                       AND a.statut = 'V'

                       AND c.code = a.compte_compta

                       and c.groupe5 is not null

                       AND a.portefeuille = '103'

                       AND c.groupe3 = 'IC'

            GROUP BY groupe5

                    

                    

           )FF,tp.param_codier cod

       

       

      WHERE AA.code_ref= BB.groupe5

      AND AA.code_ref= CC.groupe5

      AND AA.code_ref= DD.groupe5

      AND AA.code_ref= EE.groupe5

      AND AA.code_ref= FF.groupe5

      and cod.classe='DetailMvtActifClasse'

      --and cod.code_ref not in ('ARH')            

      and ((substr(AA.code_ref,1,3)=cod.code_ref and length(AA.code_ref)=4)

        or (substr(AA.code_ref,1,4)=cod.code_ref and length(AA.code_ref)=5))

      ORDER BY cod.ordre, AA.ordre

       

       

      Here is the execution plan (Sorry it's in XML format converted to excel)

       

      idoperationoptimizercostcardinalitybytescpu_costio_costtimeobject_IDid2operation3optionoptimizer4object_ownerobject_nameobject_typeobject_instancecost5cardinality6bytes7cpu_cost8io_cost9time10object_ID11id12operation13option14optimizer15object_owner16object_name17object_type18search_columnscost19cardinality20cpu_cost21io_cost22access_predicatestime23object_instance24bytes25filter_predicatesobject_ID26id27operation28option29optimizer30object_owner31object_name32object_type33search_columns34cost35cardinality36cpu_cost37io_cost38access_predicates39time40bytes41object_instance42id43operation44option45cost46cardinality47bytes48cpu_cost49io_cost50time51object_owner52object_instance53filter_predicates54id55operation56option57cost58cardinality59bytes60cpu_cost61io_cost62time63object_owner64object_instance65filter_predicates66id67operation68option69cost70cardinality71bytes72cpu_cost73io_cost74time75object_owner76object_instance77filter_predicates78id79operation80cost81cardinality82bytes83cpu_cost84io_cost85time86object_owner87object_instance88filter_predicates89option90access_predicates91object_ID92optimizer93object_name94object_type95object_ID96id97operation98option99object_owner100object_name101object_type102object_instance103cost104cardinality105bytes106cpu_cost107io_cost108time109filter_predicates110access_predicates111optimizer112search_columns113object_ID114id115operation116option117optimizer118object_owner119object_name120object_type121search_columns122cost123cardinality124cpu_cost125io_cost126access_predicates127filter_predicates128time129bytes130object_instance131id132operation133cost134cardinality135bytes136cpu_cost137io_cost138time139object_ID140option141optimizer142object_owner143object_name144object_type145object_instance146filter_predicates147search_columns148access_predicates149id150operation151cost152cardinality153bytes154cpu_cost155io_cost156time157object_ID158option159optimizer160object_owner161object_name162object_type163object_instance164filter_predicates165search_columns166access_predicates167object_ID168id169operation170option171optimizer172object_owner173object_name174object_type175object_instance176cost177cardinality178bytes179cpu_cost180io_cost181filter_predicates182time183search_columns184access_predicates185object_ID186id187operation188option189optimizer190object_owner191object_name192object_type193search_columns194cost195cardinality196cpu_cost197io_cost198access_predicates199filter_predicates200time201
      0SELECT STATEMENTALL_ROWS15 7491325197 374 50815 73518901TABLE ACCESSBY INDEX ROWIDANALYZEDTPTIERSTABLE1212515 4632112INDEXUNIQUE SCANANALYZEDTPPK_TIERSINDEX (UNIQUE)1118 1711"TR"."CODE"='103'1
      0SELECT STATEMENTALL_ROWS15 7491325197 374 50815 7351893NESTED LOOPS2117717 5932104TABLE ACCESSBY INDEX ROWIDANALYZEDTPTIERSTABLE2117 393212364"T"."EST_OPCVM"='O' AND "T"."FLAG_ACTIF"='O'15INDEXUNIQUE SCANANALYZEDTPPK_TIERSINDEX (UNIQUE)1118 1711"T"."CODE"='103'1
      0SELECT STATEMENTALL_ROWS15 7491325197 374 50815 7351893NESTED LOOPS2117717 5932126TABLE ACCESSBY INDEX ROWIDTPPARAM_CODIER_SYSTABLE01200012411337INDEXRANGE SCANANALYZEDTPPK_PARAM_CODIER_SYSINDEX (UNIQUE)2012000"A"."CLASSE"='GESTIONNAIRE' AND "A"."CODE_REF"=NVL("T"."ZONE_TEXT15",'WAFAG')1
      0SELECT STATEMENTALL_ROWS15 7491325197 374 50815 7351898SORTORDER BY15 7491325197 374 50815 7351899NESTED LOOPSOUTER15 7481183 167 68215 73518932510NESTED LOOPSOUTER909135 923 0929061131211NESTED LOOPSOUTER681129923 445 510679912NESTED LOOPSOUTER453128610 971 297452613NESTED LOOPSOUTER22612735 489 224226314NESTED LOOPS012607 15001215TABLE ACCESSBY INDEX ROWIDTPPARAM_CODIER_SYSTABLE210121 54855001316INDEXRANGE SCANANALYZEDTPPK_PARAM_CODIER_SYSINDEX (UNIQUE)2015500"A"."CLASSE"='DetailMvtActifClasse' AND "A"."LANGUE"='fr'"A"."LANGUE"='fr'1
      0SELECT STATEMENTALL_ROWS15 7491325197 374 50815 7351898SORTORDER BY15 7491325197 374 50815 7351899NESTED LOOPSOUTER15 7481183 167 68215 73518932510NESTED LOOPSOUTER909135 923 0929061131211NESTED LOOPSOUTER681129923 445 510679912NESTED LOOPSOUTER453128610 971 297452613NESTED LOOPSOUTER22612735 489 224226314NESTED LOOPS012607 15001217TABLE ACCESSBY INDEX ROWIDTPPARAM_CODIER_SYSTABLE220113155001"A"."FLAG_ACTIF"='O'318INDEXRANGE SCANANALYZEDTPPK_PARAM_CODIER_SYSINDEX (UNIQUE)2015500"A"."CLASSE"='DetailMvtActifRubrique' AND "A"."LANGUE"='fr'"A"."LANGUE"='fr' AND ("A"."CODE_REF"=SUBSTR("A"."CODE_REF",1,3) AND LENGTH("A"."CODE_REF")=4 OR "A"."CODE_REF"=SUBSTR("A"."CODE_REF",1,4) AND LENGTH("A"."CODE_REF")=5)1
      0SELECT STATEMENTALL_ROWS15 7491325197 374 50815 7351898SORTORDER BY15 7491325197 374 50815 7351899NESTED LOOPSOUTER15 7481183 167 68215 73518932510NESTED LOOPSOUTER909135 923 0929061131211NESTED LOOPSOUTER681129923 445 510679912NESTED LOOPSOUTER453128610 971 297452613NESTED LOOPSOUTER22612735 489 224226319VIEW PUSHED PREDICATE2261135 482 0742263TP1420FILTERCOUNT(*)>021SORTAGGREGATE14622NESTED LOOPS23NESTED LOOPS2261465 482 0742263424TABLE ACCESSBY INDEX ROWIDANALYZEDTPCOMPTE_COMPTATABLE16211415 3012"C"."GROUPE3"='EST2'1525INDEXRANGE SCANANALYZEDTPIDX_CPTA_GRP5INDEX1127 5211"C"."GROUPE5"="A"."CODE_REF""C"."GROUPE5" IS NOT NULL1
      0SELECT STATEMENTALL_ROWS15 7491325197 374 50815 7351898SORTORDER BY15 7491325197 374 50815 7351899NESTED LOOPSOUTER15 7481183 167 68215 73518932510NESTED LOOPSOUTER909135 923 0929061131211NESTED LOOPSOUTER681129923 445 510679912NESTED LOOPSOUTER453128610 971 297452613NESTED LOOPSOUTER22612735 489 224226319VIEW PUSHED PREDICATE2261135 482 0742263TP1420FILTERCOUNT(*)>021SORTAGGREGATE14622NESTED LOOPS23NESTED LOOPS2261465 482 0742263626INDEXRANGE SCANANALYZEDTPNDX_DATE_COMPTAINDEX134 177928 8291311"A"."DATE_COMPTA">TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."DATE_COMPTA"<=TO_DATE(' 2013-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
      0SELECT STATEMENTALL_ROWS15 7491325197 374 50815 7351898SORTORDER BY15 7491325197 374 50815 7351899NESTED LOOPSOUTER15 7481183 167 68215 73518932510NESTED LOOPSOUTER909135 923 0929061131211NESTED LOOPSOUTER681129923 445 510679912NESTED LOOPSOUTER453128610 971 297452613NESTED LOOPSOUTER22612735 489 224226319VIEW PUSHED PREDICATE2261135 482 0742263TP1420FILTERCOUNT(*)>021SORTAGGREGATE14622NESTED LOOPS27TABLE ACCESS2242645 466 77222437BY INDEX ROWIDANALYZEDTPHISTO_COMPTATABLE15"A"."PORTEFEUILLE"='103' AND "A"."STATUT"='V' AND "C"."CODE"="A"."COMPTE_COMPTA"
      0SELECT STATEMENTALL_ROWS15 7491325197 374 50815 7351898SORTORDER BY15 7491325197 374 50815 7351899NESTED LOOPSOUTER15 7481183 167 68215 73518932510NESTED LOOPSOUTER909135 923 0929061131211NESTED LOOPSOUTER681129923 445 510679912NESTED LOOPSOUTER453128610 971 297452628VIEW PUSHED PREDICATE2261135 482 0742263TP1729FILTERCOUNT(*)>030SORTAGGREGATE14631NESTED LOOPS32NESTED LOOPS2261465 482 074226333TABLE ACCESS211415 301214BY INDEX ROWIDANALYZEDTPCOMPTE_COMPTATABLE19"C"."GROUPE3"='IC'534INDEXRANGE SCANANALYZEDTPIDX_CPTA_GRP5INDEX127 5211"C"."GROUPE5" IS NOT NULL11"C"."GROUPE5"="A"."CODE_REF"
      0SELECT STATEMENTALL_ROWS15 7491325197 374 50815 7351898SORTORDER BY15 7491325197 374 50815 7351899NESTED LOOPSOUTER15 7481183 167 68215 73518932510NESTED LOOPSOUTER909135 923 0929061131211NESTED LOOPSOUTER681129923 445 510679912NESTED LOOPSOUTER453128610 971 297452628VIEW PUSHED PREDICATE2261135 482 0742263TP1729FILTERCOUNT(*)>030SORTAGGREGATE14631NESTED LOOPS32NESTED LOOPS2261465 482 074226335INDEX134 177928 8291316RANGE SCANANALYZEDTPNDX_DATE_COMPTAINDEX1"A"."DATE_COMPTA">TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."DATE_COMPTA"<=TO_DATE(' 2013-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
      0SELECT STATEMENTALL_ROWS15 7491325197 374 50815 7351898SORTORDER BY15 7491325197 374 50815 7351899NESTED LOOPSOUTER15 7481183 167 68215 73518932510NESTED LOOPSOUTER909135 923 0929061131211NESTED LOOPSOUTER681129923 445 510679912NESTED LOOPSOUTER453128610 971 297452628VIEW PUSHED PREDICATE2261135 482 0742263TP1729FILTERCOUNT(*)>030SORTAGGREGATE14631NESTED LOOPS36TABLE ACCESS2242645 466 77222437BY INDEX ROWIDANALYZEDTPHISTO_COMPTATABLE18"A"."PORTEFEUILLE"='103' AND "A"."STATUT"='V' AND "C"."CODE"="A"."COMPTE_COMPTA"
      0SELECT STATEMENTALL_ROWS15 7491325197 374 50815 7351898SORTORDER BY15 7491325197 374 50815 7351899NESTED LOOPSOUTER15 7481183 167 68215 73518932510NESTED LOOPSOUTER909135 923 0929061131211NESTED LOOPSOUTER681129923 445 510679937VIEW PUSHED PREDICATE22811312 474 2122273TP1138FILTERCOUNT(*)>039SORT146AGGREGATE40HASH JOIN228627612 474 2122273"C"."CODE"="A"."COMPTE_COMPTA"441TABLE ACCESSBY INDEX ROWIDANALYZEDTPCOMPTE_COMPTATABLE2415 3082NVL("C"."GROUPE3",'VIDE')<>'EST2' AND NVL("C"."GROUPE3",'VIDE')<>'IC'1561342INDEX127 521115RANGE SCANANALYZEDTPIDX_CPTA_GRP5INDEX"C"."GROUPE5" IS NOT NULL1"C"."GROUPE5"="A"."CODE_REF"
      0SELECT STATEMENTALL_ROWS15 7491325197 374 50815 7351898SORTORDER BY15 7491325197 374 50815 7351899NESTED LOOPSOUTER15 7481183 167 68215 73518932510NESTED LOOPSOUTER909135 923 0929061131211NESTED LOOPSOUTER681129923 445 510679937VIEW PUSHED PREDICATE22811312 474 2122273TP1138FILTERCOUNT(*)>039SORT146AGGREGATE40HASH JOIN228627612 474 2122273"C"."CODE"="A"."COMPTE_COMPTA"743TABLE ACCESSBY INDEX ROWIDANALYZEDTPHISTO_COMPTATABLE2252325 331 691225"A"."PORTEFEUILLE"='103' AND "A"."SENS"='C' AND "A"."STATUT"='V'37 4241244INDEX144 177935 1001416RANGE SCANANALYZEDTPNDX_DATE_COMPTAINDEX1"A"."DATE_COMPTA">TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."DATE_COMPTA"<=TO_DATE(' 2013-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
      0SELECT STATEMENTALL_ROWS15 7491325197 374 50815 7351898SORTORDER BY15 7491325197 374 50815 7351899NESTED LOOPSOUTER15 7481183 167 68215 73518932510NESTED LOOPSOUTER909135 923 0929061131245VIEW PUSHED PREDICATE22811312 477 5822273TP846FILTERCOUNT(*)>047SORTAGGREGATE14648HASH JOIN228627612 477 5822273"C"."CODE"="A"."COMPTE_COMPTA"449TABLE ACCESSBY INDEX ROWIDTPCOMPTE_COMPTATABLE10245615 30821NVL("C"."GROUPE3",'VIDE')<>'EST2' AND NVL("C"."GROUPE3",'VIDE')<>'IC'ANALYZED550INDEXRANGE SCANANALYZEDTPIDX_CPTA_GRP5INDEX1127 5211"C"."GROUPE5"="A"."CODE_REF""C"."GROUPE5" IS NOT NULL1
      0SELECT STATEMENTALL_ROWS15 7491325197 374 50815 7351898SORTORDER BY15 7491325197 374 50815 7351899NESTED LOOPSOUTER15 7481183 167 68215 73518932510NESTED LOOPSOUTER909135 923 0929061131245VIEW PUSHED PREDICATE22811312 477 5822273TP846FILTERCOUNT(*)>047SORTAGGREGATE14648HASH JOIN228627612 477 5822273"C"."CODE"="A"."COMPTE_COMPTA"751TABLE ACCESSBY INDEX ROWIDTPHISTO_COMPTATABLE92252518 0325 333 1612253"A"."PORTEFEUILLE"='103' AND "A"."SENS"='D' AND "A"."STATUT"='V'ANALYZED652INDEXRANGE SCANANALYZEDTPNDX_DATE_COMPTAINDEX1144 177935 10014"A"."DATE_COMPTA">TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."DATE_COMPTA"<=TO_DATE(' 2013-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')1
      0SELECT STATEMENTALL_ROWS15 7491325197 374 50815 7351898SORTORDER BY15 7491325197 374 50815 7351899NESTED LOOPSOUTER15 7481183 167 68215 73518932553VIEW PUSHED PREDICATETP14 8391147 244 59014 82917913554FILTERCOUNT(*)>055SORTAGGREGATE14856NESTED LOOPS57NESTED LOOPS14 8397 590364 320147 244 59014 829179458TABLE ACCESSBY INDEX ROWIDTPCOMPTE_COMPTATABLE7244415 29321ANALYZED559INDEXRANGE SCANANALYZEDTPIDX_CPTA_GRP5INDEX1127 5211"B"."GROUPE5"="A"."CODE_REF""B"."GROUPE5" IS NOT NULL1
      0SELECT STATEMENTALL_ROWS15 7491325197 374 50815 7351898SORTORDER BY15 7491325197 374 50815 7351899NESTED LOOPSOUTER15 7481183 167 68215 73518932553VIEW PUSHED PREDICATETP14 8391147 244 59014 82917913554FILTERCOUNT(*)>055SORTAGGREGATE14856NESTED LOOPS57NESTED LOOPS14 8397 590364 320147 244 59014 829179860INDEXRANGE SCANTPIDX_CPTA_CPTINDEX4115 6183 416 429411"A"."COMPTE_COMPTA"="B"."CODE"ANALYZED1
      0SELECT STATEMENTALL_ROWS15 7491325197 374 50815 7351898SORTORDER BY15 7491325197 374 50815 7351899NESTED LOOPSOUTER15 7481183 167 68215 73518932553VIEW PUSHED PREDICATETP14 8391147 244 59014 82917913554FILTERCOUNT(*)>055SORTAGGREGATE14856NESTED LOOPS61TABLE ACCESS5 5841 76565 30553 871 0545 58068TP6###################BY INDEX ROWID7ANALYZEDHISTO_COMPTATABLE

       

      Thanks for your help

        • 1. Re: Tunning an SQL query
          BluShadow

          Question now moved to the SQL and PL/SQL forum as it's more appropriate.

          • 2. Re: Tunning an SQL query
            Roger

            Have you tried to combind CC - FF into something like:

             

            SELECT c.groupe5

                 , SUM (DECODE(c.groupe3,'EST2',NULL,'IC',NULL,DECODE(A.sens,'D',A.montant)))   exo_cc

                 , SUM (DECODE(c.groupe3,'EST2',NULL,'IC',NULL,DECODE(A.sens,'C',A.montant)))   exo_dd

                 , SUM (DECODE(c.groupe3,'EST2',DECODE(A.sens,'D',1,-1) * A.montant))           exo_ee

                 , SUM (DECODE(c.groupe3,'IC'  ,DECODE(A.sens,'D',1,-1) * A.montant))           exo_ff

              FROM            tp.histo_compta A

                   INNER JOIN tp.compte_compta c on (c.code = a.compte_compta)

            WHERE A.date_compta > to_date('31/12/2012','dd/mm/yyyy')

               AND A.date_compta <= to_date('31/12/2013','dd/mm/yyyy')

               AND A.statut = 'V'

               AND c.groupe5 IS NOT NULL

               AND A.portefeuille = '103'

               AND NVL(c.groupe3,'VIDE') NOT IN ('IC')

            GROUP BY groupe5;

             

            as far as I've seen the tables are same and most of the conditions too. The differences can be handled in DECODE or CASE statements.

             

            hth

            • 3. Re: Tunning an SQL query
              956354

              Hello ,

               

              Thanks for your reply I really appreciate it .

               

              So If I understand corrcetly the query should look like this with your advise :

               

              -Do you I have to check anything else in the tables used / database /Indexs because I feel that something has changed since the last time he told me that the query used to take 5s  and the funny thing is that he doesn't remember when so Bummer.

               

              SELECT cod.code_ref,

              decode (cod.code_ref ,'MAK','A',

                                  'MAA','B',

                                  'MAB','C',

                                  'MAC','D',

                                  'MAD','E',

                                  'MAE','F',

                                  'MAG','G',

              'MAL','L',

                                  'MAF','M',

                                  'MAH','N',

                                  'MAI','O',

                                  'MAJ','P',

                                   'AUTRES'

                                      )ordre

              , cod.valeur classe,AA.valeur  rubrique, null compte,

              decode(bb.exo,NULL,0,bb.exo) solde_deb,

              decode(CCDDEEFF.exo,NULL,0,CCDDEEFF.exo) acquis,

              decode(CCDDEEFF.exo,NULL,0,CCDDEEFF.exo) ces,

              decode(CCDDEEFF.exo,NULL,0,CCDDEEFF.exo) diff_est,

              decode(CCDDEEFF.exo,NULL,0,CCDDEEFF.exo) diff_intc,

              (select tr.description from tp.tiers tr where tr.code = '103') description,

              (select  g.gestionnaire

              from tp.vue_gestionnaire g

              where g.code  ='103' ) gestionnaire

               

               

              FROM ( SELECT a.code_ref, a.valeur,a.ordre

                     FROM tp.param_codier a

                     WHERE  a.classe IN ( 'DetailMvtActifRubrique')

                     and a.flag_actif='O'

                     ORDER BY ordre

                   ) AA,

                   ( SELECT b.groupe5, SUM(a.montant * decode (a.SENS,'D',1,-1)) exo

                     FROM tp.histo_compta a, tp.compte_compta b

                     WHERE a.compte_compta = b.code

                     AND a.portefeuille = '103'

                     AND a.statut ='V'

                     and b.groupe5 is not null

                     AND (  ( a.date_compta <= TO_DATE('31/12/2012','dd/mm/yyyy') AND

                             (a.journal <> 'CLOT'  or a.journal is null  ) )

                           OR

                            ( a.date_compta < TO_DATE('31/12/2012','dd/mm/yyyy') AND a.journal = 'CLOT')

                         )

                     GROUP BY groupe5

                   )BB,

                   ( SELECT c.groupe5

                   , SUM (DECODE(c.groupe3,'EST2',NULL,'IC',NULL,DECODE(A.sens,'D',A.montant)))   exo_cc

                   , SUM (DECODE(c.groupe3,'EST2',NULL,'IC',NULL,DECODE(A.sens,'C',A.montant)))   exo_dd

                   , SUM (DECODE(c.groupe3,'EST2',DECODE(A.sens,'D',1,-1) * A.montant))           exo_ee

                   , SUM (DECODE(c.groupe3,'IC'  ,DECODE(A.sens,'D',1,-1) * A.montant))           exo_ff

                FROM  tp.histo_compta A

                     INNER JOIN tp.compte_compta c on (c.code = a.compte_compta)

              WHERE A.date_compta > to_date('31/12/2012','dd/mm/yyyy')

                 AND A.date_compta <= to_date('31/12/2013','dd/mm/yyyy')

                 AND A.statut = 'V'

                 AND c.groupe5 IS NOT NULL

                 AND A.portefeuille = '103'

                 AND NVL(c.groupe3,'VIDE') NOT IN ('IC')

              GROUP BY groupe5; ) CCDDEEFF,tp.param_codier cod

               

               

              WHERE AA.code_ref= BB.groupe5

              AND AA.code_ref= CCDDEEFF.groupe5

              AND AA.code_ref= CCDDEEFF.groupe5

              AND AA.code_ref= CCDDEEFF.groupe5

              AND AA.code_ref= CCDDEEFF.groupe5

              and cod.classe='DetailMvtActifClasse'

              --and cod.code_ref not in ('ARH')            

              and ((substr(AA.code_ref,1,3)=cod.code_ref and length(AA.code_ref)=4)

                or (substr(AA.code_ref,1,4)=cod.code_ref and length(AA.code_ref)=5))

               

               

              ORDER BY cod.ordre, AA.ordre

               

              Thanks Again

              Regards

              Ismail

              • 4. Re: Tunning an SQL query
                Roger

                here:

                 

                decode(CCDDEEFF.exo,NULL,0,CCDDEEFF.exo) acquis,

                decode(CCDDEEFF.exo,NULL,0,CCDDEEFF.exo) ces,

                decode(CCDDEEFF.exo,NULL,0,CCDDEEFF.exo) diff_est,

                decode(CCDDEEFF.exo,NULL,0,CCDDEEFF.exo) diff_intc,

                 

                you do not have a column called exo in ccddeeff (they are called exo_cc, exo_dd, exo_ee and exo_ff)

                 

                and the same logic using nvl(col,0) is much easier to read

                 

                 

                here:

                 

                AND AA.code_ref= CCDDEEFF.groupe5

                AND AA.code_ref= CCDDEEFF.groupe5

                AND AA.code_ref= CCDDEEFF.groupe5

                AND AA.code_ref= CCDDEEFF.groupe5

                 

                having 4 times the same condition is somehow .. it's just not needed

                 

                 

                 

                hth

                • 5. Re: Tunning an SQL query
                  956354

                  Hello ,

                   

                  Thanks again.

                   

                  So I changed this :

                  decode(CCDDEEFF.exo,NULL,0,CCDDEEFF.exo) acquis,

                  decode(CCDDEEFF.exo,NULL,0,CCDDEEFF.exo) ces,

                  decode(CCDDEEFF.exo,NULL,0,CCDDEEFF.exo) diff_est,

                  decode(CCDDEEFF.exo,NULL,0,CCDDEEFF.exo) diff_intc,

                   

                  by this:

                  decode(CCDDEEFF.exo_cc,NULL,0,CCDDEEFF.exo_cc) acquis,

                  decode(CCDDEEFF.exo_dd,NULL,0,CCDDEEFF.exo_dd) ces,

                  decode(CCDDEEFF.exo_ee,NULL,0,CCDDEEFF.exo_ee) diff_est,

                  decode(CCDDEEFF.exo_ff,NULL,0,CCDDEEFF.exo_ff) diff_intc,

                   

                  And kept just this :

                   

                  AND AA.code_ref= CCDDEEFF.groupe5

                   

                  I will at the client HQ tomorrow morning and I will test this and let you know the result

                   

                  Thanks

                  Regards

                  Ismail