一位同事跟我说查询一个sql报错ORA-01446
SQL> select user_id,
2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,
3 dbms_rowid.rowid_block_number(rowid)blockno,
4 dbms_rowid.rowid_row_number(rowid)rowno
5 from user_accesslog
6 where user_id =57633213;
dbms_rowid.rowid_relative_fno(rowid)rel_fno,
*
ERROR at line 2:
ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY,
etc.
[oracle@localhost ~]$ oerr ora 1446
01446, 00000, "cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc."
// *Cause:
// *Action:
+++我检查了一下这个sql,并没有拼写或者格式上的错误,而且同事跟我说这个sql之前能查
+++感觉比较奇怪
+++从错误的字面意思,可以大概看出来,如果视图里面有DISTINCT, GROUP BY之类的操作,是不支持rowid的查询的
检查user_accesslog,发现确实是个视图,而且视图中包含group by操作
group by a.user_id,a.type,a.ip_address;
+++再确认,说这个view最近刚改过,增加了分组统计总数
如果去掉这个分组统计的功能,查询是没有问题的
SQL> select user_id,
2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,
3 dbms_rowid.rowid_block_number(rowid)blockno,
4 dbms_rowid.rowid_row_number(rowid)rowno
5 from user_accesslog
6 where user_id =57633213;
USER_ID REL_FNO BLOCKNO ROWNO
---------- ---------- ---------- ----------
57633213 17 305302 13
+++查询rowid之类的sql,最好能直接去查询物理表,在视图上查询确实有很多局限。
SQL> select user_id,
2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,
3 dbms_rowid.rowid_block_number(rowid)blockno,
4 dbms_rowid.rowid_row_number(rowid)rowno
5 from user_accesslog
6 where user_id =57633213;
dbms_rowid.rowid_relative_fno(rowid)rel_fno,
*
ERROR at line 2:
ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY,
etc.
[oracle@localhost ~]$ oerr ora 1446
01446, 00000, "cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc."
// *Cause:
// *Action:
+++我检查了一下这个sql,并没有拼写或者格式上的错误,而且同事跟我说这个sql之前能查
+++感觉比较奇怪
+++从错误的字面意思,可以大概看出来,如果视图里面有DISTINCT, GROUP BY之类的操作,是不支持rowid的查询的
检查user_accesslog,发现确实是个视图,而且视图中包含group by操作
group by a.user_id,a.type,a.ip_address;
+++再确认,说这个view最近刚改过,增加了分组统计总数
如果去掉这个分组统计的功能,查询是没有问题的
SQL> select user_id,
2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,
3 dbms_rowid.rowid_block_number(rowid)blockno,
4 dbms_rowid.rowid_row_number(rowid)rowno
5 from user_accesslog
6 where user_id =57633213;
USER_ID REL_FNO BLOCKNO ROWNO
---------- ---------- ---------- ----------
57633213 17 305302 13
+++查询rowid之类的sql,最好能直接去查询物理表,在视图上查询确实有很多局限。