5 Replies Latest reply: Nov 14, 2012 2:54 PM by odie_63 RSS

    Need to change existing SQL of a view

    amitavachatterjee1975
      Hi,

      My current view SQL contains a LEFT OUTER JOIN and a GROUP BY clause as a result optimizer is not picking up the correct indexes for the tables.
      The view is

      CREATE OR REPLACE FORCE VIEW "V_IDS_APPSBYIDANDCATEGORY" ("CONTROLNUMBER", "MODTIMESTAMP", "TXNSTATUSCODE", "TXNSTATUSNOTE", "DATASTATUSCODE", "DATASTATUSNOTE", "COMMITLEVEL", "CREATETIMESTAMP", "TRANSACTIONTYPE", "TXNID", "LASTNAME", "FIRSTNAME", "MIDDLENAME", "SUFFIX", "SEX", "DOB", "ADDRESS1", "ADDRESS2", "CITY", "STATE", "COUNTRY", "ZIP", "DEMDATATYPE", "DEMDATAID", "RECORDQUEUEID", "DLID", "CUSTOMERID") AS

      SELECT
      T.CONTROLNUMBER, T.MODTIMESTAMP, T.TXNSTATUSCODE, T.TXNSTATUSNOTE, T.DATASTATUSCODE, T.DATASTATUSNOTE,
      DECODE(T.COMMITLEVEL,'Committed','Complete','In Progress'), T.CREATETIMESTAMP, T.TRANSACTIONTYPE, T.TXNID,

      D.LASTNAME, D.FIRSTNAME, D.MIDDLENAME, D.SUFFIX, D.SEX, D.DOB, D.ADDRESS1, D.ADDRESS2, D.CITY, D.STATE, D.COUNTRY, D.ZIP, D.DEMDATATYPE, D.DEMDATAID,

      N.IDNUMBER RECORDQUEUEID,

      O.DLID, O.CUSTOMERID

      FROM IDS_TXNDEMDATAMAP M

      inner join IDS_DEMDATA D
      on M.DEMDATAID = D.DEMDATAID

      inner join IDS_TXN T
      on M.TXNID = T.TXNID

      inner join IDS_TXNIDNUMBERS N
      on M.TXNID = N.TXNID and UPPER(N.IDCATEGORY) = 'RECORDQUEUEID'

      LEFT JOIN
      ( SELECT DEMDATAID ,
           MAX(DECODE(IDCATEGORY,'DLID',IDNUMBER,NULL)) DLID ,
           MAX(DECODE(IDCATEGORY,'CustomerID',IDNUMBER,NULL)) CUSTOMERID
      FROM IDS_IDNUMBERS
      GROUP BY DEMDATAID
      ) O
      ON M.DEMDATAID=O.DEMDATAID;

      Is there any way to modify this SQL to sort of modify the LEFT OUTER JOIN and GROUP BY clause.

      The data rarely changes, so is there a way to use some sort of materialized view? Let me know.

      Thanks
      Amitava.
        • 1. Re: Need to change existing SQL of a view
          sb92075
          amitavachatterjee1975 wrote:
          Hi,

          My current view SQL contains a LEFT OUTER JOIN and a GROUP BY clause as a result optimizer is not picking up the correct indexes for the tables.
          why do you think you know better than the CBO?

          Are statistics current for all involved tables & indexes?
          • 2. Re: Need to change existing SQL of a view
            SomeoneElse
            You can't alter a view to change the SQL statement.

            You must drop and create it or use create or replace.
            • 3. Re: Need to change existing SQL of a view
              Frank Kulash
              Hi, Amitava,
              amitavachatterjee1975 wrote:
              Is there any way to modify this SQL to sort of modify the LEFT OUTER JOIN and GROUP BY clause.
              I don't see any obvious inefficiencies or mistakes in the code.
              For all tuning questions, see the forum FAQ {message:id=9360003}
              The data rarely changes, so is there a way to use some sort of materialized view? Let me know.
              Yes, a materialized view might make it faster. What happens when you try it?
              • 4. Re: Need to change existing SQL of a view
                amitavachatterjee1975
                Yes, sometimes the LEFT OUTER JOIN section is doing "TABLE ACCESS FULL" for IDS_IDNUMBERS table. So in essence I wish I can replace the portion

                "...
                LEFT JOIN
                ( SELECT DEMDATAID ,
                     MAX(DECODE(IDCATEGORY,'DLID',IDNUMBER,NULL)) DLID ,
                     MAX(DECODE(IDCATEGORY,'CustomerID',IDNUMBER,NULL)) CUSTOMERID
                FROM IDS_IDNUMBERS
                GROUP BY DEMDATAID
                ) O
                ON M.DEMDATAID=O.DEMDATAID;
                ..."

                with something like self-join without a GROUP BY clause. Cause of the GROUP BY clause without a WHERE clause, the CBO is sometimes doing "TABLE ACCESS FULL" for IDS_IDNUMBERS table.

                Also how can I create a materialized view for the LEFT JOIN portion and use it here.

                Edited by: amitavachatterjee1975 on Nov 14, 2012 11:48 AM
                • 5. Re: Need to change existing SQL of a view
                  odie_63
                  My current view SQL contains a LEFT OUTER JOIN and a GROUP BY clause as a result optimizer is not picking up the correct indexes for the tables.
                  Post the explain plan and database version.
                  See Frank's reply for the link to the related FAQ thread.

                  On Oracle 11, a Join Predicate Pushdown (JPPD) with a GROUP BY in the view is possible, so the CBO might decide to perform it - depending on the cost of the transformation of course.
                  The explain plan, and ultimately a CBO trace should tell you why it happens or not.

                  Some further readings :
                  https://blogs.oracle.com/optimizer/entry/basics_of_join_predicate_pushdown_in_oracle
                  http://dioncho.wordpress.com/2009/04/12/join-predicate-pushing-and-group-by/