Re: Indexing problem?

postbus_at_sybrandb.demon.nl (Sybrand Bakker) wrote in message news:<a20d28ee.0304140014.3c1c9f40_at_posting.google.com>...
> john.gable_at_cedar.com (John) wrote in message news:<e47c36e4.0304120841.3083d2bf_at_posting.google.com>...
> > Hello,
> >
> > I am a PeopleSoft developer working with a PeopleSoft application on
> > an Oracle 8i 8.1.7.0.0 database and am experiencing a few serious
> > problems that I think may be related. I apologize if this isn't the
> > exact right forum for this question. Because I'm having this problem
> > on a database which is a copy of another database in which I'm not
> > experiencing this problem makes me think that there's something at the
> > db level causing this.
> >
> > There seems to be a date recognition problem. I'll try to explain my 2
> > symptoms in non-PeopleSoft-speak.
> >
> > 1) Through the on-line system, I am able to update the current row
> > (based on the current date) of an effective dated table without being
> > signed in in the required "Correction" mode
> >
> > 2) A PeopleSoft built-in function whose purpose is to return the
> > effective date of the current row in the buffer is instead returning
> > the effective date of the OLDEST row.
> >
> > As I had mentioned, these same functions are working on the database
> > from which the problematic database was copied. Here is what I've done
> > so far in my troubleshooting:
> >
> > * Verified System Date
> > * Dropped indexes and accessed data without them
> > * Verified that the Oracle patch #869177 for descending indexes
> > required for my PeopleSoft upgrade was applied
> >
> > I thought that the problem was related to either indexes or db
> > configuration but dropping the indexes of the table didn't seem to
> > help.
> >
> > Has anyone encountered similar issues or have any thoughts?
> >
> > Thank you!
> >
> > John
>
>
> It very much looks like the Peoplesoft code is implicitly relying on
> the physical order of the rows. Now the physical order has changed the
> code doesn't work anymore. I would consider this as a bug in
> Peoplesoft.
>
> regards
>
> Sybrand Bakker
> Senior Oracle DBA

Thank you for your response Sybrand! Unfortunately, I can't look at the code behind the built-in function that is supposed to retrieve the current row so I can't see what it's doing. What the PeopleSoft application does do prior to trying to retrieve the current row is do a select against the table with an ORDER BY so theoretically the data should be in the same order.

Let me ask you this. In my troubleshooting efforts, I wanted to process the data without indexes on the problematic table. To do that, I executed the "DROP INDEX" command for all indexes for that table. Would that have forced any selects against that table to use a table scan?

Thanks again for your time!

John Received on Mon Apr 14 2003 - 17:49:35 CEST