Oracle Database Discussions

Consider this example.

Create table Account (account_id number primary key, account_name varchar2(200));

Create table Emp (emp_id number primary key, name varchar2(200));

Create table Account_Team (account_id number references account(account_id), emp_id number references emp(emp_id), primary key (account_id, emp_id))
organization INDEX;


A vpd policy is put in place on the account table, it is adding the predicate of
" account_id in (select account_id from account_team where emp_id = sys_context('employee_info','emp_id')) "

Where the context variables are set for every user when they login.
This works ok, (I inherited all this btw) but it's un-correlated to the outer table. (Account)
Where the problem comes in is when those pesky users come along and I have
users that have 300K different accounts they are members of a team of.

I have a datamodel/business application change in the works to help with all that, however my question is this.


Is there a way to correlate the outer table that a vpd policy is defined upon, to a subquery.
I have no assurance on the alias of the outer table, and there could be cases of more then one instance of the account table in the query (rare but could happen).

As a sql that looks like: select ... from account A where exists (select 1 from account_team at where at.account_id = a.account_id and at.emp_id = ...)
runs way better then what I get with this vpd policy which is just the emp_id being looked at.

As I said logically I'm thinking there isn't a way. But was wondering it there was some secret sauce that let you do this. :)