I recently applied RLS to several large (several million rows) tables in my 9.5 database and noticed that queries
againsta single large RLS protected table perform well however queries that join several large RLS protected tables
performvery poorly. The explain plan shows the optimizer is scanning the entire table to enforce the RLS policy before
executingthe primary key join that would reduce the query results to a single row from each table. Clearly performance
wouldbe better if it performed the join before the policy check.
From what I can understand the RLS implementation strives to execute policy checks before user provided predicate
checksso as to avoid leaking protected data. Is there any way to make the join look "safe" to the optimizer to avoid
fulltable scans? Isn't this a common scenario?
Thanks,
Dave