Strange indeed. Looks like a bug in PQ when combined with IFFS...
It kinda 'forgets' to right-pad the value to the CHAR column width,
which it does with index range scan and even with IFFS when query
runs non-parallel... Of course, I am speculating about true nature of
the issue here. To be 100% sure you probably need to craft a testcase
that always reproduces and open a TAR with Oracle Support so that
they can investigate and confirm if this is a bug with PQ or elsewhere
(or simply open a TAR and provide them with system state dumps
and traces they will likely ask you to create for analysis.) Since the issue
can possibly lead to data corruption, I think they will take it serious...
By the way, which platform is this on - Windows? Unix (which)?
--
Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"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 Mon Sep 16 2002 - 11:19:17 CDT
