CODEWITHSUNDEEP
oracle-database
Mehdi Souregi
Mehdi Souregi

Reputation: 3265

retrieve results using union all with oracle

I have 3 tables with exact same structure

table1 contains today's data, table2 contains data of this week, table3 contains the rest of the data from the past, and rows are ordered by id

I want to retrieve last 500 rows from these tables depending on some 'where' conditions.

And for performance issues if table1 got 500rows that satisfy those conditions there is no need to look up on the other tables. same thing if we find 500 on table1 and table2 : there is no need to look up on the BIG table table3.

My goal is to not touch the big table table3 (and table2) as best as I can

My question is, if I do UnionAll, and if tables1 already contains the 500 rows that I need, is Oracle going to search on the table3 and table2 and waste some time on it (rows are ordered btw)? Or by doing UnionAll Oracle concats all the tables and then check what satisfies the conditions and then takes the first 500 rows, or there is a another option?

Upvotes: 2

Views: 624

Answers (1)

kfinity
kfinity

Reputation: 9091

Okay, so I tested this a little, and the results surprised me.

select 'test' from all_objects
union all 
select 'TEST' from all_objects
fetch first 500 rows only;

In this case, all 500 results show test. It's only pulling from the first query. This does seem to suggest that your idea would work. (NB: I'm using 12c syntax here, with the fetch first X rows only, but it should be the same with rownum.)

However, if you have an ORDER clause at the same level as the UNION ALL, it defeats the purpose, since Oracle will have to collect the full dataset in order to sort it - it can't just fetch the first 500 rows.

select 'test' from all_objects
union all 
select 'TEST' from all_objects
order by 1 asc
fetch first 500 rows only;

All results are TEST, and the query is slow. So yeah, don't do this. If you need to do ordering for display purposes, I'd nest it either inside or outside this UNION query.

Outside:

select * from
  (select 'test' from all_objects
  union all 
  select 'TEST' from all_objects
  fetch first 500 rows only)
order by 1 asc;

Inside:

select * from (select 'test' from all_objects order by 1 asc)
union all 
select * from (select 'TEST' from all_objects order by 1 asc)
fetch first 500 rows only;

Depending on what kind of sorting you want.

Upvotes: 1

Related Questions