Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle SQL Multiple Unions

Posted on 2014-03-07
8
Medium Priority
?
466 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 23

Assisted Solution

by:Steve Wales
Steve Wales earned 2000 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 38

Expert Comment

by:Geert Gruwez
ID: 39916903
so your basically driving a car with a blindfold ?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 23

Assisted Solution

by:Steve Wales
Steve Wales earned 2000 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 38

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 23

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 23

Accepted Solution

by:
Steve Wales earned 2000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
Introduction to Processes
Starting up a Project

609 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