Optimizing a union sql query

Question

I am doing a UNION between 3 tables. To put you in the picture, one table is considered as the main table and the 2 other tables are considered as sub tables. The 2 sub tables have always the same number of records for each case. So I wanted to make a union between these 3 tables, in this union I'd like to repeat the information in the column fetched in the main table and list the information the 2 sub tables where every line in sub table 1 correspond to the line in sub table 2. I cannot put the real code because of professional reasons so here is a simple example corresponding to my real case. Consider a table called Author (Author_ID, Author_FirstName, Author_LastName). This table will be our main table. Then consider we have a table called Adresses (Adress_ID, Street_Coord, Author_ID), this will be our sub table 1. Then consider the table called Cities (City_ID, City_Name, Author_ID). Our author X has 2 adresses in 2 cities. When I execute my query I get the result R1 which is totally logical, but I want to modify my query to get the result R2. Could you please help me to change my query to get the result R2?

SQL Query:

SELECT "Author"."Author_ID", "Author"."Author_FirstName", "Author.Author_LastName",
       TO_CHAR(NULL) AS "Street_Coord", TO_CHAR(NULL) AS "City_Name"
FROM "Author"
WHERE "Author"."Author_ID"='X'

UNION

SELECT TO_NUMBER(NULL) AS "Author_ID", TO_CHAR(NULL) AS "Author_FirstName", TO_CHAR(NULL) AS "Author_LastName", 
       "Adresses"."Street_Coord", TO_CHAR(NULL) AS "City_Name" 
FROM "Adresses"
WHERE "Adresses"."Author_ID"='X'

UNION

SELECT TO_NUMBER(NULL) AS "Author_ID", TO_CHAR(NULL) AS "Author_FirstName", TO_CHAR(NULL) AS "Author_LastName", 
       TO_CHAR(NULL) AS "Street_Coord", "Cities"."City_Name"
FROM "Cities"
WHERE "Cities"."Author_ID"='X'

Result R1:

ID_AUTHOR | AUTHOR_FirstName | AUTHOR_LastName | Street_Coord   | City_Name       |
----------------------------------------------------------------------------------
X         |James             | Conor           | NULL           | NULL            |
----------------------------------------------------------------------------------
X         |NULL              | NULL            | 1245 rich st   | NULL            |
----------------------------------------------------------------------------------
X         |NULL              | NULL            | 154 music st   | NULL            |
----------------------------------------------------------------------------------
X         |NULL              | NULL            | NULL           | Madrid          |
----------------------------------------------------------------------------------
X         |NULL              | NULL            | NULL           | Barcelona       |
----------------------------------------------------------------------------------

Result R2: I want you to help get this result please:

ID_AUTHOR | AUTHOR_FirstName | AUTHOR_LastName | Street_Coord   | City_Name       |
----------------------------------------------------------------------------------
X         |James             | Conor           | 1245 rich st   | Madrid          |
----------------------------------------------------------------------------------
X         |James             | Conor           | 154 music st   | Barcelona       |
----------------------------------------------------------------------------------

Many thanks, Walloud

Hey eaolson, a join of 11 tables crashed ! ORA-01652: unable to extend temp segment by 128 in tablespace TEMP 01652. 00000 - "unable to extend temp segment by %s in tablespace %s" *Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated. *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated. — Walloud, May 23 '13 at 17:42
That error sounds like you need to extend the size of your TEMP tablespace. http://stackoverflow.com/questions/11839576/ora-01652-unable-to-extend-temp-segment-by-in-tablespace — eaolson, May 23 '13 at 23:15

score 0 · Answer 1 · answered May 23 '13 at 00:35

0

THe first thought is that you really want to join the tables together. Something like:

SELECT "Author"."Author_ID", "Author"."Author_FirstName", "Author.Author_LastName",
       "Adresses"."Street_Coord", "Cities"."City_Name"
FROM "Author" join
     "Adresses"
     on Author.Author_id = Adresses.Author_id join
     Cities
     on Author.Author_id = Cities.Author_id 
WHERE "Author"."Author_ID"='X';

However, this would return a cartesian product of the values, which is 4 rows rather than 2 rows. It seems that the purpose is to "align" the different tables. In that case, this query comes close to what you want:

SELECT "Author"."Author_ID", "Author"."Author_FirstName", "Author.Author_LastName",
       "Adresses"."Street_Coord", "Cities"."City_Name"
FROM "Author" join
     (select a.*, rownum as seqnum from "Adresses" a
     ) Adresses
     on Author.Author_id = Adresses.Author_id join
     (select c.*, rownum as seqnum from Cities c
     ) Cities
     on Author.Author_id = Cities.Author_id and addresses.seqnum = cities.seqnum
WHERE "Author"."Author_ID"='X';

The major issues with this approach is that SQL tables are inherently unordered. You need some column to order these by, in order for the query to be guaranteed to work. For instance, if you have an auto-incrementing id in the table, then each subquery could by order by id.

answered May 23 '13 at 00:35

Gordon Linoff

1,242,037
58
646
786

I suspect your first answer is what the Walloud *intends*, but then Cities needs an address_id rather than an author_id. – eaolson May 23 '13 at 00:46
@eaolson . . . The OP is pretty clear on what the tables contain. The structure is definitely unusual. I'm guessing that it is something like lines on a form with different columns. – Gordon Linoff May 23 '13 at 01:35
Hey Guys, in real world I have 11 tables ! When I used JOIN on 11 tables Oracle crashed because of the big number of returned records, that's why I'm using UNION. I have to find a solution only with UNION. Thanks again ! – Walloud May 23 '13 at 16:09
That doesn't make sense. They're two different operations. A JOIN will basically give you additional columns, a UNION will give you more rows. It's like you're asking how to turn the car but you cant use the steering wheel, so you want to know how to do it with only the gas pedal. – eaolson May 23 '13 at 23:16
@Walloud . . . My guess is that Oracle crashed because the joins were not set up correctly. Handling 11 tables should be no problem for Oracle. – Gordon Linoff May 24 '13 at 10:30

score 0 · Answer 2 · answered May 23 '13 at 00:36

0

You don't want to do an UNION, you want to do a JOIN :

SELECT "Author"."Author_ID", "Author"."Author_FirstName", "Author.Author_LastName", "Adresses"."Street_Coord", "Cities"."City_Name"
 FROM "Author"
 INNER JOIN "Adresses" ON "Author"."Author_ID"="Adresses"."Author_ID"
 INNER JOIN "Cities" ON "Author"."Author_ID"="Cities"."Author_ID"
 WHERE "Author"."Author_ID"='X'

Didn't test it, it might contains some typos...

Also, you will need to have an extra join column between "Adresses" and "Cities" to match "1245 rich st" with "Madrid" and "154 music st" and "Barcelona". Something like "City_ID". Then you add it in the INNER JOIN ... ON clause :

INNER JOIN "Cities" ON "Adresses"."City_ID"="Cities"."City_ID" AND "Author"."Author_ID"="Cities"."Author_ID"

answered May 23 '13 at 00:36

Eric Citaire

4,355
1
29
49

Hey Guys, in real world I have 11 tables ! When I used JOIN on 11 tables Oracle crashed because of the big number of returned records, that's why I'm using UNION. I have to find a solution only with UNION. Thanks again ! – Walloud May 23 '13 at 16:09
Well, you should probably execute multiple queries then. In your example, you could execute a query for Author informations like first and last names, and a separate query for adresses and cities. It makes more sense and you will fetch less data than with a single big fat query. – Eric Citaire May 24 '13 at 09:29
hey Walloud , the guys here are right , you should use the JOIN statement instead of UNION statement. if you're getting the ORA-01652 problem , try to "debug" this out , first try to join 2 tables and each time try to add another table to your query and try to understand which tables causes the query to throw an exception (ORA-01652) , after you understood it , try to optimize it by adding indexes (on the author_id in the target table for example) – planben Jul 18 '13 at 05:20

Optimizing a union sql query

2 Answers2