Solved

Oracle SQL Multiple Unions

Posted on 2014-03-07
8
396 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
  • 4
  • 2
  • 2
8 Comments
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 500 total points
Comment Utility
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
Comment Utility
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 36

Expert Comment

by:Geert Gruwez
Comment Utility
so your basically driving a car with a blindfold ?
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 500 total points
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
> 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This article will show, step by step, how to integrate R code into a R Sweave document
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now