hi everybody,
I actually start this thread in SQL / PLSQL category but realized that this is not an sql problem, is an index problem so wanted to reopen it here.
you can read it from here :
2354377
We have an index (actually two of them that we saw) that has not got all rows in the table. that seems quite odd to me but when oracle use an index (let's call it INDEX1), query returns no rows but if I add another condition with AND, query use an another index and a row is comming.
this is not returning row:
select * from My_Table Where ID = 100; -- INDEX1 is used.
no result.
but this one:
select * from My_Table Where ID = 100 and sub_id = 0; -- INDEX2 is used
returns one row.
INDEX1 has not got "ID = 100" row (also if i force for a full table scan, row is returning again). after rebuilding index INDEX1 also worked correctly.
so How is that possible ? when i run the query, I didnt got any error. also checked alert.log file and nothing again. Index has corrupted but there is no exception, no error. how can i trust to my query results now ?
What should i do ?