| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Too many rows reported for index in tkprof
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
tel: +44 (0)7771 760660 fax: +44 (0)7092 348865 web: www.go-faster.co.uk
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Leandro Guimaraes
> Faria C. Dutra
> Sent: 10 August 2005 13:44
> To: info2_at_go-faster.co.uk
> Cc: oracle-l_at_freelists.org; oracle-l-bounce_at_freelists.org
> Subject: RE: Too many rows reported for index in tkprof
>
>
>
> oracle-l-bounce_at_freelists.org 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_at_toyota.com.br
> 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.
> --
> http://www.freelists.org/webpage/oracle-l
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Aug 10 2005 - 09:49:56 CDT
 |
 |