If there is a situation where the data is not being found using certain WHERE clauses (for example WHERE id = ?), but could be found in some other ways using some other columns (for example WHERE first_name = ? AND last_name = ?), this could be an issue with index corruption.
Root cause: There was a row (or a set of rows) in the table without a corresponding index entry. This could be due to oracle bug or lost write condition (h/w & OS involved). If it happens more than once, Oracle Support should be contacted - there are tons of bug related to this in MOS.
Some real life example below which I encountered recently -
++ Try to select the record
I knew there is a row with ID = 372876, but was not able to find that using ID in WHERE clause.
SQL> SET AUTOTRACE ON
SQL> SELECT rowid, id FROM client t1 WHERE id = 372876;
no rows selected
Execution Plan
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 2 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| PK_CLIENT_OVERSIGHT | 1 | 17 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
++ Force full table scan, avoiding the index scan
SQL> SELECT /*+ FULL(t1) */ rowid, id
FROM client t1 WHERE id = 372876;
ROWID ID
------------------ ----------
AAAU3uAABAAKSexAAD 372876
Execution Plan
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 384 (6)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| CLIENT | 1 | 17 | 384 (6)| 00:00:01 |
--------------------------------------------------------------------------------------
++ Now do a corruption check including indexes in the table
Solution:
++ Drop index or affected indexes, then recreate. DO NOT rebuild. Dropping and recreating index forces Oracle to recreate the index from the table data.
++ Hold on, there might be cases that, after dropping and recreating index did not resolve the issue, there might be more erros like below when you do DML on the table -
ORA-08102: index key not found, obj# 128201, file 39, block 627105 (2)
In that case, just recreate the table using CREATE TABLE .. AS .. or expdp/impdp or online redefinition.
Root cause: There was a row (or a set of rows) in the table without a corresponding index entry. This could be due to oracle bug or lost write condition (h/w & OS involved). If it happens more than once, Oracle Support should be contacted - there are tons of bug related to this in MOS.
Some real life example below which I encountered recently -
++ Try to select the record
I knew there is a row with ID = 372876, but was not able to find that using ID in WHERE clause.
SQL> SET AUTOTRACE ON
SQL> SELECT rowid, id FROM client t1 WHERE id = 372876;
no rows selected
Execution Plan
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 2 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| PK_CLIENT_OVERSIGHT | 1 | 17 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
++ Force full table scan, avoiding the index scan
SQL> SELECT /*+ FULL(t1) */ rowid, id
FROM client t1 WHERE id = 372876;
ROWID ID
------------------ ----------
AAAU3uAABAAKSexAAD 372876
Execution Plan
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 384 (6)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| CLIENT | 1 | 17 | 384 (6)| 00:00:01 |
--------------------------------------------------------------------------------------
++ Now do a corruption check including indexes in the table
SQL> ANALYZE TABLE PROD.CLIENT VALIDATE STRUCTURE CASCADE;
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file
++ Look at the trace file to get more info
trace file: row not found in index tsn: 15 rdba: 0x09cad60a
++ Use SQL to find the object/segment/indexes
SELECT owner, segment_name, segment_type, partition_name
FROM DBA_SEGMENTS
WHERE header_file = (SELECT file#
FROM v$datafile
WHERE rfile# = dbms_utility.data_block_address_file(to_number('&rdba','XXXXXXXX'))
AND ts#= &tsn)
AND header_block = dbms_utility.data_block_address_block(to_number('&rdba','XXXXXXXX'));
Here, rdba=09cad60a (excluding first 2 digits) and tsn= 15
++ Drop index or affected indexes, then recreate. DO NOT rebuild. Dropping and recreating index forces Oracle to recreate the index from the table data.
++ Hold on, there might be cases that, after dropping and recreating index did not resolve the issue, there might be more erros like below when you do DML on the table -
ORA-08102: index key not found, obj# 128201, file 39, block 627105 (2)
In that case, just recreate the table using CREATE TABLE .. AS .. or expdp/impdp or online redefinition.
***
