Re: Clarification of BSTAT-ESTAT results

From: Jonathan Lewis <Jonathan_at_jlcomp.demon.co.uk>
Date: 1995/12/22
Message-ID: <819664192snz_at_jlcomp.demon.co.uk>#1/1


In article <4beqkb$dv8_at_ds2.acs.ucalgary.ca> you write:

: Rows Execution Plan
: ------- ---------------------------------------------------
: 0 INSERT STATEMENT HINT: RULE
: 133964 SORT (GROUP BY)
: 133964 NESTED LOOPS
: 133966 NESTED LOOPS
: 3055 NESTED LOOPS
: 1495 NESTED LOOPS
: 196 TABLE ACCESS (FULL) OF 'FIN_COMP'
: 1495 TABLE ACCESS (BY ROWID) OF 'FIN_DOWNLOAD_ACCT_CTL'
: 1560 INDEX (RANGE SCAN) OF 'FIN_DOWNLOAD_ACCT_CTL_KEY1' (UNIQUE)
: 3055 TABLE ACCESS (BY ROWID) OF 'WR_VAL_D'
: 4550 INDEX (RANGE SCAN) OF 'WR_VAL_D_KEY1' (NON-UNIQUE)
: 133966 TABLE ACCESS (BY ROWID) OF 'FA_BALANCE'
: 10843370 INDEX (RANGE SCAN) OF 'FA_BALANCE_KEY2' (NON-UNIQUE)
: 133964 TABLE ACCESS (BY ROWID) OF 'FA_HEAD'
: 133966 INDEX (UNIQUE SCAN) OF 'FA_HEAD_KEY1' (UNIQUE)
:

At first sight, and in the absence of details of your tables, it looks to me as if your access path on this query is wrong.

Hitting 10,000,000 index entries and selecting 133,966 rows is not terribly effective.

Have you considered the arithmetic of doing a tablescan off the FA_BALANCE table and seeing how that affects your performance

-- 
Jonathan Lewis
Received on Fri Dec 22 1995 - 00:00:00 CET

Original text of this message