Re: Passing Parameters to a stored Func.

From: DanHW <danhw_at_aol.com>
Date: 06 Sep 2000 00:31:51 GMT
Message-ID: <20000905203151.07878.00001291_at_ng-fo1.aol.com>


>Hi Jason!
>
>IMO the inserts into the another view causes the 3 min's of run, not the
>select.
>
>Stefan.
>
>jason <jfielding_at_trinitech.co.uk> schrieb in im Newsbeitrag:
>8ovv3p$lng$1_at_trinitech.demon.co.uk...
>> Hi everyone,
>> I've got a stored function which does a selection on one view and inserts
>> the results into another view. The 'Where' clause of the select reads:
>>
>> where vdlf.DOJChangeDateTime > TodaysDate_in
>>
>> vdlf is the view; and TodaysDate_in is the date passed into the function.
>>
>> This function takes about 3 mins to run, but if I hard-code the date in,
>> replacing the 'TodaysDate_in' in the 'Where' clause, the result comes back
>> instantly. I've tried messing around with the format, I can hard-code
>> 'TodaysDate' as a string or date and the result is instant, but if I
 pas-in
>> a date or string, it's 3 mins.
>>
>> Any ideas would be most appreciated.
>> TIA
>> Jason
>>
>>

Do an explain plan on the query with and without the literal. My guess is that you are using/not using the index. (assuming there is one of course). If that is in fact the case, you need to re-write your query so that it uses the index. Maybe try

 where vdlf.DOJChangeDateTime > to_date(TodaysDate_in)

When you have <date> = <string>, if it converts it to <string of date> = <string> then you will not be using the index. However, if you make it <date>=<date>, then it will use the index. I have never seen any documentation that tells how Oracle changes data types in this kind of situation.

HTH
Dan Hekimian-Williams Received on Wed Sep 06 2000 - 02:31:51 CEST

Original text of this message