Oracle Indexes on Left Outer Joins

Question

So I'm having some issues with proper / any use of indexes in Oracle 11Gr2 and I'm trying to get a better understanding of how my explain plan ties back to my query so that I can apply indexing properly. When running the following query:

 SELECT JLOG1.JLOG_KEY,
            JLOG1.SRC_CD,
            JLOG1.JRNL_AMT,
            CASD.CONT_NO,
            SUM (NVL (VJLOG.TDTL_AMT, 0)) TDTL_SUM
       FROM GL_Journal_Logs JLOG1,
            GL_JLOG_Details VJLOG,
            CASE_DATA CASD
      WHERE  VJLOG.JLOG_KEY(+) = JLOG1.JLOG_KEY
            AND CASD.CASE_KEY(+) = JLOG1.CASE_KEY
            AND JLOG1.JRNL_CD = '0'
            AND JLOG1.SRC_CD = '2'
            AND JLOG1.ACCT_IF_CD = '0'
   GROUP BY JLOG1.JLOG_KEY, JLOG1.SRC_CD,JLOG1.JRNL_AMT, CASD.CONT_NO
    HAVING JLOG1.JRNL_AMT <> SUM (NVL (VJLOG.TDTL_AMT, 0));

I'm getting the following explain details:

I can understand that the indexes on my join "keys" (JLOG_KEY or CASE_KEY) wouldn't necessarily apply seeing as it's an outer join (or should they?), however when creating indexes on JLOG1 (JRNL_CD, SRC_CD, ACCT_IF_CD), technically would these take effect given my "where" clause?

Should I create any indexes at all given the circumstances or is there a better way of doing this?

Solution

Depending on the cardinality of the columns in your predicates, an appropriate index might be used on the GL_JLOG_DETAILS table, avoiding a full table scan. A covering index may avoid accessing the data pages at all:

ON GL_JOURNAL_LOGS (JRNL_CD,SRC_CD,ACCT_IF_CD,JLOG_KEY,CASE_KEY,JRNL_AMT)

(You probably want the column with the most selective predicate first in that index)

Also, your query may be able to make effective use of indexes

ON GL_JLOG_DETAILS (JLOG_KEY, TDTL_AMT) 

and

ON CASE_DATA (CASE_KEY, CONT_NO)

Also, be sure that the statistics on the tables and indexes are up-to-date.

---

Also, that (+) notation for an OUTER JOIN may be limiting the optimizer.
Oracle now supports the ANSI style joins, which may allow the optimizer more latitude in coming up with an execution plan, e.g.

  FROM GL_Journal_Logs JLOG1
  LEFT
  JOIN GL_JLOG_Details VJLOG ON VJLOG.JLOG_KEY = JLOG1.JLOG_KEY
  LEFT
  JOIN CASE_DATA CASD ON CASD.CASE_KEY = JLOG1.CASE_KEY
 WHERE JLOG1.JRNL_CD = '0'
       AND JLOG1.SRC_CD = '2'
       AND JLOG1.ACCT_IF_CD = '0'

Comments

[denisb]

Thank you so much for the input. The addition of the GL_JOURNAL_LOGS indexes mentioned above seem to have improved a bit. As far as the GL_LOG_DETAILS, because I'm using function around my TDTL_AMT, should I create function based index as part of that same index, or simply create function based index on that value alone? I read that anytime you use function for one of the value (e.g NVL(val,0)), the index is out the door.. what's the best practice is in this case. Would a simple index REBUILD do the same, or should I gather table stats before I run this every time on the tables affected?

[spencer7593]

@denisb: what you read is right. The NVL function wrapped around that column (in your query) prohibits an "index seek" operation on that column. But in your case, it doesn't matter. You are referencing that expression only in the SELECT list and in the HAVING clause, so it's a moot point. You want JLOG_KEY as the leading column in the index, since that is referenced in the join predicate. The purpose of also INCLUDING the TDTL_AMOUNT in the same index is so that it is a "covering" index... the query can be satisfied from just the index, without referencing any data pages.

[spencer7593]

@denisb: you could try adding an index ON GL_JLOG_DETAILS (JLOG_KEY, NVL(TDTL_AMT,0)). (But in your query, it's still only the predicate on JLOG_KEY that is sargable.) But before you do that, I recommend you consider rewriting your query to move that NVL function outside of the SUM function... I believe in your case, NVL(SUM(TDTL_AMT),0) is equivalent to SUM(NVL(TDTL_AMT,0)), those return the same value, even when there are no matching rows. That will bypass needing to run the NVL on every value and including zeros in the SUM. (The SUM aggregate is already doing a check for NULL.)

[Jon Heller]

+1 for recommending the ANSI syntax, which is usually much clearer. However, ANSI joins will not help the optimizer since Oracle still transforms all queries into the old syntax anyway: jonathanlewis.wordpress.com/2010/12/03/ansi-argh