Home » SQL & PL/SQL » SQL & PL/SQL » purity level concept (Oracle 10.2.0.3)
purity level concept [message #438852] Tue, 12 January 2010 10:40 Go to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
I read the link on purity level

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28425/pl_sql.htm

My bad I could not understand much through the examples given


Can anyone please help me to understand about this feature with simple example.Why should we use and when to use?

Appreciate your help on this topic.

Regards,
Ved

Re: purity level concept [message #438891 is a reply to message #438852] Tue, 12 January 2010 21:27 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Consider the following function, which increments a package global and returns the result:
CREATE OR REPLACE FUNCTION MY_FUNC RETURN INTEGER AS
BEGIN
    MY_PACKAGE.MY_INTEGER := NVL(MY_PACK.MY_INTEGER,0) + 1;
    RETURN(MY_PACK.MY_INTEGER);
END;


Then we use it in the following query:
SELECT a.*, my_func()
FROM   my_table;


It is pretty easy to see that each time we run this query in a single session, we get a different result. This means the function is NON-DETERMINISTIC. The thing that makes it non-deterministic is that it UPDATES the PACKAGE STATE.

This is usually fine though, because SYSDATE is also non-deterministic, and we can use that in most places.

However, there are restrictions with non-deterministic functions. For example, you cannot use them in a Materialised View.

The Purity Level of a function tells us what it does and does not do, such as read/write database state, or read/write package state. Generally, a function that writes neither database nor package state is usually deterministic (unless it uses SYSDATE or some other non-deterministic feature).

Functions can be used in Materialised Views, Index Types, Function-based indexes, user-defined aggregation functions, and probably lots of other stuff.

Depending on the purity-level of the function, it may be banned from use in one or more of these areas, or it may be permitted in a limited fashion (eg. may not parallelise).

Ross Leishman
Re: purity level concept [message #438923 is a reply to message #438891] Wed, 13 January 2010 01:23 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Thanks for the nice explanation.
I did not understand when you meant

"Depending on the purity-level of the function, it may be banned from use in one or more of these areas, or it may be permitted in a limited fashion (eg. may not parallelise)"

Did you meant that we can impose purity levels on non deterministic function?

Regards,
Ved
Re: purity level concept [message #438935 is a reply to message #438923] Wed, 13 January 2010 02:28 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
No, you cannot make a non-deterministic function deterministic by using PRAGMAs.

In the early days of v7, if you wanted to use a user-defined function in SQL, you HAD to provide a pragma that guaranteed Write-No-Database-Stage (WNDS) and Write-No-Package-State (WNPS). In later versions, this was relaxed because the compiler worked it out for itself. This made the PRAGMAs somewhat redundant - at least in some cases.

Now, I'm not sure under which circumstances you need to provide the PRAGMAs (like I said - the compiler can work a lot of it out for itself). There is probably something in that doco that would tell you though.

Ross Leishman
Re: purity level concept [message #438950 is a reply to message #438935] Wed, 13 January 2010 03:16 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Thanks once again for the quick reply.

Quote:

I'm not sure under which circumstances you need to provide the PRAGMAs (like I said - the compiler can work a lot of it out for itself). There is probably something in that doco that would tell you though.


I wanted to know under which circumstances we need to provide the PRAGMAs. Any practical scenarios if anyone have worked on it?

Quote:

the compiler can work a lot of it out for itself

So, you meant to say its no more a developer job after Oracle v7?
Many thanks..

Regards,
Ved

[Updated on: Wed, 13 January 2010 03:16]

Report message to a moderator

Re: purity level concept [message #438975 is a reply to message #438950] Wed, 13 January 2010 05:50 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Not saying that at all. I thought I was being pretty clear when I said I was not very clear about it.

Suggest you re-read the documentation now that you understand the concept better, as the answer probably lies there.

Ross Leishman
Re: purity level concept [message #438978 is a reply to message #438950] Wed, 13 January 2010 06:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I know that you still need to use Restrict_References with the TRUST option if you're calling functions that are written in Java, as the optimiser cannot parse them to determine their status.
Re: purity level concept [message #439029 is a reply to message #438978] Wed, 13 January 2010 12:26 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Thanks for the information. Is there any link that demonstarates the example with some practical scenarios.A complete demo on this. Shameless me!!!!!!!! I am not been able to figure out its implementation when I read the link.

Regards,
Ved
Re: purity level concept [message #439044 is a reply to message #438852] Wed, 13 January 2010 13:20 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Maybe some background on PURITY LEVEL will help. Here is what I believe is so, though there is plenty of room for me to be wrong so someone who knows better than I should speak up if they see a mistake.

PURITY LEVEL affects several things in oracle when code runs, but most notably it affects optimization.

When a piece of sql runs, oracle can optimize it many different ways. Some of the more advanced optimization techniques are not based on which index to use, but rather on how to rewrite a query to a sematically equivelant form that will run faster yet will still yeild a correct result. To this end there has been a great deal of research and thinking done by database vendors and oracle in particular to invent intelligent and creative ways to rewrite a query.

But as you can imagine, like any other theory, there are rules and requirements that must be adhered to in order to make certain that any rewrite created is fully correct. So here is the kicker... Many rewrite optimizations are valid only if certain facts are true about a query. PURITY LEVEL is one way to describe such facts. If a query satisfies only a low level of purity then some rewrite techniques can be used but not others. But if a query is at the highest level of purity, then all rewrite techniques can be used on the query without fear of creating a version of the query that is not semantically equivelant or which generates an incorrect answer.

So PURITY LEVEL affects query rewrite. The more pure the query, the more rewrite techniques that can be employed to rewrite it.

But what is PURITY LEVEL? One way to think about PURITY LEVEL is as a description of how a piece of code interacts with its environment. Here is a list of PURITY LEVEL names and what they mean:

RNDS  --  read no database state
RNPS  --  read no package state
WNDS  --  write no database state
WNPS  --  write no package state

RNDS means that the covered piece of code does not select data from the database.

RNPS means that the covered piece of code does not read package global variables.

WNDS means that the covered piece of code does not insert/update/delete or otherwise change data in the database.

WNPS means that the covered piece of code does not change the values of global package variables.

These concepts apply to 3gl code like PLSQL and JAVA. If a PLSQL function for example, changes package global variables, then it cannot claim to support WNPS PURITY LEVEL. If a SELECT statement calls this package function then the calling SQL statement also cannot claim to support WNPS PURITY LEVEL because it must accept the limits of the PLSQL code it is calling. Thus query rewrite techniques that require a WNPS PURITY LEVEL cannot be used on this piece of SQL code.

Normally oracle can look at a piece of code and know what the PURITY LEVEL is so you do not have to tell oracle. But sometimes it can't and so you have to supply a RESTRICT_REFERENCES pragma to tell oracle what the PURITY LEVEL is. You do not have to worry about being wrong because if you are, then at runtime Oracle will figure that out and generate an error.

Either way, at compile time or at runtime, oracle will tell you if you need to specify a pragma for your code to be executable, or if what you specified is invalid. But again, most of the time you do not need to do anything. Indeed I have not used the RESTRICT_REFERNCES pragma since version 9i of the database.

If you want to know more detail, there is plenty of info available with a simple GOOGLE. But to answer your initial question directly:

in the early days (oracle8i) we had to specify the PURITY LEVEL of plsql code a lot because Oracle was not very good at figuring it out for itself. But with currently supported releases 9i, 10g, 11g, oracle is way better at it so we almost never specify the PURITY LEVEL of a plsql component. Oracle will tell you if you need to do so either at compile time or runtime.

Maybe someone else knows of specific situations where it is needed. One was already mentioned related to JAVA code.

Good luck, Kevin
Re: purity level concept [message #439178 is a reply to message #439044] Thu, 14 January 2010 15:40 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Thank you once again.
As always, your articles/messages are so much helpful. Thanks once again for giving your valuable time to make things easier for me to understand this concept.


Regards,
Ved
Re: purity level concept [message #488760 is a reply to message #439178] Tue, 11 January 2011 00:55 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
Any example or reference of purity level in packages or types.
Re: purity level concept [message #509341 is a reply to message #488760] Fri, 27 May 2011 05:09 Go to previous messageGo to next message
orsoghu
Messages: 46
Registered: May 2011
Location: Venice, Italy
Member
So for example if I use a procedure with a dynamic query created by a string and executed with execute immediate, specifying the purity level might help oracle understandingb what it will do?
Re: purity level concept [message #509369 is a reply to message #509341] Fri, 27 May 2011 06:56 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Got a more specific example of what you mean?
Re: purity level concept [message #509377 is a reply to message #438852] Fri, 27 May 2011 07:10 Go to previous messageGo to next message
orsoghu
Messages: 46
Registered: May 2011
Location: Venice, Italy
Member
I don't have any real example right now

but i'm thinking something like:


PROCEDURE do_something (operation IN VARCHAR2, table_name IN VARCHAR2, conditions IN VARCHAR2)


sql_query varchar2(4000) := operation || table_name || conditions;

BEGIN

EXECUTE IMMEDIATE sql_query;

END;





This can get a lot more complex, but in any case I imagine that oracle wouldn't have any clue of what is going to happen until execution time. So what I would like to understand is:"may this be an example of where purity level specification is needed?"
Re: purity level concept [message #509380 is a reply to message #509377] Fri, 27 May 2011 07:15 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Purity level is for functions/procedures that are called from SQL statements - select, insert, update etc.
And since you can't directly call procedures from SQL they would have to called via a function.
So I doubt purity level is relevant here.
Re: purity level concept [message #509393 is a reply to message #509380] Fri, 27 May 2011 08:29 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I do not know the answer for sure. However:

In today's Oracle, if you try to use code that does not satisfy the necessary purity leve, Oracle will throw an error so you will know at runtime if you have violated a necessary purity level.

In this situation, Oracle cannot tell what type of operation you are doing (SELECT/INSERT/UPDATE/DELETE/another PLSQL call) inside the execute immediate. I suspect Oracle will do one of two things:

1) it will take the most restrictive option and assume you are doing all things and hence mark your code as not a satisfying any purity level.

2) it will take the least restrictive option and let runtime error trapping catch a violation of purity level if it happens.

My money is on #2 but this is easy enough to find out with a simple test. Why don't you create one and see.

Kevin

[Updated on: Fri, 27 May 2011 08:30]

Report message to a moderator

Re: purity level concept [message #509394 is a reply to message #509393] Fri, 27 May 2011 08:34 Go to previous messageGo to next message
orsoghu
Messages: 46
Registered: May 2011
Location: Venice, Italy
Member
I could, but I have absolutely no idea of how to see what purity level will it choose Razz

Anyway after more thinking, my idea is that in my example the query would be evalutated at run-time, so when Oracle know it's contents and therefore specifying a purity level in the procedure would be useless.

I'm beginning to think that as you said, this is something that was needed more in previous Oracle versions but know it seems kinda useless

[Updated on: Fri, 27 May 2011 08:35]

Report message to a moderator

Re: purity level concept [message #509399 is a reply to message #509393] Fri, 27 May 2011 08:45 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
I didn't think my reply through there did I?
Re: purity level concept [message #509401 is a reply to message #509394] Fri, 27 May 2011 08:49 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
orsoghu wrote on Fri, 27 May 2011 14:34
I could, but I have absolutely no idea of how to see what purity level will it choose Razz


There's only 4, what's the problem?
Simple example:
SQL> CREATE TABLE bob (a NUMBER);

Table created.

SQL> CREATE PACKAGE bob_pkg AS
  2  
  3  PROCEDURE bob_proc_dynamic;
  4  pragma restrict_References (bob_proc_dynamic, WNDS);
  5  
  6  END bob_pkg;
  7  /

Package created.

SQL> CREATE PACKAGE BODY bob_pkg AS
  2  
  3  PROCEDURE bob_proc_dynamic AS
  4  BEGIN
  5  
  6    EXECUTE IMMEDIATE 'INSERT INTO bob (a) values (1)';
  7  
  8  END bob_proc_dynamic;
  9  END bob_pkg;
 10  /

Package body created.

SQL> CREATE PACKAGE bob_pkg_2 AS
  2  
  3  PROCEDURE bob_proc;
  4  pragma restrict_References (bob_proc, WNDS);
  5  END bob_pkg_2;
  6  /

Package created.

SQL> CREATE PACKAGE BODY bob_pkg_2 AS
  2  
  3  PROCEDURE bob_proc AS
  4  BEGIN
  5  
  6    INSERT INTO bob (a) values (1);
  7    COMMIT;
  8  
  9  END bob_proc;
 10  
 11  END bob_pkg_2;
 12  /

Warning: Package Body created with compilation errors.

SQL> sho errors
Errors for PACKAGE BODY BOB_PKG_2:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/1      PLS-00452: Subprogram 'BOB_PROC' violates its associated pragma
SQL> 
SQL> 
SQL> select * from bob;

no rows selected

SQL> exec bob_pkg.bob_proc_dynamic;
BEGIN bob_pkg.bob_proc_dynamic; END;

*
ERROR at line 1:
ORA-06538: statement violates WNDS RESTRICT_REFERENCES pragma
ORA-06512: at "LIVE.BOB_PKG", line 6
ORA-06512: at line 1


SQL> 


No idea how useful that is.
Re: purity level concept [message #509402 is a reply to message #509401] Fri, 27 May 2011 08:53 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
And thinking about it you can't write or read a package state with dynamic sql, so only WNDS and RNDS are relevant in this case.


EDIT: missing 'it'.

[Updated on: Fri, 27 May 2011 08:53]

Report message to a moderator

Re: purity level concept [message #509406 is a reply to message #509399] Fri, 27 May 2011 08:58 Go to previous messageGo to next message
orsoghu
Messages: 46
Registered: May 2011
Location: Venice, Italy
Member
Quote:
I'm beginning to think that as you said, this is something that was needed more in previous Oracle versions but know it seems kinda useless


That was for Kevin.

Anyway thanks for your example cookie, that gave me the idea that the purity level could be used to trap dynamic sql behaviours that you don't want to accept:

in my previous example let's say that you want to allow people to build dynamic SELECTs but not INSERTs, so with prama you could catch that exception and throw a punch in the user's eye.

Ok I think I got it now Smile
Re: purity level concept [message #509411 is a reply to message #509406] Fri, 27 May 2011 09:16 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
orsoghu wrote on Fri, 27 May 2011 14:58
in my previous example let's say that you want to allow people to build dynamic SELECTs but not INSERTs, so with prama you could catch that exception and throw a punch in the user's eye.


You can. However, if you're allowing users that much freedom in dynamic sql you're probably opening up your system to SQL Injection attacks. You don't want to be the next SONY.
Re: purity level concept [message #509414 is a reply to message #509411] Fri, 27 May 2011 09:22 Go to previous messageGo to next message
orsoghu
Messages: 46
Registered: May 2011
Location: Venice, Italy
Member
Quote:
You can. However, if you're allowing users that much freedom in dynamic sql you're probably opening up your system to SQL Injection attacks. You don't want to be the next SONY.


Laughing yes I agree, that was just the first trivial example that came up in my mind (well the reason behind all this is that i'm studying for the pl/sql certification and there was a question about purity level in the exam samples)
Re: purity level concept [message #509418 is a reply to message #509414] Fri, 27 May 2011 09:32 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
I can think of a use.

On a previous system I had a function to convert monetary ammounts from one currency to another. This function was called from various SQL queries and PL/SQL functions/procedures.
At some point someone decided to modify the function so that if it encounters a currency it hadn't seen before it would write an entry in a log table to record the fact.
This worked fine until someone ran a report for data with a new currency and promptly got an error saying you can't do DML from within a select.
If we had added the WNDS state pragma to that function in the first place it probably would have prevented that mistake.
(To be fair a proper review of the change ought to have prevented it as well).

I was thinking of that in my first reply to you.
Re: purity level concept [message #509433 is a reply to message #509418] Fri, 27 May 2011 10:17 Go to previous message
orsoghu
Messages: 46
Registered: May 2011
Location: Venice, Italy
Member
I got that error too, trying to find a use to purity level, so I guess that must be it:

create table QUERY_LOG
(
  USER_NAME    VARCHAR2(30),
  REFRESH_DATE DATE default SYSDATE,
  QUERY_TYPE   VARCHAR2(30)
)
tablespace USERS;

------------------------------------

CREATE OR REPLACE FUNCTION max_log_date(p_user VARCHAR2) RETURN DATE IS

  max_date DATE;

BEGIN

  SELECT MAX(refresh_date)
    INTO max_date
    FROM query_log
   WHERE user_name = p_user;

  INSERT INTO query_log VALUES (p_user, sysdate, 'SELECT');

  COMMIT;

  RETURN max_Date;

END max_log_date;

------------------------------------------

SELECT *
  FROM query_log
 WHERE refresh_date = max_log_date(user)




thanks for your help!
Previous Topic: PL/SQL package (merged 2)
Next Topic: Reading BLOB field to a REF CURSOR (3 merged by CM)
Goto Forum:
  


Current Time: Sat Dec 10 17:01:02 CST 2016

Total time taken to generate the page: 0.08893 seconds