关于在使用rowid和rownum时遇到ORA-01446错误的原因即解决方法。

关于在使用rowid和rownum时遇到ORA-01446错误的原因即解决方法。

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 to
           underlying physical records, no ROWIDs can be returned.
Action:    Remove ROWID from the view selection clause, then re-execute the statement.

翻译中文就是：
ORA-01446: 无法从DISTINCT、GROUP BY等子句的视图中选择ROWID。
原因：     一个查询命令试图从一个含有根据函数或者表达式衍生出的列的视图中查询ROWIDS。
           因为从视图中查询出来的行不能和实际的物理记录对应，所以不能返回ROWIDS。
解决方法： 从试图的查询字符中移除ROWID，然后重新执行命令。

我们看下面这个例子：

语句1：select * from (select t.*, rowid from dual t);

语句2：select t.*, rowid, rownum from dual t;

语句3：select * from (select t.*, rowid, rownum from dual t);

语句4：select dummy from (select t.*, rowid, rownum from dual t);

语句5：select * from (select t.*, rowid ri, rownum from dual t);


语句1执行成功。因为查询实际上是走全表扫描。
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'


语句2执行成功。原因同语句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'


语句3执行失败。因为涉及到了伪劣rownum的使用。
rowid和rownum虽然都是伪列，但它们是不同的：rowid 可以说是物理存在的，表示记录在表空间中的唯一位置ID，在DB中唯一。只要记录没被搬动过，rowid是不变的。rowid 相对于表来说又像表中的一般列；而rownum记录的是oracle数据库从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则rownum值为1，第二条为2，依次类推。
语句3要从子查询中查询rownum列，相当于把子查询的结果集当作一个视图，由于还需要从视图中取得rowid，所以会报ORA-01446错误。
SQL> select * from (select t.*, rowid, rownum from dual t);
select * from (select t.*, rowid, rownum from dual t)
       *
ERROR 位于第 1 行:
ORA-01446: cannot select ROWID from view with DISTINCT, GROUP BY, etc.


语句4执行成功。因为最后筛选的记录集中只含有dummy，没有包含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'


语句5执行成功。因为子查询中把rowid对应的列使用了别名ri，所以对视图的查询就没有包含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'




如果不明白语句3的可以看看这里。
select * from (select dummy,rowid from dual)
等同于
select dummy,rowid from (select dummy,rowid from dual);
内外的rowid是相同的。

select * from (select dummy,rownum from dual)
等同于
select dummy,rownum from (select dummy,rownum from dual);
内外的rownum不同，子查询会当作视图。

select * from (select dummy,rowid,rownum from dual)
等同于
select dummy,rowid,rownum from (select dummy,rowid,rownum from dual);
由于内外rownum不相同，子查询会当作视图，所以报错。



本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/heyixiang/archive/2007/12/29/2003391.aspx