Skip navigation

CBO should peek at SYS_CONTEXT values just like bind values

score 180
You have not voted. Active

I have no idea about the complexity of a possible implementation, just hinting at the idea that this might be possible:

 

Since 11g and adaptive cursor sharing, the CBO peeks at bind variables and might decide to use a different execution plan depending on the input. This could also be done for SYS_CONTEXT values that are embedded in queries, which would help write complicated stuff like this:

 

CREATE OR REPLACE VIEW v_categories_per_actor AS

 

SELECT DISTINCT actor_id, c.name

FROM category c

JOIN film_category fc USING (category_id)

JOIN film_actor fa USING (film_id)

WHERE fa.actor_id = TO_NUMBER(sys_context('MY_APP', 'ACTOR_ID'))

AND sys_context('MY_APP', 'USER_TYPE') = 'C'

 

UNION ALL

 

SELECT DISTINCT actor_id, c.name

FROM category c

JOIN film_category fc USING (category_id)

JOIN film_actor fa USING (film_id)

WHERE sys_context('MY_APP', 'USER_TYPE') = 'O'

 

Imagine this view uses SYS_CONTEXT to distinguish between session / user types, e.g. customers (C) who can see only parts of the data and operators (O) who can see all the data. Now, this view can be embedded in other views to form very complex access control lists. In situations like these, peeking at the actual SYS_CONTEXT could greatly help because in those cases, the execution plan could completely avoid one of the two UNION ALL subqueries, rather than keeping it in place and costing it, because those wrong costs and cardinalities will propagate to every upstream view that uses this one.

Comments

Vote history