Thanks for this. It is isn't from the PeopleSoft Query tool. It might be SQR, but it is more likely to be from a different external system that is referencing PeopleSoft, because the tables are qualified with 'sysadm.'. There are at least two significant problems here. i) There is a missing join. If you join 'vn.vendor_id = v.vendor_id', then you should also join 'vn.setid = v.vendor_setid'. This is a common error that shows a lack of understanding for the data model. I would also suggest changing the 'unique key' index on PS_VENDOR from CREATE UNIQUE INDEX PS_VENDOR ON PS_VENDOR (SETID, VENDOR_ID) ... to CREATE UNIQUE INDEX PS_VENDOR ON PS_VENDOR (VENDOR_ID, SETID) ... So if someone forgets to specify SETID you can still search by VENDOR_ID. From PT8.x you can set the 'custom key order' attribute on the index in Application Designed and change the order there. ii) If you have an index on PS_BOLETO_BRL.DOCUMENT_NUMB_BRL, then the function on the condition will disable it. Instead of AND SUBSTR (v.invoice_id, 1, 6) = SUBSTR (b.document_numb_brl, 1, 6) you might try AND b.document_numb_brl LIKE SUBSTR (v.invoice_id, 1, 6)||'%' Or you might need to use a function-based index. There is something else odd here that I missed the first time round - there is an index PSXPYMNT_VCHR_XREF. The third character, in this case 'X', is what PeopleSoft calls the index ID. When users specified indexes are created in App Designer each one is give a single character index ID. App Designer allocated the first unused letter for each index. X is the 24th letter of the alphabet. Are there really 24 user indexes? There are only 4 specified in the vanilla application. What has been going on? regards _________________________ David Kurtz Go-Faster Consultancy Ltd. tel: +44 (0)7771 760660 fax: +44 (0)7092 348865 web: www.go-faster.co.uk mailto:david.kurtz@xxxxxxxxxxxxxxx Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com Next Go-Faster Seminar: PeopleSoft for the DBA, London UK, October PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx > [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Leandro Guimaraes > Faria C. Dutra > Sent: 10 August 2005 13:44 > To: info2@xxxxxxxxxxxxxxx > Cc: oracle-l@xxxxxxxxxxxxx; oracle-l-bounce@xxxxxxxxxxxxx > Subject: RE: Too many rows reported for index in tkprof > > > > oracle-l-bounce@xxxxxxxxxxxxx gravou em 2005-08-09 19:17:16: > > > It would be nice to see the query which generated this execution plan. > > I had refrained to do so because I wasn't concerned with the > overall query but by the strange number, and others have already pointed > me to the nested loop as a culprit. But since you asked, be prepared, it > is long: > > SELECT > p.business_unit, > v.vendor_id, > p.voucher_id, > p.scheduled_pay_dt, > p.pymnt_gross_amt, > vn.name1, > p.pymnt_modality_brl, > p.pymnt_method, > v.invoice_id, > b.document_numb_brl, > p.bank_cd, > p.bank_acct_key, > b.bol_seq_num_brl, > b.gross_amt, > 0, > 0, > p.pymnt_gross_amt - (b.gross_amt - 0 + 0), > p.pymnt_cnt > FROM > sysadm.PS_PYMNT_VCHR_XREF p, > sysadm.PS_VOUCHER v, > sysadm.PS_VENDOR vn, > sysadm.PS_BOLETO_BRL b > WHERE > p.BOL_SEQ_NUM_BRL = ' ' > AND p.PYMNT_SELCT_STATUS = 'N' > AND p.PYMNT_METHOD = 'EFT' > AND p.PYMNT_TYPE <> 'W' > AND p.PYMNT_ACTION <> 'P' > AND p.PYMNT_GROSS_AMT > 0 > AND v.ENTRY_STATUS <> 'X' > AND v.IN_PROCESS_FLG = 'N' > AND b.tdb_associado_brl = 'N' > AND v.BUSINESS_UNIT = p.BUSINESS_UNIT > AND v.VOUCHER_ID = p.VOUCHER_ID > AND vn.vendor_id = v.vendor_id > AND v.business_unit = b.business_unit > AND p.remit_vendor = b.vendor_id > AND SUBSTR (v.invoice_id, 1, 6) > = SUBSTR (b.document_numb_brl, 1, 6) > ; > > > > It is obviously from PeopleSoft Financials because of tables > > PS_PYMNT_VCHR_XREF and PS_VENDOR > > PS_BOLETO_BRL does not appear in the vanilla application, so this is a > > customisation. > > Yep. > > > > The full scan on VENDOR is often caused by the user forgetting to join > the > > table on SETID (which is the first column in the unique index). In many > > companies there is only a single SETID, or a single SETID per > legislature. > > Hence users often forget to add the join in PS/Query. So this is > probably > > an ad-hoc on-line query in a customised part of the application. > > Dead right. > > > > The other point to make is that if you are to successfully administer a > > PeopleSoft system, then you need to get a little familiarity with the > > PeopleSoft development tools. You don't need to be able to develop > > application code, but you do need to be able to find out how things are > > defined or coded and then get a developer to change them. > > Good advice, thanks. If only I had not some dozens of Oracle, MS > SQL Server, Sybase, Informix and DB2 instances to manage... and far worse > apps than PeopleSoft (made for example by recent Clipper programmers), I'd > certainly follow it. For now I'm left to trying to understand the > database at hand. > > Investment in knowledge is always good advice, too bad employers > like to understaff and overextend. > > > -- > Leandro Guimarães Faria Corcete DUTRA > Administrador de Bases de Dados +55 (11) 4390 5383 > Toyota do Brasil Ltda ldutra@xxxxxxxxxxxxx > São Bernardo do Campo, SP BRASIL > > > > This message (including any attachments) is confidential and may > be privileged and intended solely for the use of the > person/entity to whom it is addressed. If you have received it by > mistake please notify the sender by returning via e-mail as well > as delete this message from your system. Any unauthorized use or > dissemination of this message in whole or in part is prohibited. > Please note that e-mails are susceptible to change. TOYOTA DO > BRASIL LTDA (including its group companies) shall not be liable > for the improper or incomplete transmission of the information > contained in this communication, neither for personal, > nonbusiness related information nor opinion sent through this > email or even for any delay in its receipt or damage to your > system. TOYOTA DO BRASIL LTDA (or its group companies) does not > guarantee that the integrity of this communication has been kept > nor that this communication is free of viruses, interceptions or > interference. > -- > //www.freelists.org/webpage/oracle-l > -- //www.freelists.org/webpage/oracle-l