Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> FTS in SQL Query ... Advice please?
Folks
Qs What Options exist to avoid FTS on GAM in the below SQL Query?
NOTE - GAM Table was RE-Created & RE-Analyzed again about 15 Days After EAB. db_file_multiblock_read_count=16 - should it be reduced to 8?
NOTE - GAM PARTITION BY RANGE ("SOL_ID") - has 13 indexs - Index 1 - Unique index on ACID
EAB PARTITION BY RANGE ("ACID") - has 2 indexes - Index 1 - Unique index
( acid, eod_date ), Index 2 - (end_eod_date,acid,eod_date)
SELECT COUNT(*), NVL(SUM(EAB.TRAN_DATE_BAL),:"SYS_B_0") , GAM.acct_crncy_code
FROM GAM, EAB WHERE GAM.ACID = EAB.ACID AND GAM.del_flg != :"SYS_B_1"
AND GAM.entity_cre_flg = :"SYS_B_2"
AND GAM.acct_prefix = :"SYS_B_3"
AND GAM.acct_num = :"SYS_B_4"
AND GAM.acct_crncy_code = :"SYS_B_5"
AND EAB.eod_date <= TO_DATE(:"SYS_B_6" , :"SYS_B_7" )
AND EAB.end_eod_date >= TO_DATE(:"SYS_B_8" , :"SYS_B_9")
GROUP BY GAM.acct_crncy_code
call count cpu elapsed disk query current rows
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 1 231.70 377.55 3009679 3011675 0 0
total 3 231.71 377.56 3009679 3011675 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 18 (TBAADM)
Rows Row Source Operation
0 SORT GROUP BY NOSORT (cr=3011675 pr=3009679 pw=0 time=377555388 us)
0 TABLE ACCESS BY LOCAL INDEX ROWID EOD_ACCT_BAL_TABLE
(cr=3011675 pr=3009679 pw=0 time=377555361 us)
1 NESTED LOOPS (cr=3011675 pr=3009679 pw=0 time=377555338 us)
0 PARTITION RANGE ALL PARTITION: 1 16 (cr=3011675 pr=3009679 pw=0 time=377555329 us)
0 TABLE ACCESS FULL GENERAL_ACCT_MAST_TABLE PARTITION: 1 16
(cr=3011675 pr=3009679 pw=0 time=377555131 us)
0 PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN IDX_EOD_ACCT_BAL_TABLE PARTITION: KEY KEY
(cr=0 pr=0 pw=0 time=0 us)(object id 93112)
![]() |
![]() |