Jeleeb, the short answer is that without the scalar subquery the function will run for every row. If it is in the "select" portion it will run for every row returned. If it is in the "where" portion it will run for every row evaluated. That means a SQL to PL/SQL context switch for every row. That is very expensive.

When you use a scalar subquery, it will cache the result of the function (up to a limit depending on your db version). If the same bind variable is used, it won't rerun the function, it will just take the value from the cache. It is significantly faster because it avoids the context switch AND it doesn't have to run the function.

In the case of (select my_function(empno) from dual) it won't actually help because the value of empno will be different for every row. Hence, it will need to run the query every time. So, there is no benefit. But, it doesn't really hurt either. So, if you are just in the habit of ALWAYS doing it, then you can decide to not do it when you are sure it won't help.

Regarding point 8, it's incredibly complicated to come up with rules as to when one way or another is faster. It's easier to have a representative data set and just test it out. I suspect with 23c the database may automatically rewrite the query based upon the data profile to get the best performance. We'll see if that happens, but for now, I suggest following point 1, and then using the method that gives you the best (fastest) result.

Here are some examples that should help with point 7 and 8.

Example 1: my_pkg.my_function executes for every row due to no scalar subquery:

select e.empno, my_pkg.my_function(p_param => 'Y') as results from emp e;

Example 2: my_pkg.my_function called from a scalar subquery caches the result of the function; does not execute on every row:

select e.empno, (select my_pkg.my_function(p_param => 'Y') from dual) as results from emp e;

Example 3: There isn't really a benefit in this instance because empno is different for every row, however it's still a good habit:

select e.empno, (select my_pkg.my_function2(p_param => e.empno) from dual) as results from emp e;

Hope that helps! :)