norowsselected
--无记录返回
Execution Plan
----------------------------------------------------------
Plan hash value: 399371572
------------------------------------------------------------------------------------
| Id | Operation | Name| Rows| Bytes | Cost (%CPU)| Time
|
------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 4262 | 76716 | 27 (71)| 00:00:01 |
| 1 | MINUS | | | | | |
| 2 | SORT UNIQUE| | 4262 | 38358 | 9 (12)| 00:00:01 |
| 3 | TABLEACCESS FULL| COL_USAGE$ | 4262 | 38358 | 8 (0)| 00:00:01 |
| 4 | SORT UNIQUENOSORT| | 4262 | 38358 | 18 (6)| 00:00:01 |
|* 5 | INDEXFULLSCAN | I_COL_USAGE$ | 4262 | 38358 | 17 (0)| 00:00:01 |
------------------------------------------------------------------------------------
--验证表两个sql是否正确(一个全表扫描，另个index 快速扫描)
SQL> SELECT/*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL#
2 FROMsys.COL_USAGE$ t whereOBJ# isnotnullorINTCOL# isnotnull
3 MINUS
4 SELECT/*+ FULL(t1) */ OBJ#,INTCOL#
5 FROMsys.COL_USAGE$ t1;
OBJ# INTCOL#
---------- ----------
4294951004 2
4294951004 3
4294951004 4
4294951004 26
4294951004 27
4294951037 4
4294951037 5
4294951037 6
4294951037 9
4294951037 10
4294951840 11
OBJ# INTCOL#
---------- ----------
4294951840 12
4294951906 4
4294952709 3
4294952867 4
4294952867 9
16 rowsselected.
--证明index中的记录比表中多了16条
?
解决问题并验证
SQL> alterindexsys.I_COL_USAGE$ rebuild online;
Indexaltered.
SQL> SELECT/*+ FULL(t1) */ OBJ#,INTCOL#
FROMsys.COL_USAGE$ t1
2 3 MINUS
4 SELECT/*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL#
5 FROMsys.COL_USAGE$ t whereOBJ# isnotnullorINTCOL# isnotnull
6 ;
norowsselected
SQL> SELECT/*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL#
2 FROMsys.COL_USAGE$ t whereOBJ# isnotnullorINTCOL# isnotnull
3 MINUS
4 SELECT/*+ FULL(t1) */ OBJ#,INTCOL#
5 FROMsys.COL_USAGE$ t1;
norowsselected
这次出现此问题的原因是因为在更新语句中使用索引找到一条记录，然后到表中去查询时该记录不存在，出现此错误，一般解决方法是重建索引