Solved

Oracle SQL Multiple Unions

Posted on 2014-03-07
8
422 Views
Last Modified: 2014-03-21
I have a large query that looks something like this:

Select
     columns
 FROM(
                    --Dataset 1
                   SELECT
                       Columns FROM TableName
                  UNION
                 --Dataset 2
                   SELECT
                       Columns FROM TableName
                 UNION
                 --Dataset 3
                   SELECT
                       Columns FROM TableName
             ) AS ALIAS#1
                  (
                    --Dataset 4
                    SELECT
                       Columns FROM TableName
                  UNION
                   --Dataset 5
                   SELECT
                       Columns FROM TableName
                  UNION
                  --Dataset 6
                    SELECT
                       Columns FROM TableName
                  )AS ALIAS#2
WHERE
CONDITION #1
CONDITION #2
ETC..

The question is, what is the order of operations within each block that deliver an alias

There are 3 select statements in each, how are they run and then union compared?

Thanks in advance,
0
Comment
Question by:DB-aha
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 500 total points
ID: 39912831
One way to determine what's happening under to covers (in general, not specifically for this query) is to do an EXPLAIN PLAN for your query  and use DBMS_XPLAN to format the results.

Docs: http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_xplan.htm#ARPLS378

Natively that doesn't give you the execution order.

However, there's a utility at this site: http://www.oracle-developer.net/utilities.php (it's the top one) that expands upon the native functionality and shows you the order the optimizer decided to execute the different parts of the query.

DBMS_XPLAN and the Utility are talked about in this article: http://www.oracle-base.com/articles/9i/dbms_xplan.php
0
 
LVL 1

Author Comment

by:DB-aha
ID: 39912860
My issue though is that I do not have access to the actual data, I am just looking at the code to see where data is being changed, updated, manipulated etc. I cannot run code even in read only access so deciphering this is not possible tools. Thanks
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 39916903
so your basically driving a car with a blindfold ?
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 500 total points
ID: 39917736
As mentioned above, you're really shooting in the dark here.  Looking at code and guessing what the optimizer is doing is only going to get you so far.

I took a table out of a database I had and worked up the following query:

Record counts in this table are as follows:

YEAR   COUNT(*)
---- ----------
2008     813751
2009     898061
2010     890261
2011     911726
2012     921699
2013     938490
2014      49955

Open in new window


So, I unioned 2008/2009/2010 at the start, then unioned 2011/2012/2013, and then unioned the two sets together.

explain plan for
select * from 
(
 select * from
  (select * from table1 where column1 = 'VALUE1' and column2 like '2008%'
   union
   select * from table1 where column1 = 'VALUE1' and column2 like '2009%'
   union
   select * from table1 where column1 = 'VALUE1' and column2 like '2010%'
  ) a
 UNION
 select * from
  (select * from table1 where column1 = 'VALUE1' and column2 like '2011%'
   union
   select * from table1 where column1 = 'VALUE1' and column2 like '2012%'
   union
   select * from table1 where column1 = 'VALUE1' and column2 like '2013%'
  ) b
) c
where status = '0'
/

Open in new window


Then I ran the modified display of the plan and got the following

PLAN_TABLE_OUTPUT
------------------------------
Plan hash value: 2010317257

------------------------------------------------------------------------------------------------------------
| Id  | Pid | Ord | Operation                         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 |     |  22 | SELECT STATEMENT                  |            |     6 | 40086 |   904  (96)| 00:00:11 |
|   1 |   0 |  21 |  VIEW                             |            |     6 | 40086 |   904  (96)| 00:00:11 |
|   2 |   1 |  20 |   SORT UNIQUE                     |            |     6 | 40086 |   904  (96)| 00:00:11 |
|   3 |   2 |  19 |    UNION-ALL                      |            |       |       |            |          |
|   4 |   3 |   9 |     VIEW                          |            |     3 | 20043 |   451  (96)| 00:00:06 |
|   5 |   4 |   8 |      SORT UNIQUE                  |            |     3 |  5055 |   451  (96)| 00:00:06 |
|   6 |   5 |   7 |       UNION-ALL                   |            |       |       |            |          |
|*  7 |   6 |   2 |        TABLE ACCESS BY INDEX ROWID| TABLE1     |     1 |  1685 |   149  (96)| 00:00:02 |
|*  8 |   7 |   1 |         INDEX SKIP SCAN           | TABLE101I1 |     1 |       |   148  (96)| 00:00:02 |
|*  9 |   6 |   4 |        TABLE ACCESS BY INDEX ROWID| TABLE1     |     1 |  1685 |   149  (96)| 00:00:02 |
|* 10 |   9 |   3 |         INDEX SKIP SCAN           | TABLE101I1 |     1 |       |   148  (96)| 00:00:02 |
|* 11 |   6 |   6 |        TABLE ACCESS BY INDEX ROWID| TABLE1     |     1 |  1685 |   149  (96)| 00:00:02 |
|* 12 |  11 |   5 |         INDEX SKIP SCAN           | TABLE101I1 |     1 |       |   148  (96)| 00:00:02 |
|  13 |   3 |  18 |     VIEW                          |            |     3 | 20043 |   451  (96)| 00:00:06 |
|  14 |  13 |  17 |      SORT UNIQUE                  |            |     3 |  5055 |   451  (96)| 00:00:06 |
|  15 |  14 |  16 |       UNION-ALL                   |            |       |       |            |          |
|* 16 |  15 |  11 |        TABLE ACCESS BY INDEX ROWID| TABLE1     |     1 |  1685 |   149  (96)| 00:00:02 |
|* 17 |  16 |  10 |         INDEX SKIP SCAN           | TABLE101I1 |     1 |       |   148  (96)| 00:00:02 |
|* 18 |  15 |  13 |        TABLE ACCESS BY INDEX ROWID| TABLE1     |     1 |  1685 |   149  (96)| 00:00:02 |
|* 19 |  18 |  12 |         INDEX SKIP SCAN           | TABLE101I1 |     1 |       |   148  (96)| 00:00:02 |
|* 20 |  15 |  15 |        TABLE ACCESS BY INDEX ROWID| TABLE1     |     1 |  1685 |   149  (96)| 00:00:02 |
|* 21 |  20 |  14 |         INDEX SKIP SCAN           | TABLE101I1 |     1 |       |   148  (96)| 00:00:02 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - filter("COLUMN2" LIKE '2008%')
   8 - access("COLUMN1"='VALUE1' AND "TABLE1"."STATUS"='0')
       filter("TABLE1"."STATUS"='0')
   9 - filter("COLUMN2" LIKE '2009%')
  10 - access("COLUMN1"='VALUE1' AND "TABLE1"."STATUS"='0')
       filter("TABLE1"."STATUS"='0')
  11 - filter("COLUMN2" LIKE '2010%')
  12 - access("COLUMN1"='VALUE1' AND "TABLE1"."STATUS"='0')
       filter("TABLE1"."STATUS"='0')
  16 - filter("COLUMN2" LIKE '2011%')
  17 - access("COLUMN1"='VALUE1' AND "TABLE1"."STATUS"='0')
       filter("TABLE1"."STATUS"='0')
  18 - filter("COLUMN2" LIKE '2012%')
  19 - access("COLUMN1"='VALUE1' AND "TABLE1"."STATUS"='0')
       filter("TABLE1"."STATUS"='0')
  20 - filter("COLUMN2" LIKE '2013%')
  21 - access("COLUMN1"='VALUE1' AND "TABLE1"."STATUS"='0')
       filter("TABLE1"."STATUS"='0')

About
------
  - XPlan v1.2 by Adrian Billington (http://www.oracle-developer.net)

Open in new window


From this, you can see that it executed the 2008 query, then the 2009 and then the 2010, unioned them all together and sorted them to remove duplicates (I used UNION and not UNION all, so no duplicates are allowed).
Then it did 2011, 2012, 2013, unioned and sorted for duplicates.
Finally, unioned the two result sets together, sorted for duplicates and returned the result set.

In this instance, it executes pretty much as it reads.  

I don't have multiple tables with similar structures so for this example, I used the same table multiple times.

However, as mentioned above, if you don't have access to the database to be able to extract information like this, desk checking code isn't going to tell you too much.
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 39917878
> Steve Wales
your sample shows index skip scan.
usually, this is for me an indication of a problem

it's using an index with more fields than fields required to do the search
eg: search criteria contains 2 fields, while index contains 5 fields
creating a new index with only the required fields gives better performance
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39917906
It's a third party, pre-packaged application, I have no flexibility in modification to the tables in it - and the query I used is highly unlikely to be a life-like query - it's the largest table in the database and was something I could quickly use on the test box as an answer to the original topic with a demo explain plan.

Real world queries would use the full primary key almost all the time :)  (but we're getting away from the original question ... thanks for your concern though!)
0
 
LVL 1

Author Comment

by:DB-aha
ID: 39927233
I am still unclear. The reason I asked this question was because I thought there was a fundamental order that is applied w hen you deal with say 3 unions as it exists in part of the code I provided.  I guess the irst question should have been, In the first part of the code, is there a tried and true order on how that runs?
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 39927241
Execution of a query is based entirely upon the optimizer and how it thinks it is best to handle the execution based on what it knows about the tables (based upon statistics).

For something like multiple unions though, I don't see why any special processing is needed, it did not surprise me (in the example I ran) to find that the optimizer had chosen to fetch the data in the order that the unions appeared in the select statement.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question