RE: Views based on function returning table

My opinion is that you should use SQL when you can and PL/SQL only when it is necessary.  If the query is fixed, use a regular view – I suspect that it will perform better.  As Tom Kyte would say, TEST this assumption – that is the only way to KNOW which performs better.

 

Not that you should never use a function that returns a collection.  Functions that return collections can do things that would be difficult or impossible with a regular view. ; For instance, I have a package that is a shell for a Java Stored Procedure that lets you treat a directory as a table – so you can list the files in that directory with a simple SELECT.  You can add parameters to functions that return collections, and you can use those parameters to FORCE people to use bind variables, which is a very good thing for performance.  Once you add parameters, however, you can only put it in a view for a fixed set of parameters. You can also use these functions to hide the underlying table structure.

 

By the way, if you write a function that returns a collection, and you plan to use it in SELECTs, it is best to make it a pipelined function.  That way, the entire collection doesn't have to be in memory, and the database can process it in parallel.

 

 

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