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
Solved

Oracle SQL Multiple Unions

Posted on 2014-03-07
8
417 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
pl/sql - query very slow 26 71
Selenium docs api java index 3 48
Using SQL*PLUS issue with where statement with trunc function 3 27
Help with Oracle IF statment 5 25
Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
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 video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
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…

861 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