Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Too many rows reported for index in tkprof

RE: Too many rows reported for index in tkprof

From: David Kurtz <>
Date: Wed, 10 Aug 2005 15:47:49 +0100
Message-ID: <>

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?


David Kurtz
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
Book: PeopleSoft for the Oracle DBA: Next Go-Faster Seminar: PeopleSoft for the DBA, London UK, October PeopleSoft DBA Forum:

> -----Original Message-----
> From:
> []On Behalf Of Leandro Guimaraes
> Faria C. Dutra
> Sent: 10 August 2005 13:44
> To:
> Cc:;
> Subject: RE: Too many rows reported for index in tkprof
> 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:
> 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
> sysadm.PS_PYMNT_VCHR_XREF p,
> sysadm.PS_VOUCHER v,
> sysadm.PS_VENDOR vn,
> sysadm.PS_BOLETO_BRL b
> p.BOL_SEQ_NUM_BRL = ' '
> AND b.tdb_associado_brl = 'N'
> 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_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
> 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.
> --

Received on Wed Aug 10 2005 - 09:49:56 CDT

Original text of this message