| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: user-defined functions
A copy of this was sent to jmodic_at_src.si (Jurij Modic)
(if that email address didn't require changing)
On Wed, 30 Dec 1998 22:18:56 GMT, you wrote:
>On Wed, 30 Dec 1998 14:38:17 GMT, andrewf_at_jaredgroup.com wrote:
>
>>In article <3689C79F.9AB8EC77_at_sybrandb.demon.nl>,
>> Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote:
>>>
>>> In the past I have been working with user-defined functions in selects which
>>> were issuing additional selects (I didn't design them luckily). The
>>> performance of this was usually quite bad.
>>
>>User defined functions should be designed carefully. There are situations you
>>just need these additional selects and the function can simplify your code and
>>give better performance.
>>
>>My concern is, it is said that user defined functions are not subject to
>>Oracle read consistent model. If so, be more careful as you may get
>>inconsistent result.
>
>Can you give more details about this possible read inconsistency or
>give any reference to the literature where this is mentioned? I'm not
>avare and have not read of any such warnings about using user defined
>PL/SQL functions.
>
typically, when you start a query -- the answer is 'preordained' due to the read consistency model. Even if someone comes along while you are running your long running query and deletes every row in the table you are querying and commits -- you will continue to see those rows to get the answer that existed in the database at the point in time your query began.
There are 2 exceptions to this.
One is the v$ tables. The have no redo and are always read in current mode (they aren't really tables). You can see this by:
SQL> select hsecs from v$timer, ( select rownum from all_objects where rownum < 100 );
HSECS
8445745
8445745
8445745
8445745
8445745
8445745
8445745
8445745
8445745
8445745
8445745
8445745
8445745
8445745
8445745
8445745
8445747
8445747
8445747
8445747
8445747
....
see, the value of hsecs changed in the middle. that is 'impossible' if v$timer was a real table. You must be cautious when using v$ tables in subqueries and such in DML (insert/update/delete) as strange things can occurr (since they don't always return the same answer twice in the same query).
pl/sql called from sql is the other exception. If the pl/sql function you call queries the database -- each query it makes is read consistent with respect to itself however it not NOT read consistent with respect to the query calling it. It (the pl/sql function) can and will see changes made by others and committed whereas the invoking query will NOT see those same changes. The solution to this, if you cannot work in this mode, is to set your transaction to read_only or serializable. That way, the read consistency model is spread across your entire transaction, not just individual statements.
>As I understand it, that's why function's purity level must be
>specified with "PRAGMA RESTRICT REFERENCES" if you wan't to use
>function in SQL command. If this specified purity level is violated
>during execution, oracle will not execute the function and ORA error
>will be returned.
>
wait till 8i :) big time relaxed rules on this feature (and no need for pragma restrict_references at all -- still supported but no longer needed). In fact, there are cases where pl/sql functions that modify the database CAN be called from SQL (not selects but inserts/updates/deletes)... Also, dynamic sql can be done in pl/sql called from sql (including selects)....
>Regards,
>Jurij Modic <jmodic_at_src.si>
>Certified Oracle7 DBA (OCP)
>================================================
>The above opinions are mine and do not represent
>any official standpoints of my employer
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Dec 31 1998 - 08:31:21 CST
 |
 |