5 Replies Latest reply: Nov 21, 2012 2:17 PM by Dom Brooks RSS

    Where case when query not running at all in 11.2.0.3

    CrazyAnie
      Hi,

      I have a query that was running fine till the time we were in 11.1.0.7. Last month, we had a migration to 11.2.0.3, and the query has stopped running since then. It runs indefinitely, and finally I have to kill it. However, until 11.1.0.7, I had absolutely no problems with it. Not sure what changed in 11.2.0.3 that causes it to never give me a result. The query is as below:

      SELECT T1.NAME,T2.AGE,T2.level, T2.AMT,
      FROM table1 T1, table2 T2
      WHERE (CASE WHEN
      (T1.name = 'Peter' AND SUBSTR(T1.desc,1,15)=SUBSTR(T2.description,1,15))
      THEN 1
      ELSE 0
      END)=1;

      Please advise!

      Thanks in advance.
        • 1. Re: Where case when query not running at all in 11.2.0.3
          sb92075
          CrazyAnie wrote:
          Hi,

          I have a query that was running fine till the time we were in 11.1.0.7. Last month, we had a migration to 11.2.0.3, and the query has stopped running since then. It runs indefinitely, and finally I have to kill it. However, until 11.1.0.7, I had absolutely no problems with it. Not sure what changed in 11.2.0.3 that causes it to never give me a result. The query is as below:

          SELECT T1.NAME,T2.AGE,T2.level, T2.AMT,
          FROM table1 T1, table2 T2
          WHERE (CASE WHEN
          (T1.name = 'Peter' AND SUBSTR(T1.desc,1,15)=SUBSTR(T2.description,1,15))
          THEN 1
          ELSE 0
          END)=1;

          Please advise!

          Thanks in advance.
          what does SQL TRACE show that it is doing?


          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: Where case when query not running at all in 11.2.0.3
            Hoek
            Have you compared the execution plans of the query from both database versions ?
            Any non default v$parameter values?
            Can you access Metalink/My Oracle Support?
            • 3. Re: Where case when query not running at all in 11.2.0.3
              Frank Kulash
              Hi,
              CrazyAnie wrote:
              Hi,

              I have a query that was running fine till the time we were in 11.1.0.7. Last month, we had a migration to 11.2.0.3, and the query has stopped running since then. It runs indefinitely, and finally I have to kill it. However, until 11.1.0.7, I had absolutely no problems with it. Not sure what changed in 11.2.0.3 that causes it to never give me a result. The query is as below:

              SELECT T1.NAME,T2.AGE,T2.level, T2.AMT,
              FROM table1 T1, table2 T2
              WHERE (CASE WHEN
              (T1.name = 'Peter' AND SUBSTR(T1.desc,1,15)=SUBSTR(T2.description,1,15))
              THEN 1
              ELSE 0
              END)=1;

              Please advise!
              I'm surprised it runs in any version. You have a comma right before the keyword FROM. If you were not getting a compile-time error, that was a bug in 11.1.0.7 that they fixed by 11.2.0.3.
              It's still strange that the query runs for a long time. I would expect it to give you an error when you tried to compile it. Is there any chance that what you posted is not what you're actually running?

              Also, LEVEL, NAME and DESC are not good column names. How Oracle reacts to using keywords like that as column names can vary from version to version. If a word is in v$reserved_words.keyword, then it's best to avoid using it as a column (or table, or variable, or schema) name.

              Edited by: Frank Kulash on Nov 21, 2012 1:33 PM
              By the way, that WHERE clause is a lot more complicated than it needs to be. Why not use
              WHERE      t1.name = 'Peter' 
              AND      SUBSTR ( t1."DESC"     -- If you must use DESC, double-quote it
                          , 1
                          , 15
                          ) = SUBSTR ( t2.description
                                         , 1
                               , 15
                               )
              ? Always think carefully before using CASE in a WHERE (or HAVING, or START WITH, or CONNECT BY) clause.
              • 4. Re: Where case when query not running at all in 11.2.0.3
                CrazyAnie
                Hi Frank,

                You are right, actually what you are saying makes perfect sense to me, and I had tried it out at my end, and it gave me the results in a sec. If i simply use:

                WHERE      t1.name = 'Peter'
                AND      SUBSTR ( t1."DESC"     -- If you must use DESC, double-quote it
                     , 1
                     , 15
                     ) = SUBSTR ( t2.description
                               , 1
                               , 15
                               )

                I get the results almost instantly. However, as soon as i start using:

                WHERE (CASE WHEN --(TC.edw_feed_desc = 'LD') AND -- ++only checking for the substr part within the case when++
                SUBSTR(T1."DESC",1,15)=SUBSTR(T2.DESCRIPTION,1,15)
                THEN 1 ELSE 0
                END)=1;

                And the query starts to run endlessly. So, there is defintely something goofy going on within the case where substr, and it cant understand what is it. :(

                Thanks so much for your reply!
                Aneesha.
                • 5. Re: Where case when query not running at all in 11.2.0.3
                  Dom Brooks
                  So, there is defintely something goofy going on within the case where substr, and it cant understand what is it.
                  How about starting with some basic information like execution plans for both versions including prdicates?