RE: Query Optimis

From: Stephane Faroult <sfaroult_at_oriolecorp.com>
Date: Wed, 26 Jun 2002 07:28:30 -0800
Message-ID: <F001.004888D9.20020626072830@fatcity.com>

('binary' encoding is not supported, stored as-is)
The name b.insert_sequence smells (to me) of number. Your 'like' generates an implicit to_char() conversion which prevents Oracle from using the index - plus the OR, it gives a full scan an irresistible appeal. You should rethink how you pass your inp_insrt_seq and do your comparison, if my guess is correct.

>----- Original Message -----
>From: cosltemp-g.manoj_at_orbitech.co.in
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Wed, 26 Jun 2002 03:34:35
>
>Hi,
> I have a cursor which is taking some time to
>execute (when i/p para
>is null)
>How can the cursor qry be optimised.
>Only either of 2 subqry execute depend on input
>parameter (null or not
>null ) currently hardcoded as null
>the o/p qry gives about 70K rows.
>
>Note : Unique index on b.insert_sequence is
>present,
> primary key on (
>fin_rec_id,fin_rec_serial_no,inst_dt) is present
>in table aliased b
> primary key on ( fin_rec_id,fin_rec_serial_no)
>is present in table
>aliased a
> there is only one row in map_timestamp for
>specified
>mapping_name,schema_name
>
>
>cursor pop_ln_dtls(inp_insrt_seq varchar2 := NULL)
>is
>select
>a.fin_rec_id,a.fin_rec_serial_no,a.cosmos_base_no,
>a.br_cod,a.tran_ref_no,inst_dt,
>prin_amt,b.insert_sequence
>from obs_fin_rec a,obs_fin_schd b
>where a.fin_rec_id = b.fin_rec_id
>and a.fin_rec_serial_no = b.fin_rec_serial_no
>and (((ip_ln_ref_no is null) and
>(
> b.insert_sequence >
>(
> select nvl(last_sequence,0)
> from map_timestamp
> where mapping_name = c_mapping_name
> and schema_name = c_schema_name
>))) or
>(
> (ip_ln_ref_no is not null) and
> (tran_ref_no = ip_ln_ref_no) and
> (b.insert_sequence like inp_insrt_seq||'%' )
>))
>order by b.insert_sequence;
>
>
>Thanks
>Manoj.

Regards,

Stephane Faroult
Oriole

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroul
  INET: sfaroult_at_oriolecorp.com

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).

Received on Wed Jun 26 2002 - 10:28:30 CDT