STRANAGE SELECT BEHAVIOR

I dont know if this problem existed before but Oracle 9.2.0.1 seems to be behaving very strange.

I have a table 'TABLE1' with six columns

'FIELD1', 'FIELD2', 'FIELD3', 'FIELD4', 'FIELD5', FIELD6' - all defined as CHAR 24 fields.

I have defined two indexes - 1st one on FIELD1 as primary index and second one on 'FIELD2' through 'FIELD5' in that order.

When I query the table with following SQL clauses I notice strange behavior

1. SELECT * FROM TABLE1 WHERE FIELD2= 'X' - No result !! (Table has records)
2. SELECT * FROM TABLE1 WHERE FIELD2= 'X' AND 'FIELD1' = 'Y' - I get back the results correctly !!!!!!
3. SELECT * FROM TABLE1 WHERE FIELD2= 'X ' (right padded) - I get back right result set
4. SELECT * FROM TABLE1 WHERE RTRIM(FIELD2)= 'X' - get back results correctly

If I look into explain plan of query 1 it is using INDEX 2 and whenver it uses INDEX 2 it returns wrong result. Explain plan says it is using INDEX FULL FAST SCAN and parallel query.

Once I drop the index and recreate it, I get back the right results. This time explain plan says it is using INDEX RANGE SCAN

I have noticed this problem on all tables with INDEX DEGREE=4. Once I analyze the table or recreate the index, problem disappears. I have one more table left with the same problem and I am saving it so I that I can provide any data someone needs to crack this problem.

How can a basic select operation not return the right result !!!!

Anyone else seeing this behavior? I know how to get rid of it once I see the problem but how do I know where will I get this problem. It sounds very dangerous behavior as it can result in lot of wrong updates in application programs and that too completely unpredictably. So much for data integrity. Received on Sat Sep 14 2002 - 11:10:08 CDT