11 Replies Latest reply: Jul 12, 2012 11:57 PM by 948948 RSS

    Excluding slow table access in a UNION ALL view

    948948
      Hi,

      I have a view which unions three tables together.

      One component of the view requires a table scan, as 90% of the records are required.

      This view is then used in another outer select where these records are actually not required.

      So I tagged each component with a code and excluded that in the outer select. However it still appears to access the table.


      Is there any way I can exclude a component of the UNION ALL or do I need to explicitly split them?


      example:


      SELECT * FROM (
      SELECT 'A' Q, JANUARY F FROM ENORMOUS.TABLE WHERE KEY = 'Non Selective Key' UNION ALL
      SELECT 'B' Q, 1 F FROM DUAL UNION ALL
      SELECT 'C' Q, 1 F FROM DUAL
      ) A
      WHERE Q = 'B'

      When I run the query plan without the WHERE it performs the table scan

      When I include the WHERE it still performs the table scan but with a FILTER NULL IS NOT NULL afterwards.


      So it appears that it is doing the table scan regardless and then throwing the records away - is that correct?


      Any thoughts appreciated. I would prefer not the split this view out if possible as it is used everywhere.
        • 2. Re: Excluding slow table access in a UNION ALL view
          948948
          In summary my question is: is The Oracle query planner smart enough to exclude a component from a load of stacked UNION ALL queries?


          given this query:
          CREATE VIEW TEST AS
          SELECT Q, F 
          FROM 
          (
          SELECT 'A' Q, JANUARY F FROM ENORMOUS.TABLE WHERE KEY = 'Non Selective Key' UNION ALL
          SELECT 'B' Q, 1 F FROM DUAL UNION ALL
          SELECT 'C' Q, 1 F FROM DUAL
          ) A;
          
          -- 1. This one selects from all tables, including a table scan on the enormous table
          SELECT * FROM TEST;
          
          -- 2. This one selects from all tables, including a table scan on the enormous table
          -- However the query plan has a FILTER after the table scan. Does it exclude this work?
          SELECT * FROM TEST WHERE Q = 'B';
          When I run the query plan without the WHERE it performs the table scan

          When I include the WHERE the query plan indicates it's doing the table scan but with a FILTER NULL IS NOT NULL afterwards.

          So it appears that it is doing the table scan regardless and then throwing the records away - is that correct?

          Any thoughts appreciated. I would prefer not the split this view out if possible as it is used everywhere.


          Oracle version:

          Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
          PL/SQL Release 11.2.0.2.0 - Production
          CORE     11.2.0.2.0     Production
          TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
          NLSRTL Version 11.2.0.2.0 - Production


          Plan output for 1:

          SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=7166 Card=2 M Bytes=41 M)
          1 VIEW (Cost=7166 Card=2 M Bytes=41 M)
          2 1 UNION-ALL
          3 2 TABLE ACCESS FULL PLANNING.BF_GEN_STATS_TRAN (Cost=7162 Card=2 M Bytes=72 M)
          4 2 FAST DUAL (Cost=2 Card=1)
          5 2 FAST DUAL (Cost=2 Card=1)


          Plan output for 2:

          SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=2 Card=3 Bytes=48)
          1 VIEW (Cost=2 Card=3 Bytes=48)
          2 1 UNION-ALL
          3 2 FILTER
          4 3 TABLE ACCESS FULL PLANNING.BF_GEN_STATS_TRAN (Cost=7162 Card=2 M Bytes=72 M)
          5 2 FAST DUAL (Cost=2 Card=1)
          6 2 FILTER
          7 6 FAST DUAL (Cost=2 Card=1)
          • 3. Re: Excluding slow table access in a UNION ALL view
            rp0428
            >
            I would prefer not the split this view out if possible as it is used everywhere.
            >
            Not sure what you meant by that since that would be the easiest thing to do. Haven't tested this but it should give the idea.
            CREATE VIEW TEST_SMALL AS
            SELECT Q, F 
            FROM (
            SELECT 'B' Q, 1 F FROM DUAL UNION ALL
            SELECT 'C' Q, 1 F FROM DUAL
            );
            
            CREATE VIEW TEST AS 
            SELECT Q, F 
            FROM 
            (
            SELECT 'A' Q, JANUARY F FROM ENORMOUS.TABLE WHERE KEY = 'Non Selective Key' UNION ALL
            SELECT Q, F FROM TEST_SMALL
            );
            The TEST view hasn't changed at all so you can continue to use it everywhere. But it is built from a view that excludes the 'enormous' table and you can use that new view for your outer join that doesn't need the big table.
            • 4. Re: Excluding slow table access in a UNION ALL view
              indra budiantho
              I would prefer not the split this view out if possible as it is used everywhere
              It is like create 'Theory of Everything'. The best way to improve performance is to tailor your view per query basis. There are some conditions where index is suppressed and optimizer choose FTS: NULL values, using function or expression, and etc. However, you can still force Optimizer to use index, for example, by using hint.
              • 5. Re: Excluding slow table access in a UNION ALL view
                948948
                Well the question remains: is The Oracle query planner smart enough to exclude a component from a load of stacked UNION ALL queries?

                Further to this: when I see a whole chain of the query plan leading into a filter that says NULL IS NOT NULL (i.e. clearly false) then will it exclude the work under that branch, even though it appears in thew query plan.... so will it 'short circuit' that piece of work.

                It appears from my experiments that it does. It would be nice to have confirmation.
                • 6. Re: Excluding slow table access in a UNION ALL view
                  rp0428
                  >
                  Well the question remains: is The Oracle query planner smart enough to exclude a component from a load of stacked UNION ALL queries?

                  Further to this: when I see a whole chain of the query plan leading into a filter that says NULL IS NOT NULL (i.e. clearly false) then will it exclude the work under that branch, even though it appears in thew query plan.... so will it 'short circuit' that piece of work.
                  >
                  What you have shown in the previous output (see the cost and cardinality) is similar to what Oracle does with 'partition views'.

                  That is how Oracle's partitioning got its start - way back in version 7. They were called partition views. They still exist but aren't supported anymore (now that partitioning is available) and could be removed at any time.

                  Use them at your own risk. In particular see the rules and guidelines in the doc

                  See Managing Partition Views in the Oracle7 Tuning, release 7.3.3 doc
                  http://docs.oracle.com/cd/A57673_01/DOC/server/doc/A48506/partview.htm

                  And here is a link to a forum thread where Oracle ACE Billy Verreynne provides example code for a partition view using 5 tables and shows how the constraint is used to satisfy the requirements allowing Oracle to 'prune away' the 4 tables that aren't needed for the query.
                  Table name in from clause
                  • 7. Re: Excluding slow table access in a UNION ALL view
                    indra budiantho
                    I am not sure about its behavior as in a simple expression like:
                    if false and true... as further as i know.. 'it can be either short circuit or can be not short circuit'.

                    And for more complex query, it is more unlikely.
                    • 8. Re: Excluding slow table access in a UNION ALL view
                      948948
                      I found some information about the NULL IS NOT NULL in the query plan

                      at the bottom of this page
                      http://www.hellodba.com/reader.php?ID=15&lang=EN

                      and halfway down this page
                      http://optimizermagic.blogspot.com.au/2008/06/why-are-some-of-tables-in-my-query.html


                      According to these, it will not execute the part of tree under this filter, which is my objective.

                      Edited by: Nick.McDermaid on 12/07/2012 20:52
                      • 9. Re: Excluding slow table access in a UNION ALL view
                        indra budiantho
                        Okay, it is the optimizer's behavior. However, there could be other cost, like parsing time, etc. So, instead of let the optimizer to optimize the query (another cost), why not you make a good query from the start? (it is performance by design).
                        • 10. Re: Excluding slow table access in a UNION ALL view
                          rp0428
                          >
                          I found some information about the NULL IS NOT NULL in the query plan
                          >
                          Maria Colgan is a member of Oracle's Optimizer development team and her blog has numerouse articles providing insight and examples of optimizer functionality and how to take advantage of it.

                          You also should have noticed that construct in the plans provided by Billy in the forum link I provided.

                          One caveat: if you rely on constructs like this you need take extra measures to know EXACTLY what parameters are set, AND NEED TO BE SET, in the environment you are using them in. When a plan based on a construct like this changes the performance results can be disastrous because entire tables and full table scans can be included that weren't previously.
                          • 11. Re: Excluding slow table access in a UNION ALL view
                            948948
                            Okay, it is the optimizer's behavior. However, there could be other cost, like parsing time, etc. So, instead of let the optimizer to optimize the query (another cost), why not you make a good query from the start? (it is performance by design).

                            Because this does not happen in the real world. I'm working on a decision support system that's over ten years old. I have eight years of other peoples development to try not to break, and I have very little room to maneuver without causing unexpected impacts on the business rules in the system.

                            'Design' takes into account factors like simplicity and maintainability.


                            rp0428, your comments have been most helpful, so that's now three articles saying the same thing about NULL IS NOT NULL so I can be reasonably sure its the truth


                            I'm not actually using an Oracle partitioned view - I haven't created any check constraints, the components are view from different tables etc.