variable in a view
April 4th, 2007
Imagine you have a view and you want to have a parameter in your view. You cannot have a bind variable in your view. But you could have a function which return a package variable. And this package variable could be set manually for your session
Here we go
SQL> create or replace package p is n number; end p; 2 / Package created. SQL> create or replace function f return number is 2 begin return p.n; end; 3 / Function created. SQL> create or replace view v as select ename from 2 emp where empno=f; View created. SQL> select * from v; no rows selected SQL> exec p.n:=7788 PL/SQL procedure successfully completed. SQL> select * from v; ENAME ---------- SCOTT
Simple and clever, thank you for sharing
http://forums.oracle.com/forums/thread.jspa?messageID=1695969�
The alternative to a packaged global variable is an application context using SYS_CONTEXT.
Worth a read. Behavior may vary in later database versions…
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1288401763279#3302928446952
I’ve been using this technique for year. Everything started in Oracle 8 when pushing predicates into complex view didn’t work as expected. It solved many performance problems.
Stuff like this is very often used in “thick DB” or “database centric” approaches which I find very promising. Consider for example the papers of Toons Koppelaars (http://web.inter.nl.net/users/T.Koppelaars/).
Using contexts will probably be faster, because it doesn’t involve a context switch to pl/sql and the optimizer can make better use of it. If you use a function, you might want to say it’s deterministic, btw.
thanks all for the pointers to sys_context.
Chris, the function is not deterministic
Thanks for mentioning that I am mistaken, Laurent. I was under the impression that the “deterministic” clause refers to the time span of running a sql statement, which is nonsense. You live and learn, e.g. not to rely on heresay but read the docs and test
by the way I am reading your document with great interest. I am currently in a J2EE project where the database has no importance for the developers. So your document give me some lights about how to argue that there is something more than select/insert/update in an Oracle Database. I wonder if I ever will write about this. For the moment it is more political than technical, so now no post about hibernate and OptimalJ
Glad that you’re interested. The papers of Paul Dorsey (http://www.dulcian.com/Papers%20by%20topic/Methodology.htm) could help you in this discussion, too. The “The Thick Database Approach - Revisited” paper for example compares the development time / LOC of Java centric vs. database centric solutions. I suppose you will like the conclusion:
“In our experience, moving code from the middle tier to the database had the following benefits:
* Reduced the total amount of code
* Reduced development time
* Improved performance
* Reduced network traffic
* Reduced the complexity of the application
Therefore, the thick database approach is a viable alternative to the conventional wisdom of reducing reliance on the database. It leverages existing database talent and can result in dramatic improvements in performance. The current trend in moving logic to the middle tier may have been premature given the experiences detailed in this paper.”
I agree that this can be done. I’m missing something though. Why would you, why not just push predicate to the view when the view merge occurs (basically just using a where with this in the SQL that accesses the view)?
That would be more effective as just one call needs to be executed. I’m sure this is useful somehow, but I’m not really sure when.
it makes the view somehow dynamic, for example if the view is selected from a reporting tool like BO, you can hide the logic.
I have never used it myself
Mathias: predicates can’t be pushed past agregating clauses like union, distinct, count, sum etc. (see Pawel’s comment above). For performance you usually want to get the filtering done at the earliest stage. Oracle fine grained access (FGAC) uses the “context” approach.