Solved

Oracle SQL Multiple Unions

Posted on 2014-03-07
8
410 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
data lookup in Oracle - need suggestions 55 113
Queries 15 39
Advice on Xojo as a development tool over VB. 4 55
'G_F01' is not a procedure or is undefined 3 25
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

777 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