Caveats for using pipelined table functions in Oracle to implement parametrized view functionality?

Question

We've made a decision to layer some "parameterized views" over some regular views in Oracle in order to properly encourage correct where predicates to always be used in the queries.

The bulk of the repetitive code (tables joined appropriately) will be in the view, so that we will no longer have many different procedures and functions with their own copies of common joins and filters.

Then we will layer pipelined table functions over those views to ensure callers provide the necessary filters so that the views are not called "for all time and space". I have looked at alternatives using sys_context and userenv and package variables and although they appear to be what Oracle users call parameterized views, they simply are not viable to have those shims around a view every time it is used and they are not re-usable in self-joins.

I've read a lot about this in a variety of places, including StackOverflow:

Table-Valued Functions in ORACLE 11g ? ( parameterized views )

Database: Pipelined Functions

Is using a SELECT inside a pipelined PL/SQL table function allowed?

This is an architectural decision to try to improve the maintainability of an application which has become sprawling with a LOT of repeated queries. Views would go some way to helping, but I am worried that we have no way to enforce predicates on callers to stop them from doing silly things.

I've had a great deal of success using this technique in SQL Server with inline table-valued functions and it really helped make the system a lot more coherent and easier to track dependencies and effects of proposed changes, since there was a) less code and b) more re-use and less repetition.

I'm a little worried about that last link, it seems to imply that I might have concurrency or timing issues if I was to join one of these pipelined table functions and use it to update another table.

Please share your experiences with pipelined table functions and what I need to look out for? Also if there is a better alternative, let me know in your answer too?

Answer 1

Yes, the point-in-time behaviour of querying a table within a pipelined function is different to that of querying the table directly or via a view, so that needs to be taken into account. That said, it's not usually a problem if the pipelined function is querying an infrequently updated table. I can't think of any concurrency or timing issues though.

My main problem with providing pipelined functions for developers to use (as opposed to using views), is that they (like some views) may be easily misused. Developers may choose to join the results of one pipelined function to another, resulting in very inefficient queries that cannot take advantage of things like indexes, pushed predicates, and table constraints.

If maintainability is your main problem, then I would prefer views - they can help reduce duplicated code by defining common transformations in one place, and perhaps common joins as well; however even these are too easily misused (e.g. joining to a view, even though it joins to another table that is not required by the original query).

Performance and efficiency, will probably be the things to watch out for. Put a rigorous review regime in place for all SQL in the application to look for poorly written or inconsistent queries.