Re: Indexing problem?
From: Frank <fvanbortel_at_netscape.net>
Date: Mon, 14 Apr 2003 20:33:00 +0200
Message-ID: <3E9AFEDC.8040303_at_netscape.net>
>>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
Date: Mon, 14 Apr 2003 20:33:00 +0200
Message-ID: <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?
-- Regards, Frank van BortelReceived on Mon Apr 14 2003 - 20:33:00 CEST