8 Replies Latest reply on Mar 20, 2017 4:48 PM by rp0428

    Performance impact (benefit/degrade) by using inline views

    user10566312

      Hi,

       

      I have a SQL where it is filtering tables using inline views and then joining with other tables. I am supposed to tune it. I was thinking directly joining and using where clause would be better rather than WITH clause inline views.

      I am using Oracle 11g.

       

      SQL with inline views:

      WITH t1 as

      (select ... from a1... where ...)

      , t2 as

      (select ... from a2... where ...)

      ,t3 as

      (select ... from a3... where ...)

      ,t4 as

      (select ... from a4 inner join t3 on (...) left outer join t2 (...) left outer join t2 (...)

      where a4...);

       

      SQL without inline views:

      select ..

      from a4 inner join a3 on (a4...= a3...)

      left outer join a2 on (a3... = a2... and a2...=)

      left outer join a1 on (a3... = a1... and a1...=)

      where a4...=

      and a3...=;

       

      Appreciate anyone's expert opinion on this.

       

      Pls note that even though the SQL uses WITH clause, the entire SQL is not using subquery factoring, i.e., the inline views are used only once in the SQL.

        • 1. Re: Performance impact (benefit/degrade) by using inline views
          John Stegeman

          Have you looked at the query execution plan?

          • 2. Re: Performance impact (benefit/degrade) by using inline views
            BluShadow

            user10566312 wrote:

             

            Hi,

             

            I have a SQL where it is filtering tables using inline views and then joining with other tables. I am supposed to tune it. I was thinking directly joining and using where clause would be better rather than WITH clause inline views.

             

             

             

            What makes you think that?

             

            The query optimizer is very clever.  It knows best how to combine the subqueries (not "inline") you specify, whether those are best considered in the WITH clause or as part of the table joins.  Often, you will find the execution plan is the same, unless the subquery is used multiple times, in which case it may be evaluated separately... but the optimizer will determine the best path based on all the information it has about all the tables and what you're trying to do.

            • 3. Re: Performance impact (benefit/degrade) by using inline views
              Andrew Sayer

              user10566312 wrote:

               

              Hi,

               

              I have a SQL where it is filtering tables using inline views and then joining with other tables. I am supposed to tune it. I was thinking directly joining and using where clause would be better rather than WITH clause inline views.

              I am using Oracle 11g.

               

              SQL with inline views:

              WITH t1 as

              (select ... from a1... where ...)

              , t2 as

              (select ... from a2... where ...)

              ,t3 as

              (select ... from a3... where ...)

              ,t4 as

              (select ... from a4 inner join t3 on (...) left outer join t2 (...) left outer join t2 (...)

              where a4...);

               

              SQL without inline views:

              select ..

              from a4 inner join a3 on (a4...= a3...)

              left outer join a2 on (a3... = a2... and a2...=)

              left outer join a1 on (a3... = a1... and a1...=)

              where a4...=

              and a3...=;

               

              Appreciate anyone's expert opinion on this.

               

              Pls note that even though the SQL uses WITH clause, the entire SQL is not using subquery factoring, i.e., the inline views are used only once in the SQL.

              My opinion is that I'd rather not GUESS.

               

              Look at the execution plan, does it look optimal to you? Can you identify where the time is going?-Not unless you know your data.

              I find asking Uncle Oracle to gather row source statistics and tell me them in the plan SO MUCH EASIER. Follow the instructions in https://blogs.oracle.com/optimizer/entry/how_do_i_know_if  to see what I mean.

               

              Once you can see where the time is going, THEN you can decide how to address a problem (if one exists).

              • 4. Re: Performance impact (benefit/degrade) by using inline views
                mathguy

                A quick scan of the Oracle documentation will show that the optimizer can treat the WITH clause subqueries either as inline views or as temporary tables. It will evaluate both options and choose the one that produces the lowest "cost". If each factored subquery (which IS the correct term for what you find in the WITH clause, whether it ends up being treated as an inline view or a temporary table) is used only once in the overall query, it is likely that they will simply be inlined, and the execution plan will be the same as inlining the views yourself.

                 

                With that said, the pseudo-code you wrote points to a potential pitfall. In an OUTER join, the result of the query may be different if you move conditions from WHERE clauses to the ON clauses instead. There has been extensive discussion of this issue on this forum just recently. If you are tempted to do what you presented (even against the advice received here), make sure you do it correctly. Also, make sure you do it correctly if you want to compare execution plans to convince yourself that the advice received here is sound!

                • 5. Re: Performance impact (benefit/degrade) by using inline views
                  Andrew Sayer

                  mathguy wrote:

                   

                   

                  With that said, the pseudo-code you wrote points to a potential pitfall. In an OUTER join, the result of the query may be different if you move conditions from WHERE clauses to the ON clauses instead. There has been extensive discussion of this issue on this forum just recently. If you are tempted to do what you presented (even against the advice received here), make sure you do it correctly. Also, make sure you do it correctly if you want to compare execution plans to convince yourself that the advice received here is sound!

                  I don't see what you mean, the columns being filtered on in the where clause are from a4 (which is being "from"ed) and a3 which is being inner joined - there would be no difference.

                  • 6. Re: Performance impact (benefit/degrade) by using inline views
                    mathguy

                    Yes, you are right - I didn't pay close attention to the rewritten query; the outermost WHERE condition from the original query was left in a WHERE clause in the outermost query in the rewrite also, it wasn't moved to an ON condition.

                     

                    My general point, though, was that whenever we make such changes, we must be careful how we do it so the resulting query is logically identical to the original. It seems the OP was, indeed, careful!

                    • 7. Re: Performance impact (benefit/degrade) by using inline views
                      Chris Hunt

                      It's quite likely that the optimiser will choose the same plan for both aproaches, assuming they are equivalent, so I would pick the option that is clearer for human maintainers of the code to understand. For me, that usually means your second approach (without WITH clauses), but YMMV.

                      • 8. Re: Performance impact (benefit/degrade) by using inline views
                        rp0428

                        I have a SQL where it is filtering tables using inline views and then joining with other tables. I am supposed to tune it.

                        Why?

                         

                        What evidence do you have that it needs 'tuned'? You haven't posted ANY.

                         

                        The first step you should take whenever ANYONE reports a problem is to confirm that the problem actually exists.

                         

                        Performance is all about COMPARISON: compare AFTER with BEFORE.

                         

                        Which should tell you that you need to have the BEFORE info in order to be able to know if what you did is better, worse or the the same.

                         

                        Once you have the BEFORE info you then need to compare it to the SLA (service level agreement) for that task/query. If it meets the SLA there is NOTHING to do no matter how bad someone thinks the performance is.

                         

                        You also have to have a GOAL. Is you supposed to increase performance by 10%? 30% What?

                         

                        A goal of 'make it faster' is useless and not really a proper goal.

                         

                        1. Post the info that indicates that a performance issue exists.

                        2. Post the current performance data

                        3. Post the request performance that 'someone' ask you to achieve.