Re: Indexing problem?
Date: 26 Apr 2003 16:58:01 -0700
Message-ID: <e47c36e4.0304261558.66ce6104_at_posting.google.com>
Frank <fvanbortel_at_netscape.net> wrote in message news:<3E9AFEDC.8040303_at_netscape.net>...
> John wrote:
> > 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
>
> Yes. It will not affect what Oracle produces, only how (and
> how efficient), though. I fail to see what that would have
> to do with your problem.
> Are you sure your dates/times are correct?
Thanks everyone for your responses. We resolved this problem. As it turns out, the culprit was a PeopleSoft page object we created to resolve another problem. Thanks again for taking the time to help!
John Received on Sun Apr 27 2003 - 01:58:01 CEST