7 Replies Latest reply: Jul 13, 2010 9:01 AM by Sven W. RSS

    Long running query

    727688
      Hi, 
      
        I have query like this which runs for long time.
      
      SELECT b.rev_credit_tier
        FROM team b, assoc c, ml_emp d, empl e, deal_xref f
       WHERE e.key_empl = b.key_empl
             AND f.c_deal_prod_no = b.deal_prod_no
             AND f.c_deal_no = b.deal_no
             AND d.cab_wkr_id = nvl(c.person_number, -1)
             AND e.employee_id = d.employee_id
             AND b.credit_tier IS NOT NULL
             AND decode(d.cab_wkr_id, '-1', 213, c.id) = i.assoc_id
             AND f.cibr_deal_id = i.deal_id;
      Explain plan of the above query
      
      SELECT STATEMENT, GOAL = CHOOSE                    5019     1     81
       HASH JOIN                              5019     1     81
        NESTED LOOPS                              4     1     68
         NESTED LOOPS                              3     1     49
          NESTED LOOPS                         2     1     33
           TABLE ACCESS BY INDEX ROWID     DEAL_XREF     1     1     16
            INDEX RANGE SCAN     IN_CAB_XREF_3               1     1     
           TABLE ACCESS BY INDEX ROWID     TEAM          1     1     17
            INDEX RANGE SCAN     I_BRL_DPNO               1     4     
          TABLE ACCESS BY INDEX ROWID     EMPL               1     1     16
           INDEX UNIQUE SCAN     PK_EMPLE               1     1     
         TABLE ACCESS BY INDEX ROWID     ML_EMP               1     1     19
          INDEX RANGE SCAN     IDX_ML_EMP_ID               1     1     
        TABLE ACCESS FULL     ASSOC                    5006     1076498     13994474
      In the above explain plan, it shows full table scan for ASSOC table becuse of NVL function. How to avoid this and also we dont have privilege to create a function based index on that table column which is in another schema.The count of records in ASSOC table is 1 million
        • 1. Re: Long running query
          780914
          Which oracle version are you working?
          Are you using synonym for ASSOC.

          Best would be creating a materialized view on ASSOC table, then indexing it to optimize your query.

          Alternatively, if you are working on oracle 11g, then you can add a virtual column and index it in your schema.
          • 2. Re: Long running query
            _Karthick_
            What are the columns that are indexed in the table ASSOC. Is c.person_number a unique column?

            Did you consider creating a function based index nvl(c.person_number, -1) on ASSOC? This could be helpful if person_number is always going to be referred as nvl(c.person_number, -1)
            • 3. Re: Long running query
              727688
              Hi karthik,
              
                           yes person_number a unique column which has an unique index.
              
                           No,person_number is not going to be referred as nvl(c.person_number, -1) always.
              • 4. Re: Long running query
                _Karthick_
                Did you try creating a Function based index.

                Or may be try this
                and (d.cab_wkr_id = c.person_number or (c.person_number is null and d.cab_wkr_id = -1))
                Not sure if this will use the index. But just give it a try
                • 5. Re: Long running query
                  727688
                  We dont have access to create function based on that schema.
                  
                  I tried using this
                  
                  and (d.cab_wkr_id = c.person_number or (c.person_number is null and d.cab_wkr_id = -1))
                  
                  but it didnt use the index
                  • 6. Re: Long running query
                    riedelme
                    Mixing AND and OR condtions together in the same query can slow things down too :(

                    If you can't create a FBI can you add a column to the table with the fbi value and put a normal index on it? Or, better, if on 11g use SBH's idea of a virtual column and put an index on it (assuming this is possible)?

                    Edited by: riedelme on Jul 13, 2010 6:06 AM
                    • 7. Re: Long running query
                      Sven W.
                      Maybe like this
                      SELECT b.rev_credit_tier
                        FROM team b, assoc c, ml_emp d, empl e, deal_xref f
                       WHERE e.key_empl = b.key_empl
                             AND f.c_deal_prod_no = b.deal_prod_no
                             AND f.c_deal_no = b.deal_no
                             AND d.cab_wkr_id = c.person_number
                             AND e.employee_id = d.employee_id
                             AND b.credit_tier IS NOT NULL
                             AND c.id = i.assoc_id
                             AND f.cibr_deal_id = i.deal_id
                             AND d.cab_wkr_id != -1
                      UNION ALL
                      SELECT b.rev_credit_tier
                        FROM team b, assoc c, ml_emp d, empl e, deal_xref f
                       WHERE e.key_empl = b.key_empl
                             AND f.c_deal_prod_no = b.deal_prod_no
                             AND f.c_deal_no = b.deal_no
                             AND d.cab_wkr_id = -1
                             AND e.employee_id = d.employee_id
                             AND b.credit_tier IS NOT NULL
                             AND 213 = i.assoc_id
                             AND f.cibr_deal_id = i.deal_id;
                      Since there is no table i, are you in any way not showing the whole query? Since table i is involved in the OR logic, this might be an over simplification of the problem.