RE: Wrong Results Bug in Oracle 8.1.7.1

That's it. The init.ora parameter, IGNORE_IN_INDEX had been set to TRUE, but the indexes had not been rebuilt  

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_SLAC <mailto:ian_at_SLAC> .Stanford.edu

-----Original Message-----
Sent: Monday, June 10, 2002 9:18 AM
To: Multiple recipients of list ORACLE-L

We had a similar issue, although it always resulted in an ORA-600. It was with descending indexes as defined in the Peopletools repository. We had to set _IGNORE_DESC_IN_INDEX=TRUE (and _DB_FILE_NONCONTIG_MBLOCK_READ_COUNT=1 to comply with the Certification Requirements) and recreate the development databases. For Production, PS Support gave the OK to recreate the indexes without having to redo the upgrade.

HTH
Tony Aponte

-----Original Message-----
Sent: Monday, June 10, 2002 1:48 AM
To: Multiple recipients of list ORACLE-L

Accounts payable. But this is not a Peoplesoft problem, but an Oracle one. I tried the same query against another Peoplesoft instance, and it ran fine. So there's something more than the view involved here. It's in Oracle Support's court now.

I must be living right. It's failing in development and working in production.

Ian

-----Original Message-----
Sent: Sunday, June 09, 2002 7:13 PM
To: Multiple recipients of list ORACLE-L

what modules, if you dont mind me asking, i'm at a site where we're going to implement HR, Financials And EPM soon.

thanks, joe

MacGregor, Ian A. wrote:

>Yep sure is.
>
>Ian
>
>
>-----Original Message-----
>Sent: Sunday, June 09, 2002 5:43 AM
>To: Multiple recipients of list ORACLE-L
>
>
>Ian, is that peopleslop?
>
>joe
>
>
>MacGregor, Ian A. wrote:
>
>>SQL> SELECT VOUCHER_ID FROM PS_VCHR_MM_VW WHERE ROWNUM < 10;
>>
>>VOUCHER_
>>--------
>>00003394
>>00003395
>>00003396
>>00003397
>>00003398
>>00003399
>>00003400
>>00003401
>>00003402
>>
>>------------------------------------------------------------------------
>>set feedback on
>>
>> 1 SELECT VOUCHER_ID FROM PS_VCHR_MM_VW
>> 2* WHERE VOUCHER_ID = '00003394'
>>SQL> /
>>VOUCHER_
>>--------
>>
>>
>>1 row selected.
>>----------------------------------------------------------------------------------
>>Zounds !!! Select dump(voucher_id) shows that Oracle is returning a null here.
>>
>>Here a function is used to force the query to do full tablescans
>>
>>1 SELECT VOUCHER_ID FROM PS_VCHR_MM_VW
>> 2* WHERE CONCAT(VOUCHER_ID,'A') = CONCAT('00003394', 'A')
>>SQL> /
>>
>>VOUCHER_
>>--------
>>00003394
>>-------------------------------------------------------------------------------------
>>I get the expected results if I force full table scans.
>>
>>I looked at the explain plan for the failing query and rebuilt the ps_voucher index. This
>>did not change its erroneous results. I rebuilt the view itself again to no avail. A check on Metalink revealed bug 1852163. Although this bug's conditions were different from the one above, their were some similarities. One workaround for bug 1852163

>>
>>alter session set "_complex_view_merging" = true;
>>
>>I tried this and the original query still gave improper results.
>>-----------------------------------------------------------------------------------------------
>>All queries against the component tables of the view work fine.
>>------------------------------------------------------------------------------------------
>>The view text is
>>
>>CREATE VIEW SYSADM.PS_VCHR_MM_VW
>>AS
>>SELECT DISTINCT A.BUSINESS_UNIT, A.VOUCHER_ID, A.INVOICE_ID,
>>A.INVOICE_DT, A.PROCESS_INSTANCE, A.ENTRY_STATUS, A.POST_STATUS_AP,
>>A.MATCH_ACTION, C.PPV_POST_FLG, C.ERV_POST_FLG, A.ORIGIN FROM
>>SYSADM.PS_VOUCHER A, SYSADM.PS_PO_LINE_MATCHED C WHERE A.BUSINESS_UNIT =
>>C.BUSINESS_UNIT_AP AND A.VOUCHER_ID = C.VOUCHER_ID AND
>>A.MATCH_ACTION IN ('Y', 'E')
>>-------------------------------------------------------------------------------------
>>If I run the select statement outside of the view and tack on the 'voucher_id = ' clause
>>SELECT DISTINCT A.BUSINESS_UNIT, A.VOUCHER_ID, A.INVOICE_ID,
>>A.INVOICE_DT, A.PROCESS_INSTANCE, A.ENTRY_STATUS, A.POST_STATUS_AP,
>>A.MATCH_ACTION, C.PPV_POST_FLG, C.ERV_POST_FLG, A.ORIGIN FROM
>>SYSADM.PS_VOUCHER A, SYSADM.PS_PO_LINE_MATCHED C WHERE A.BUSINESS_UNIT =
>>C.BUSINESS_UNIT_AP AND A.VOUCHER_ID = C.VOUCHER_ID AND
>>A.MATCH_ACTION IN ('Y', 'E')
>>and a.voucher_id = '00003394'
>>/
>>
>>I get the expected results. The query plan matches the one for the failing statement.
>>----------------------------------------------------------------------------------------------
>>If I select more than voucher_id from the view with the 'voucher_id = ' predicate
>>the other fields are projected correctly, but returns voucher_id as null.
>>===========================================================================================
>>
>>Ian MacGregor
>>Stanford Linear Accelerator Center
>>ian_at_slac.stanford.edu
>>
>>
>>
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com <http://www.orafaq.com>  
-- 
Author: Joe Testa 
  INET: jtesta_at_dmc-it.com 

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051 
San Diego, California        -- Public Internet access / Mailing Lists 
-------------------------------------------------------------------- 
To REMOVE yourself from this mailing list, send an E-Mail message 
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in 
the message BODY, include a line containing: UNSUB ORACLE-L 
(or the name of mailing list you want to be removed from).  You may 
also send the HELP command for other information (like subscribing). 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com <http://www.orafaq.com>  
-- 
Author: MacGregor, Ian A. 
  INET: ian_at_SLAC.Stanford.EDU 

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051 
San Diego, California        -- Public Internet access / Mailing Lists 
-------------------------------------------------------------------- 
To REMOVE yourself from this mailing list, send an E-Mail message 
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in 
the message BODY, include a line containing: UNSUB ORACLE-L 
(or the name of mailing list you want to be removed from).  You may 
also send the HELP command for other information (like subscribing). 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).