Re: Problem with fetch@cursor

A copy of this was sent to deltondm_at_my-deja.com (if that email address didn't require changing) On Fri, 10 Sep 1999 17:01:31 GMT, you wrote:

>Hello Guys,
> I have the following cursor declared
>
>cursor allcount (st_pol varchar2, ed_pol varchar2)is
> select a.polnum,polfrq,servun,taxsta,covsta,covdue,nxtdte,plnnum,
> polmod,coveff,nonfor,decode(nonfor,'ETI','POETI',
> 'RPU','PORPU',
> 'POAUT') forcode
> from covmas b,polmas a /*+ POLMAS_PK_POLNUM */
> where b.polnum = a.polnum
> and b.covnum = 1
> and b.covdue > b.coveff
> and a.polnum between nvl(st_pol,a.polnum) and nvl(ed_pol,a.polnum);
>
>I use it as follows
>
> ...
> open allcount( 'xxx','yyyy' );
> loop
> fetch allcount into c;
> exit when allcount%notfound;
> ..
> end loop;
> close allcount;
>
>The problem is that all the fetches return quickly except the last
>one that is suppose to return not found. Indexes are on
>polmas.polnum and covmas.polnum+covmas.covnum
>

It looks like its full scanning to get the answer as none of the indexes can help in resolveing the predicate. The indexes help the join but not the predictate.

The predcate has:

where b.polnum = a.polnum -- join condition. Either index could be used for this and one probably is.

and b.covnum = 1 -- predicate, cannot use the index since covnum is second in the index.

and b.covdue > b.coveff -- predicate, cannot use the index since neither side is a constant.

and polumn between nvl(st_pol,a.polnum) and nvl(ed_pol,a.polnum) -- predicate, cannot use index since the between values are not constants, they must be evaluated for each row.

So, assuming its using the RBO (hint isn't in the right place so that would not force it to use CBO) it would:

full scan polmas

   index range scan on the index on covmas

       table access covmas by rowid

The reason the first rows return first must be because the first rows from the polmas table join to records in covas that have a covnum = 1 and covdue > coveff (that is -- its an accident they are returned fast in the beginning, unload and reload the tables and you might find you get the first 10 rows fast, wait along time, get the last 10 rows).

We must finish full scanning to tell you there is no more data.

If covnum in the covmas table is 'discriminating' (eg: where covnum = 1 returns less then 20% of the covmas table) consider putting an index on covnum.

Look into using explain plan and or autotrace in sqlplus to find out what the query is doing.

>Thanks for any help as to what can be causing this problem.
>
>Delton.
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Sep 10 1999 - 12:29:38 CDT