6
SELECT *
FROM { SELECT * FROM BigMillionTable UNION ALL SELECT * FROM SmallTensTable } 
WHERE (some_condition)

Vs 

SELECT * 
FROM BigMillionTable 
WHERE (some_condition)

UNION ALL

SELECT * 
FROM SmallTensTable
WHERE (some_condition)

My questions:

  1. Does the first query need to put all the rows in the BigMillionTable in the main memory to perform UNION ALL ?
  2. Which query provides better performance ?
Goran Kutlaca
  • 2,014
  • 1
  • 12
  • 18
kaushalpranav
  • 1,725
  • 24
  • 39
  • What happened when you tried? But I would be very surprised if Oracle chose to use different execution plans for those two statements. –  Dec 05 '18 at 10:27
  • 5
    Compare the explain plans. Any difference? – jarlh Dec 05 '18 at 10:27
  • 2
    The first query has much better performance because it fails immediately due to the curly braces syntax error. After fixing it Oracle will most probably unnest the subquery and you will see the same [execution plan.](https://stackoverflow.com/questions/34975406/how-to-describe-performance-issue-in-relational-database) – Marmite Bomber Dec 05 '18 at 11:33
  • There is a nice article on Sigmod 2018 related to the TeraData on this topic. You should read it: http://web.cs.wpi.edu/~meltabakh/Publications/UnionAll-Demo-SIGMOD-2018.pdf – Radim Bača Dec 05 '18 at 13:29

1 Answers1

0

The where condition is actually executed before the select, so in the 2nd query you are only doing a union all on a smaller set, so that should be faster.

In the 1st query, the inner query will run first, which will do a union all on the whole table and then the where would be executed before selecting the records.

So yes, 2nd would provide better performance compared to the 1st one

Sushant Sharma
  • 86
  • 3
  • 3
    Your advice could match for some RDBMS or even for some antique release of Oracle. Tecent versions can perform *query transformation* to avoid such problems, so you should test it and provide evidence about the difference before posting it. – Marmite Bomber Dec 05 '18 at 11:40
  • Even the query transformation takes that extra bit of time, which the 2nd query will not face – Sushant Sharma Dec 05 '18 at 11:42
  • @SushantSharma . . . I would expect the Oracle optimizer to be smart enough to push the filtering conditions into the `union all` subqueries. That is one thing done by databases with good optimizers. – Gordon Linoff Dec 05 '18 at 12:17
  • That periscopedata link should be banned - it's very misleading – Nick.Mc Dec 05 '18 at 13:23