Re: STRANAGE SELECT BEHAVIOR

This is happening on a Unix database. Oracle meta-link has a similar issue which they claim was solved in 9.2.0 but apparently not. I have already logged a tar with Oracle.

This is easily simulatable by creating a index with degree 4. Now I had no reason to create one with degree 4 but for some reason some indexes got created in my DB with degree 4 and all those indexes have this problem. It is another mystery on why that happended. As I had said earlier, on re-creating index, this problem goes away - that is now explained as on re-creation the index was created with degree 1.

I will post Oracle's resolution to the group once they determine the cause.

Vladimir, you are absolutely right in this being a potential cause of data corruption due to unpredictable application behavior. I am made to believe by Oracle support that have escalated this issue to their Engineering so I hope to receive a response soon.

"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message news:<am50at$5b0$1_at_babylon.agtel.net>...
> 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 - 20:10:18 CDT