Read the documentation on data types (sql reference manual). If you are
using char then everything is blank padded and so you have to compare with
the blanks. You should be using varchar2 which is not blank padded and then
you don't have to pad the queries with blanks.
Jim
"Laxman Sharma" <google4321_at_hotmail.com> wrote in message
news:85a4b40e.0209140810.1460f71c_at_posting.google.com...
> 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 - 12:07:16 CDT
