The cause of a ORA-01446 error when using rowid and rownum is the solution.

The cause of a ORA-01446 error when using rowid and rownum is the solution.

ORA-01446: cannot select rowid from view with distinct, group by, etc.
Cause: a SELECT statement attempted to select rowids from a view
Containing columns derived from functions or expressions.
Because the rows selected in the view do not correspond
Underlying physical records, no rowids can be returned.
Action: Remove rowid from the view selection clause, then re-execute the statement.

Chinese Translation:
ORA-01446: rowid cannot be selected from the View of clauses such as distinct and group.
Cause: a query command attempts to query rowids from a view containing columns derived from a function or expression.
Because the rows queried from the view cannot correspond to the actual physical records, rowids cannot be returned.
Solution: remove the rowid from the query characters and run the command again.

 

Let's look at the example below:

Statement 1: Select * from (select T. *, rowid from dual t );

Statement 2: Select T. *, rowid, rownum from dual T;

Statement 3: Select * from (select T. *, rowid, rownum from dual t );

Statement 4: Select dummy from (select T. *, rowid, rownum from dual t );

Statement 5: Select * from (select T. *, rowid Ri, rownum from dual t );

Statement 1 is successfully executed.Because the query is actually a full table scan.
SQL> select * from (select T. *, rowid from dual t );

D rowid
-------------------
X aaaadeaabaaaayiaaa

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose
1 0 Table Access (full) of 'dual'

Statement 2 is successfully executed.The reason is the same as Statement 1.
SQL> select T. *, rowid, rownum from dual T;

D rowid rownum
-----------------------------
X aaaadeaabaaaayiaaa 1

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose
1 0 count
2 1 Table Access (full) of 'dual'

Statement 3 execution failed.It involves the use of fake rownum.
Although rowid and rownum are both pseudo columns, they are different: rowid can be physical, indicating the unique location ID of the record in the tablespace, which is unique in the DB. As long as the record has not been moved, the rowid remains unchanged. Compared with a table, rowid is similar to a common column in a table. rownum records the sequence in which the Oracle database reads data from data files or buffers. If it obtains the first record, the rownum value is 1, the second record is 2, and so on.
Statement 3 to query the rownum column from the subquery, it is equivalent to the result set of the subquery as a view, because you also need to get rowid from the view, so the ORA-01446 error is reported.
SQL> select * from (select T. *, rowid, rownum from dual t );
Select * from (select T. *, rowid, rownum from dual T)
*
Error is located in row 1st:
ORA-01446: cannot select rowid from view with distinct, group by, etc.

Statement 4 is successfully executed.Because the last filtered record set only contains dummy and does not contain rowid.
SQL> select dummy from (select T. *, rowid, rownum from dual t );

D
-
X

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose
1 0 View
2 1 count
3 2 Table Access (full) of 'dual'

Statement 5 is successfully executed.Because the column corresponding to the rowid in the subquery uses the alias Ri, the query for the view does not contain the rowid.
SQL> select * from (select T. *, rowid Ri, rownum from dual t );

D Ri rownum
-----------------------------
X aaaadeaabaaaayiaaa 1

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose
1 0 View
2 1 count
3 2 Table Access (full) of 'dual'

If you do not understand Statement 3, you can look at it here.
Select * from (select dummy, rowid from dual)
Equivalent
Select dummy, rowid from (select dummy, rowid from dual );
The internal and external rowids are the same.

Select * from (select dummy, rownum from dual)
Equivalent
Select dummy, rownum from (select dummy, rownum from dual );
The rownum inside and outside is different, and the subquery is treated as a view.

Select * from (select dummy, rowid, rownum from dual)
Equivalent
Select dummy, rowid, rownum from (select dummy, rowid, rownum from dual );
Because the internal and external rownum are different, subqueries are treated as views, so an error is returned.