PL\SQL Query Optimization (currently slow)

Question

I was wondering if the community could help me optimize this query but achieve the same results. Currently, it takes roughly 22 minutes to return. I have tried a few different things but they took longer.

Any help is appreciated!

GL_TYPE - SIZE 1MB 2409 ROWS
GL_DEFINITION - SIZE 1MB 53 ROWS
GL_JOURNAL - SIZE 1.24GB 5,725,500 or greater ROWS


  SELECT MAX (CT.ENTITY_ID) ENTITY_ID,
         MAX (CT.CASH_TYPE) RULE_CODE,
         SUM (
            (SELECT NVL (SUM (JB.CLOSING_BALANCE), 0)
               FROM GL_JOURNAL JB
              WHERE     JB.GL_PRIME_ACCT = CD.GL_PRIME_ACCT
                    AND JB.GL_SUB_ACCT = CD.GL_SUB_ACCT
                    AND JB.ENTITY_ID = CT.ENTITY_ID
                    AND JB.GL_SYS_PERIOD = '201509'
                    AND JB.GL_BASIS = 'NA'
                    AND JB.GL_SOURCE <> '000')) as BEG_BALANCE
    FROM GL_TYPE CT, GL_DEFINITION CD
   WHERE CT.TYPE_CODE = CD.TYPE_CODE
GROUP BY CT.ENTITY_ID, CT.TYPE_CODE



 SELECT STATEMENT
 COST 7
   SORT AGGREGATE
   BYTES: 45 Cardinality: 1
        TABLE ACCESS BY INDEX ROWID TABLE GL_JOURNAL
        COST 10 BYTES: 45 Cardinality: 10
        INDEX RANGE SCAN INDEX
        COST 3 Cardinality: 14

       HASH GROUP BY
       COST 7 BYTES: 69,861 CARDINALITY:2,409

       #HASH JOIN
            COST 5 BYTES: 412,467 CARDINALITY:14,223
               INDEX FULL SCAN INDEX (UNIQUE)
               COST 1: BYTES: 848 CARDINALITY:53
               INDEX FAST FULL SCAN INDEX (UNIQUE)
               COST 3 BYTES:31,317 CARDINALITY:2,409

fyi, this question is not related to PL/SQL or the `plsqldeveloper` tag. Maybe `sql-tuning` would be more appropriate. — William Robertson, Nov 11 '15 at 16:51

Gordon Linoff · Answer 1 · 2015-10-20T00:53:18.903

1

I suspect that moving the subquery to the from clause would be a win:

SELECT MAX(CT.ENTITY_ID) as ENTITY_ID,
       MAX(CT.CASH_TYPE) as RULE_CODE,
       COALESCE(SUM(JB.CLOSING_BALANCE), 0) as BEG_BALANCE
FROM GL_TYPE CT JOIN
     GL_DEFINITION CD
     ON CT.TYPE_CODE = CD.TYPE_CODE LEFT JOIN
     GL_JOURNAL JB
     ON JB.GL_PRIME_ACCT = CD.GL_PRIME_ACCT AND
        JB.GL_SUB_ACCT = CD.GL_SUB_ACCT AND
        JB.ENTITY_ID = CT.ENTITY_ID AND
        JB.GL_SYS_PERIOD = '201509' AND
        JB.GL_BASIS = 'NA' AND
        JB.GL_SOURCE <> '000'
GROUP BY CT.ENTITY_ID, CT.TYPE_CODE;

This query also wants to take advantage of indexes: GL_TYPE(TYPE_CODE), GL_DEFINITION(TYPE_CODE), GL_JOURNAL(GL_PRIME_ACCT, GL_SUB_ACCT, ENTITY_ID, GL_SYS_PERIOD, GL_BASIS, GL_SOURCE, CLOSING_BALANCE).

edited Oct 20 '15 at 00:53

answered Oct 17 '15 at 14:53

Gordon Linoff

1,242,037
58
646
786

I'll give this a try, it makes sense. I'll run some test. Thanks for your help. – Christopher Oct 17 '15 at 15:15
It worked and I think it was a success. Thanks alot! – Christopher Oct 18 '15 at 19:31
1

@Christopher: If the answer worked for you, please mark it as "Accepted answer". This will give credit to Gordon, but also shows others, that you care about the help you get. – cfi Nov 10 '15 at 10:32

PL\SQL Query Optimization (currently slow)

1 Answers1