RE: sql tuning quandry

>But now in your rewritten function the sql is different from the one posted
>earlier and from the one executed in sqlplus:

>SELECT UNIT_PRICE
>FROM
>PBM_DRUG_PRICE P WHERE DRUG_NDC = :b1 AND PRCLIST_ID + 0 = :b2 AND
> EFFECTIVE_BEGIN = (SELECT MAX(EFFECTIVE_BEGIN) FROM PBM_DRUG_PRICE
> WHERE DRUG_NDC = P.DRUG_NDC AND PRCLIST_ID + 0 = P.PRCLIST_ID )
>
>where do the "+0" in the prclist_id predicates suddenly come from? That of
>course precludes the full use of the index, resulting in range scans.

At first I thought I had uploaded the wrong tkprof output. They are beginning to pile up on my end. What I discovered is that actually the developer who wrote the function in question went in and recompiled his function. I gave him an update, and he decided to rewrite the function to use back references to the main query(which I had already done). His code implements the plus zeros in order to, as you indicate, suppress the use of an index. The reason being that his code is currently in use on our production(RBO) system. I was doing the testing on a test instance(CBO). We are about to migrate production from RBO to CBO. The fact that the index is suppressed is not having an impact on the results. I originally tested the query without the +0s, and got the same results in terms of fetches/reads. Sorry for the distraction, I guess I should actually review my post before sending mail.

> From what you posted, you only have the second, ergo not the real
>execution plan. Your trace has no STAT data and therefore the real plan is
>missing and the explained plan doesn't have row counts.
>...
>i.e. cast the parameters into local variables of the exact type of the
>columns before using them in the sql and see what happens.

I am working on this now. We had an extended power outage yesterday, and I am still trying to juggle a few balls left in the air. So I may be a while before I post my results. Thanks for all the help Wolfgang, it is much appreciated.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Steve McClure
  INET: smcclure_at_usscript.com

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