Difference of performance between subquery and where filter

Database
Highlighted
Enthusiast

Difference of performance between subquery and where filter

Hello,

 

I would like to know which option is better in performance between making a subquery and a where to filter in a case like this:

 

SELECT  ID,  NAME, FUNCTION(NAME) AS NAME_TREATED
FROM  TABLE
WHERE ID < 100;
  

or

SELECT  ID,  NAME, FUNCTION(NAME) AS NAME_TREATED
FROM  (SELECT * FROM TABLE WHERE WHERE ID < 100);

Technically, the "WHERE" filter is read after the "FROM", right? The idea would be not to treat with the PL-SQL function "FUNCTION" the cases with ID >= 100 because they won't be returned and I'm wondering what way is better for the case exposed then.

 

Thanks and regards.

Juan

Accepted Solutions
Junior Contributor

Re: Difference of performance between subquery and where filter

It will be the same in both cases, as long as the FUNCTION is only used in the Select list, it will be applied on the result rows only.

1 ACCEPTED SOLUTION
3 REPLIES
Junior Contributor

Re: Difference of performance between subquery and where filter

Logically the WHERE is processed after FROM, but the projection in the SELECT list is processed after WHERE/GROUP BY/HAVING/QUALIFY, just before ORDER.

 

Simply check Explain and you will notice that both variations return exactly the same plan.

Enthusiast

Re: Difference of performance between subquery and where filter

But with the second method, would the function "FUNCTION" be applied just on the cases filtered and with the first one over all of the records?

 

Thanks and regards.

Juan
Junior Contributor

Re: Difference of performance between subquery and where filter

It will be the same in both cases, as long as the FUNCTION is only used in the Select list, it will be applied on the result rows only.