Hi Monty,
I'd answer yes, there are additional advantages even if the complexity of how you obtain a result is not high.
The benefits that I can think of quickly, in no particular order:
A. Security
- you are able to ensure the integrity of the database design, since (most) developers would not need and would not be given privileges to base tables
- the approach may eliminate certain paths to potential security holes: e.g. anyone managing to exploit the application code won't be able to get to the details of the underlying base tables
B. Preservation of performance
- you may still take advantage of the power of the Oracle optimizer: when there is no need for complex logic, the function returning a collection can simply issue a SQL SELECT statement, which ;can be traced, optimised etc.. The function does not need to do much, if anything, programatically just because it's written in PL/SQL
C. Testability
- once you have the function as a piece of executable PL/SQL, it could (and should) get its own suite of unit test cases (with good test data sets!), ideally integrated with the integration test suite for the whole application. I think it is currently slightly easier to test a PL/SQL function within an integrated test case suite than a native view. I might be wrong in this, though; depends on the approach to and the capabilities of your PL/SQL unit testing.
Of course, since it's a view it shares the encapsulation advantages of a conventional view: you can change table design underneath without code being affected, hence your fellow developers don't get angry at the DBA as often as they may have gotten in the past.
To the best of my knowledge, it is unlikely that a collection-returning function can outperform a conventional view in terms of pure retrieval performance.
However, it can be made to be very close if you don't have complex logic.
If the function just returns the result of a SELECT, just as the equivalent conventional view, then the overhead in PL/SQL should basically be just a function call and some pointer de-referencing inside the Oracle database engine.
I am saying this based on my understanding is that nowadays the SQL and PL/SQL engines are co-mingled (for want of a better word) and there are no costly context switches (process + memory space) anymore when SQL calls PL/SQL and PL/SQL passes result sets back.
Caveat: I might be missing something, though. I haven't got the opportunity to test such a scenario thoroughly and for high volumes; I only used such functions when I had a real need for some logic, which I decided was much easier to maintain in PL/SQL than in contorted SQL, especially for my maintenance successors.
In short, my belief is that performance downside is limited, while the upside is that the overall performance of your development, test and optimisation cycle could get better.
Best regards,
Gabriel
On 3/6/08, Monty Latiolais < Monty.Latiolais publicans.com">Monty.Latiolaispublicans.com> wrote:
Hey ODTUGers,
Dr Dorsey recommends basing views on functions that return collections.
There was even an example in last quarters Technical Journal.
Obviously that allows us to put some very complex logic under a view.
It seems to me that the less complex the view, the less one would need to utilize this approach,
unless, that is, there are additional reasons to do so.
Are there other benefits from this technique that I'm not seeing?
Is there any reason to expect it to outperform a more conventional view??
Monty
|
