Unfortunately, this isn’t true for Oracle (yet). Even in Oracle 12c (where APPLY/LATERAL is now supported), ttable-valued functions are opaque for the optimizer, which cannot “peek inside” to apply nice SQL transformation operations to optimise such statements.

People Emulate Parameterized Views With SYS_CONTEXT

A lot of people use Oracle’s SYS_CONTEXT feature to emulate parameterized views:

@lukaseder@flederbine you can use sys_context to achieve effect of parameterized views…works particularly well w/pipeline functions

A SYS_CONTEXT is essentially a global variable (per session), which you can set e.g. when getting a connection out of your connection pool in your Java client application. From then on, all SQL statements that use SYS_CONTEXT are globally “parameterized.” For example, our previous view becomes this:

Now, of course, this view cannot be used (yet), because it will always return an empty result:

SELECT *
FROM v_categories_per_actor
ORDER BY name;

Which yields:

NAME
----

Now, let’s do that SYS_CONTEXT initialization magic:

-- Just some boilerplate you have to do once
CREATE CONTEXT my_app USING set_ctx;
CREATE OR REPLACE PROCEDURE set_ctx(
p_actor_id NUMBER := NULL
) IS
BEGIN
dbms_session.set_context('MY_APP', 'ACTOR_ID', p_actor_id);
END;
/
-- This will now set the ACTOR_ID for our session
EXEC set_ctx(1);

If we re-run our previous statement that queries the view now:

SELECT *
FROM v_categories_per_actor
ORDER BY name;

We’ll get an actual result!

NAME
-----------
Animation
Children
Classics
Comedy
Documentary
Family
Foreign
Games
Horror
Music
New
Sci-Fi
Sports

Now, of course this is nowhere near as powerful as actual parameterized views, because I cannot lateral join this thing to an actual actor table, setting that context on each row. But it already helps if you want your Java client application be able to set a parameter for such a view. Let’s call it:

Oracle’s poor man’s parameterised views

How Does SYS_CONTEXT Impact Performance?

A client of mine makes heavy use of SYS_CONTEXT for security purposes, in order to implement something similar to row-level security. I’ll show an example later on.

Note how in the WHERE clause, I always use the SYS_CONTEXT function. I never put the literal value into the query — that would be very bad for Performance Scalability Shared pool utilization and, perhaps most importantly, security (SQL injection)

I don’t really agree with this, because in my opinion, bind variables are a much better approach to this simplistic security concern.

In addition to that, since Oracle 11g, we have adaptive cursor sharing and bind variable peeking, which means that the same query can produce distinct execution plans depending on the actual bind variable values. This isn’t (currently) being done for SYS_CONTEXT. See also this very interesting article by Connor McDonald:

Connor was kind enough to write this article as a reply to a question about SYS_CONTEXT I asked on Twitter.

Row Level Security With SYS_CONTEXT

The important thing is just to remember that no peeking is done at SYS_CONTEXT values. What does this mean?

This client of mine has always used SYS_CONTEXT just like I mentioned before, for security reasons. More particularly, they implemented sophisticated row-level security with it (they did this before Oracle had out of the box support for row-level security). Again, if you look at the previous query:

You could interpret this as being a security feature. Actors can only access their own data across the entire database. In this case, if clients don’t get GRANTs to tables, but only to these views, it’s possible to prevent access to all sorts of data that is not related to “my own actor_id”, even if client code forgets to add the appropriate predicate.

The execution plan for a query against this view shows that it’s quite decent:

As you can see, the cardinality estimates are all pretty OK, and this query does the right thing, as the database knows it can use the single SYS_CONTEXT value for a fast access predicate on the FILM_ACTOR primary key.

Let’s ignore for a moment the slightly wrong cardinality estimate on PK_FILM_ACTOR, whose effects can be fixed by using a /*+LEADING(fa fc)*/ hint inside of the view. But this is not related to using SYS_CONTEXT.

Using SYS_CONTEXT to Overload View Behaviour

Now, that particular client evaluated whether SYS_CONTEXT can be used for something entirely else: To put two UNION ALL subqueries in a view and depending on the "session mode," they wanted to execute one or the other query. Let’s assume you have different types of users in your client application:

Normal customers: “C”

Operators: “O”

The latter have more privileges and can see entirely different data. In the terms of our view, this might mean the following:

What this means now is that the same view is reused for both types of users: customers/actors (C) and operators (O). Customers only get to see their own data whereas operators get to see all data. Imagine that the real query is much more complex.

So, this is really nice, because then you can start reusing complex views and put these views in other complex views and the behaviour of your entire application starts changing depending on who is logging in. So, this shouldn’t just be called row level security, it should be called access control list, because that’s what they’re really doing.

Excellent!

So where’s the problem? The problem lies in the execution plans. Let’s update our procedure again:

As you can see, the first part of the UNION-ALL concatenation is still roughly the same, except we now got this FILTER operation on operation #5. The second part of the UNION-ALL operation, however, got its cardinalities completely wrong. Operation 11 estimates 2263 rows, even if there are none.

The E-Rows column (estimated rows) estimate that all data is selected from our Sakila database, i.e. 1000 FILM_CATEGORY relationships and 5462 FILM_ACTOR relationships, which is all of our data. But the A-Rows column (actual rows) is zero, as expected, because we set our USER_TYPE context value to 'C' for customer, not 'O' for operator.

Also interesting is the Starts column, which shows that the operations below the FILTER operation on line #12 aren’t started.

Good News, No?

Yes and no.

YES: Because even if the plan looks quite bad (for customer usage), it performed optimally. At least, Oracle knew when to stop even if estimates were wrong.

NO: Because all these cardinality (and cost) estimates will propagate leading to bigger and bigger errors, depending on how you use this view.

The above query could of course be stored in a view again to fit our security concept of giving grants only to views, not tables… In any case, what we’re doing here is the following:

We take all categories per actor from our previous view, then we want to count the number of total films in that category for that actor. If we’re still running this in a customer (C) context with ACTOR_ID = 1, we’ll get:

Which translates to: I (my user = ACTOR_ID = 1) have played in these categories, and these categories have so many total films that I played in. Again, the actual query might be much more complex, where we can’t easily factor out things (e.g. avoid doubling access to various tables). I’m just trying to make a point here.

Observe how at some point, I had a cardinality estimate of 62130 (operation #4), and the whole query was still expected to return 2263 rows (operation #1), when in fact, I got only 13.

Even if the optimizer got the number of rows almost right for the first UNION ALL subquery (estimated 12, got 13 on operation #9), the estimate for the second UNION ALL subquery made it think that with so many rows coming out of the view (2263 on operation #16), a hash join will be optimal to count the number of films (operation #4 and #5). While the actual numbers aren’t as bad as it was estimated, the hash join operation is much more costly for small data sets, than an equivalent nested loop join operation.

If we remove again that UNION ALL operation from the view, restricting the view back to the original customer (C) only use case:

Clearly, that UNION ALL and its resulting hash joins are hurting us drastically!

Conclusion: Use SYS_CONTEXT With Care

SYS_CONTEXT can be useful to emulate parameterised views as we’ve seen. There’s nothing wrong about doing “extended row-level security” by adding SYS_CONTEXT predicates in views and granting access only to views, not tables, e.g.

However, due to the lack of peeking at those SYS_CONTEXT values, we cannot profit from the adaptive cursor sharing feature. This might have been possible with real parameterised views (such as they are supported in SQL Server), but in this case, we don’t get multiple alternative execution plans for the same SQL query, depending on SYS_CONTEXT values. This has been shown by Connor McDonald in his blog post.

I do hope that a future version of Oracle will treat SYS_CONTEXT more like bind variables, because ultimately, that’s what they are: Constant external values for the scope of a single query execution, or “parameters” like in “parameterized views”. Until we have that (or real parameterised views), I strongly advise against using SYS_CONTEXT for the use-case that my client was testing (cutting off individual UNION ALL subtrees from execution plans).

But the good news is: There’s nothing wrong with the ordinary use-case (forming predicates in WHERE clauses).