Analytic functions are processed during the SELECT. This is why you need to use a subquery to filter their output. WHERE is before SELECT, so the values haven't been assigned for any analytic functions yet!
You can see this by looking at the execution plan (remember these start from the leaves at the bottom).
The query below has the ROW_NUMBER() analytic. This is processed at step 2 (WINDOW NOSORT). This is after the GROUP BY operation at 3 and before the ORDER BY at 1:
set serveroutput off
select row_number() over ( order by employee_id, department_id ) rn
from hr.employees e
join hr.departments
using ( department_id )
group by employee_id, department_id
having count(*) = 1
order by rn desc;
select *
from dbms_xplan.display_cursor ( format => 'BASIC' );
--------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
| 2 | WINDOW NOSORT | |
| 3 | FILTER | |
| 4 | SORT GROUP BY | |
| 5 | VIEW | index$_join$_001 |
| 6 | HASH JOIN | |
| 7 | INDEX FAST FULL SCAN| EMP_DEPARTMENT_IX |
| 8 | INDEX FAST FULL SCAN| EMP_EMP_ID_PK |
--------------------------------------------------------
Model expressions have their own clause to process them. Like analytic functions, this comes after GROUP/HAVING and before ORDER BY.
Again, looking at the plan for an example query you can see it's between the GROUP BY at step 4 and ORDER BY at step 1:
select employee_id, department_id
from hr.employees e
join hr.departments
using ( department_id )
group by employee_id, department_id
having count(*) = 1
model
dimension by ( employee_id, department_id )
measures ( 0 total_pay ) (
total_pay[employee_id, department_id] = 1
)
order by employee_id;
select *
from dbms_xplan.display_cursor ( format => 'BASIC' );
--------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
| 2 | SQL MODEL ORDERED | |
| 3 | FILTER | |
| 4 | HASH GROUP BY | |
| 5 | VIEW | index$_join$_001 |
| 6 | HASH JOIN | |
| 7 | INDEX FAST FULL SCAN| EMP_DEPARTMENT_IX |
| 8 | INDEX FAST FULL SCAN| EMP_EMP_ID_PK |
--------------------------------------------------------It's also worth mentioning this is the
logical order of processing. The optimizer has lots of tricks to rewrite SQL to make it more efficient.
For example, the first query above sorts by the assigned row number descending (ORDER BY rn DESC). This is to force the database into an extra sorting step.
If instead we use the default (ascending) sort, the optimizer spots we're already sorting the data this way in ROW_NUMBER. The GROUP BY step 3 has this sorting (SORT GROUP BY). So it no longer needs an extra sort after SELECT and the WINDOW operation is last:
select row_number() over ( order by employee_id, department_id ) rn
from hr.employees e
join hr.departments
using ( department_id )
group by employee_id, department_id
having count(*) = 1
order by rn;
select *
from dbms_xplan.display_cursor ( format => 'BASIC' );
-------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | WINDOW NOSORT | |
| 2 | FILTER | |
| 3 | SORT GROUP BY | |
| 4 | VIEW | index$_join$_001 |
| 5 | HASH JOIN | |
| 6 | INDEX FAST FULL SCAN| EMP_DEPARTMENT_IX |
| 7 | INDEX FAST FULL SCAN| EMP_EMP_ID_PK |
-------------------------------------------------------
So the "rules" above may appear to be broken when you look at the plans for complex queries. In reality the optimizer has transformed your query to make it more efficient.