Thanks for the question, Prasad.
Asked: July 20, 2020 - 3:38 pm UTC
Last updated: July 21, 2020 - 5:45 am UTC
Version: 12c
Viewed 1000+ times
You Asked
I am executing the following query on oracle 12C.
select col1,col2,col3 from TAB_1 where col1=:subId and col5=? and col6=?
Union All
select col1,col2,col3 from TAB_2 where col1=:subId and col5=? and col6=?
order by 2,3
Table TAB_1 contains around 1.5 billion rows and it is partitioned on appropriate columns.
Table TAB_2 contains 2 billion row and it is also partitioned on appropriate columns.
Oracle executes this SQL in few seconds and fetches around 5-10 million rows.
Now the question is,
1) When oracle says sql executed in 10 seconds, does it mean that oracle has found out expected 5-10 million rows, sorted in expected order and prepared it in some data structure to be fetched using result set by end user? If this not the case then how does it work?
2) Where does the oracle keep these fetched results (in memory or on disk)?
and Connor said...
A few things worth considering here
- If we can avoid the sort, then we don't need to *store* any result - we can just read it and send the results back to you. Even with an ORDER BY, sometimes the optimizer can avoid the sort if indexes etc can provide the information in the correct order. Look for "SORT ORDER BY" in your execution plan to see if you're sorting
- If we do need to sort (which is *likely* but not guaranteed in your case), then the results need to be kept somewhere so we can send them back to the calling interface. That will be the PGA (memory), and if you exceed your sessions allowance, it would then be dumped to temporary storage (your TEMP tablespace). We take care of this all automatically.
- We then start giving your rows back as the *client* has asked. For example, in SQL Developer, we give you the first page size of rows (typically 50) and then stop and wait for you to hit page down. This is *not* the total elapsed time for the query, it is the time taken to give you just these first 50 rows. This is why we often get asked "Why is it fast in SQL Dev and slow in my application?" If it was coming back to some application code, it is likely that the app will fetch all of them not just the first 50.
Is this answer out of date? If it is, please let us know via a Comment