0

I have a SQL with multiple UNION as follows

   select a,c from table1 ......
   UNION
   select a,c from table2 ......
   UNION
   select a,c from table3 ......
   UNION
   select a,c from table4 ......

Each of the SQL returns different number of rows, varying from 100 to 200,000

What I have seen is that when these SQL are executed separately, they return results in less than a minute.

But when UNION'ed like show above, they take upto few hours.

Is this because Oracle optimizer is trying to analyze ( in common language terms ) too much information and is not able to figure out best execution plan which fits every SQL ?

oradbanj
  • 551
  • 7
  • 23
  • Try adding `distinct` after each `select` – Ori Marko Jul 29 '19 at 16:22
  • Sorting of few hundred thousands of records will not explain the difference *of hours*, so you will need to explore the [execution plan](https://stackoverflow.com/a/34975420/4808122). This is especially relevant if your *tables* are not *tables* but *complex views*. – Marmite Bomber Jul 30 '19 at 04:46

1 Answers1

3

Perhaps not a direct answer, but UNION actually does more than just connect the result sets from the various tables, it also removes duplicates. This can be a costly operation, especially with many large tables. Assuming you don't mind having duplicates, you could use UNION ALL:

SELECT a, c FROM table1 ......
UNION ALL
SELECT a, c FROM table2 ......
UNION ALL
SELECT a, c FROM table3 ......
UNION ALL
SELECT a, c FROM table4 ......

UNION ALL won't remove duplicates, and will just connect the various result sets without any extra operations.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360