Too many rows reported for index in tkprof

Hi all.

        I'm fazed by a query I've been asked to optimize.

        By creating an index, I've been able to lower EXPLAIN's cost
estimate from nearly 5k to 36.  Problem is, this translated in an answer
time reduced from 2min 30s to 2min 20s -- too small a reduction.

        So i did autotrace:

    1271  recursive calls
   29005  db block gets
37156454  consistent gets
 1371862  physical reads


        Looking for a why, I did a tkprof:

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   NESTED LOOPS
   7249    NESTED LOOPS
   7249     NESTED LOOPS
   7342      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                 'PS_PYMNT_VCHR_XREF'
   7342       INDEX (RANGE SCAN) OF 'PSXPYMNT_VCHR_XREF' (NON-UNIQUE)
  14589      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                 'PS_VOUCHER'
  14682       INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'PS_VOUCHER'
                  (UNIQUE)
  14496     TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'PS_VENDOR'
      0    TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
               'PS_BOLETO_BRL'
17845208     INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'PS0BOLETO_BRL'
                (NON-UNIQUE)


        Oops!  Problem is, ps0boleto_brl is an index with less than 100k
rows.

        So what's wrong here?  How can such a small index give such a high
row number in tkprof?

        Ah, this is PeopleSoft 10 on Oracle 8.1.7.

        Thanks in advance!


--
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.
--
http://www.freelists.org/webpage/oracle-l

Other related posts: