tkprof output - Cause unknown ?

Kelly / List

Your Comments on the following tkprof output please ? Especially on the Large Value of query=94322530 & rows=88940824

NOTE - IDX_INTER_BR_ORIG_TRAN_TABLE Index fields = (orig_extn_cntr_code, orig_br_code, orig_bank_code, orig_date,tran_cat_code, schm_code, iba_num, reversal_ind, srl_num, crncy_code)

Qs. Does the index Need to be Chenged o Allow ALL fields of the where Clause Below to Run on the index ?

tkprof OUTPUT :-

> select count(*) ,TO_CHAR(sum(DECODE(DR_CR_IND,'D',TRAN_AMT,'C',(TRAN_AMT*
> (-1))))) into :b0,:b1
> from
> IOT
> where ((((((ORIG_EXTN_CNTR_CODE=:b2
> and ORIG_BR_CODE=:b3)
> and ORIG_BANK_CODE=:b4)
> and ORIG_DATE=TO_DATE(:b5,'DD-MM-YYYY HH24:MI:SS'))
> and IBA_NUM=:b6)
> and REVERSAL_IND<>'AD')
> and MATCHED_FLG='N')
>
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0 0
> 0
> Execute 2234 0.29 0.23 0 0 0
> 2233
> Fetch 2234 1727.27 1739.28 0 94322530 0
> 2229
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 4469 1727.56 1739.51 0 94322530 0
> 4462
>
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
> Parsing user id: 10 (TBAADM)
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 2249 SORT (AGGREGATE)
> 88940824 TABLE ACCESS (BY ROWID) OF 'INTER_BR_ORIG_TRAN_TABLE'
> 88943058 INDEX (RANGE SCAN) OF 'IDX_INTER_BR_ORIG_TRAN_TABLE' (UNIQUE)
Received on Sat Aug 05 2000 - 23:09:36 CDT