I’m querying data for my api to support pagination like below:
Select * from ext_gen_data where order by submitted_dt_tm where ext_data_id>125 and ROWNUM <=5 ;
But rownum does return as expected due to its known behaviour. To solve this problem and as per recommendation, I have modified query as below:
Select * from (Select * from ext_gen_data where order by submitted_dt_tm where ext_data_id>125) where ROWNUM <=5 ;
For more information, my inner query has column of Blob data type.
My question is what will be performance difference with and without outer query?
Can we have join with inner query, will it impact performance of a query?
Will the inner query fetch all the data which qualifies the criteria and apply filter, what exactly happens on back screen?
Any kind of guideline would be much appreciated.
Thanks,
Shahenaz
what will be performance difference with and without outer query?It's kind of an irrelevant question. The first approach gives the wrong answer, so there's no point comparing performance to this.
But in general if you write:
select * from (
select * from ...
) where ...
If you can rewrite the query (and get the same answer) as
select * from ... where ...
The database will.
When you have:
select * from (
select t.*, rownum from ...
order by ...
) where ...
The database has to run the inner query first to assign row numbers according to the order by. So in this case it has to do the inner filtering and sorting before the outer.
But in general subqueries
don't mean "do this first".
See also:
https://asktom.oracle.com/pls/apex/asktom.search?tag=sub-queries-and-joins-performance https://asktom.oracle.com/pls/apex/asktom.search?tag=subquery-performance Can we have join with inner query, will it impact performance of a query?Yes, you can join inside the subquery. Or outside it. And yes it will impact performance. How exactly depends on what you're doing. It might make it faster. It might make it slower.
But I refer to my previous statement. If you need to join to get the correct answer, then comparing to not joining is irrelevant.
Will the inner query fetch all the data which qualifies the criteria and apply filter, what exactly happens on back screen?What exactly happens on screen depends on how your application handles the data!