On Tuesday 19 November 2002 22:03, you wrote: > Qs What is the Cause in particular (or in General) of Time Difference > between "cpu" & "elapsed" Columns in the following Query ? > e = c + wait time (of anykind) (+ rounding errors)
> Qs Is there Any Scope for improvement in the following Query ? > There probably is. > Qs Is there any Best practise of working with Such Tables ? > > NOTE - > 1) (tran_date , tran_id , part_tran_srl_num) fields form the unique > index on the Table > 2) Some Other Columns of the Table are also indexed > 3) The Table is a Very Huge History Table to which only INSERT & SELECT > Operations happen > 4) The Table is the Largest of ALL Tables in the Database With a Size of > about 100 GB > > > ************************************************************************ > ******** > > select del_flg, tran_type, tran_sub_type, part_tran_type, > gl_sub_head_code, > acid, TO_CHAR(value_date,'DD-MM-YYYY HH24:MI:SS'), > tran_amt||'!'||tran_crncy_code, tran_particular, entry_user_id, > pstd_user_id, vfd_user_id, TO_CHAR(entry_date,'DD-MM-YYYY > HH24:MI:SS'), > TO_CHAR(pstd_date,'DD-MM-YYYY HH24:MI:SS'), > TO_CHAR(vfd_date,'DD-MM-YYYY > HH24:MI:SS'), rpt_code, ref_num, instrmnt_type, TO_CHAR(instrmnt_date, > 'DD-MM-YYYY HH24:MI:SS'), instrmnt_num, instrmnt_alpha, tran_rmks, > pstd_flg, > prnt_advc_ind, amt_reservation_ind, > reservation_amt||'!'||tran_crncy_code, > restrict_modify_ind, lchg_user_id, TO_CHAR(lchg_time,'DD-MM-YYYY > HH24:MI:SS'), rcre_user_id, TO_CHAR(rcre_time,'DD-MM-YYYY > HH24:MI:SS'), > cust_id, voucher_print_flg, module_id, br_code, > fx_tran_amt||'!'||crncy_code, rate_code, TO_CHAR(rate), crncy_code, > navigation_flg, tran_crncy_code, ref_crncy_code, > ref_amt||'!'||ref_crncy_code, sol_id, bank_code, trea_ref_num, > TO_CHAR(trea_rate), NVL(ts_cnt,0), rowid > FROM > TBA_CUM_TRAN_DETAIL_TBL WHERE tran_date = TO_DATE( :1 ,'DD-MM-YYYY > HH24:MI:SS') AND tran_id = :2 AND part_tran_srl_num = :3 > > > call count cpu elapsed disk query current > rows > ------- ------ -------- ---------- ---------- ---------- ---------- > ---------- > Parse 1 0.02 0.02 1 0 1 > 0 > Execute 20000 2.62 2.43 0 0 0 > 0 > Fetch 20000 7.10 8.79 7705 100001 0 > 20000 > ------- ------ -------- ---------- ---------- ---------- ---------- > ---------- > total 40001 9.74 11.24 7706 100001 1 > 20000 > > Misses in library cache during parse: 1 > Optimizer goal: RULE > Parsing user id: 20 (TBAGEN) > > Rows Row Source Operation > ------- --------------------------------------------------- > 20000 TABLE ACCESS BY INDEX ROWID CUM_TRAN_DETAIL_TABLE > 40000 INDEX UNIQUE SCAN (object id 10353) > > > Rows Execution Plan > ------- --------------------------------------------------- > 0 SELECT STATEMENT GOAL: RULE > 20000 TABLE ACCESS (BY INDEX ROWID) OF 'CUM_TRAN_DETAIL_TABLE' > 40000 INDEX (UNIQUE SCAN) OF 'IDX_CUM_TRAN_DETAIL_TABLE' (UNIQUE) -- ---------------------------------------------------------------- Anjo Kolk http://www.oraperf.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).