RE: High "elapsed" time when running on a Unique Index ??

With 10 indexes to choose from, CBO might be getting a little confused. Does explain plan show using an index or doing a full table scan? I'd test using a hint on the unique index.

Jerry Whittle
ACIFICS DBA
NCI Information Systems Inc.
jerome.whittle_at_scott.af.mil
618-622-4145

> -----Original Message-----
> From: VIVEK_SHARMA [SMTP:VIVEK_SHARMA_at_infy.com]
>
> > Qs Why is "elapsed" Time so HIGH in the following SELECT ?
> >
> > NOTE -
> > 1) TBA_GENERAL_ACCT_MAST_TBL is a SYNONYM for GENERAL_ACCT_MAST_TABLE Table
> > 2) The Respective Tablespace Contains ONLY ONE (i.e. This) Object
> > (GENERAL_ACCT_MAST_TABLE)
> > 3) There is a UNIQUE Index on the field of the Where Clause (i.e. acid)
> > for the Object (GENERAL_ACCT_MAST_TABLE)
> > 4) This Table has a Total of 10 indexes of which 5 are present in 1 Tablespace
> > & 5 are present in a 2nd Tablespace .
> > 5) This Table is a Highly Accessed Table day-to-day Working of the Application
> >
> > Select
> > ,TO_CHAR(notional_rate) ,notional_rate_code ,TO_CHAR(fx_clr_bal_amt) ,
> > TO_CHAR(fx_bal_on_purge_date) ,fd_ref_num ,TO_CHAR(fx_cum_cr_amt) ,
> > TO_CHAR(fx_cum_dr_amt) ,crncy_code ,source_of_fund ,anw_non_cust_alwd_flg ,
> > acct_crncy_code ,TO_CHAR(lien_amt) ,acct_classification_flg ,
> > system_only_acct_flg ,single_tran_flg ,TO_CHAR(utilised_amt) ,
> > inter_sol_access_flg ,purge_allowed_flg ,purge_text ,TO_CHAR(min_value_date,
> > 'DD-MM-YYYY HH24:MI:SS') ,acct_mgr_user_id ,schm_type ,
> > TO_CHAR(last_frez_date,'DD-MM-YYYY HH24:MI:SS') ,TO_CHAR(last_unfrez_date,
> > 'DD-MM-YYYY HH24:MI:SS') ,TO_CHAR(bal_on_frez_date) ,swift_allowed_flg ,
> > TO_CHAR(dacc_lim_pcnt) ,TO_CHAR(dacc_lim_abs) ,chrg_level_code ,
> > acct_cls_chrg_pend_verf ,partitioned_flg ,partitioned_type ,
> > pbf_download_flg ,TO_CHAR(pbf_delink_date,'DD-MM-YYYY HH24:MI:SS') ,
> > wtax_flg ,wtax_amount_scope_flg ,int_adj_for_deduction_flg ,operative_acid ,
> > phone_num ,native_lang_name ,nat_lang_title_code ,lang_code ,NVL(ts_cnt,0) ,
> > rowid into :b0,:b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12,:b13,:b14,
> > :b15,:b16,:b17,:b18,:b19,:b20,:b21,:b22,:b23,:b24,:b25,:b26,:b27,:b28,:b29,
> > :b30,:b31,:b32,:b33,:b34,:b35,:b36,:b37,:b38,:b39,:b40,:b41,:b42,:b43,:b44,
> > :b45,:b46,:b47,:b48,:b49,:b50,:b51,:b52,:b53,:b54,:b55,:b56,:b57,:b58,:b59,
> > :b60,:b61,:b62,:b63,:b64,:b65,:b66,:b67,:b68,:b69,:b70,:b71,:b72,:b73,:b74,
> > :b75,:b76,:b77,:b78,:b79,:b80,:b81,:b82,:b83,:b84,:b85,:b86,:b87,:b88,:b89,
> > :b90,:b91,:b92,:b93,:b94,:b95,:b96,:b97,:b98,:b99,:b100,:b101,:b102,:b103,
> > :b104,:b105,:b106,:b107,:b108,:b109,:b110,:b111,:b112,:b113,:b114,:b115,
> > :b116
> > from
> > TBA_GENERAL_ACCT_MAST_TBL where acid=:b117
> >
> >
> > call count cpu elapsed disk query current rows
> > ------- ------ -------- ---------- ---------- ---------- ---------- ----------
> > Parse 1 0.00 0.00 0 0 0 0
> > Execute 6928 3.99 999.46 0 0 0 0
> > Fetch 6928 4.47 5.57 2171 27717 0 0
> > ------- ------ -------- ---------- ---------- ---------- ---------- ----------
> > total 13857 8.46 1005.03 2171 27717 0 0

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Whittle Jerome Contr NCI
  INET: Jerome.Whittle_at_scott.af.mil

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).