问题描述
如果我们使用Oracle行级安全性(RLS)隐藏一些记录-是否有任何性能影响-会减慢我的SQL查询速度吗?为此的Oracle软件包是:DBMS_RLS.
If we use Oracle Row Level Security(RLS) to hide some records - Are there any Performance Implications - will it slow down my SQL Queries? The Oracle Package for this is: DBMS_RLS.
我计划在某些表中添加:IS_HISTORICAL = T/F.然后使用RLS隐藏具有IS_HISTORICAL = T值的记录.
I plan to add: IS_HISTORICAL=T/F to some tables. And then using RLS, hide the records which have value of IS_HISTORICAL=T.
我们在应用程序中使用的SQL查询非常复杂，具有内部/外部联接，子查询，相关子查询等.
The SQL Queries we use in application are quite complex, with inner/outer joins, subqueries, correlated subqueries etc.
在200个奇数表中，约有50个将应用此RLS策略(以IS_HISTORICAL = T隐藏记录). 150个表中的其余表是这50个表中的子表，因此RLS隐含在它们上.
Of the 200 odd tables, about 50 of them will have this RLS Policy (to hide records by IS_HISTORICAL=T) applied on them. Rest of the 150 tables are child tables of these 50 Tables, so RLS is implicit on them.
是否涉及许可证?
谢谢.
推荐答案
与所有与性能有关的问题一样，答案是取决于". RLS的工作方式是将受控查询包装在外部查询中，该外部查询将策略功能作为WHERE子句应用...
As with all questions relating to performance the answer is, "it depends". RLS works by wrapping the controlled query in an outer query which applies the policy function as a WHERE clause...
select /*+ rls query */ * from (
select /*+ your query */ ... from t23
where whatever = 42 )
where rls_policy.function_t23 = 'true'
因此，性能影响完全取决于函数中的内容.
So the performance implications rest entirely on what goes in the function.
完成这些操作的通常方法是使用上下文名称空间.这些是通过SYS_CONTEXT()函数访问的会话内存的预定义区域.因此，从上下文中检索存储值的成本可以忽略不计.就像我们通常每个会话填充一次名称空间一样(例如通过登录后触发器或类似的连接挂钩)，每个查询的总成本是微不足道的.有多种刷新命名空间的方法，这些方法可能会对性能产生影响，但这些方法在总体方案中都是微不足道的(查看其他答案).
The normal way of doing these things is to use context namespaces. These are predefined areas of session memory accessed through the SYS_CONTEXT() function. As such the cost of retrieving a stored value from a context is negligible. And as we would normally populate the namespaces once per session - say by an after logon trigger or a similar connection hook - the overall cost per query is trivial. There are different ways of refreshing the namespace which might have performance implications but again these are trivial in the overall schem of things (see this other answer).
因此，性能影响取决于您的函数实际执行的操作.这使我们考虑了您的实际政策:
So the performance impact depends on what your function actually does. Which brings us to a consideration of your actual policy:
好消息是该功能的执行本身不太可能会花费很多.坏消息是性能可能仍然是Teh Suck！无论如何，如果实时记录与历史记录的比率不理想.您可能最终将检索所有记录，然后过滤掉历史记录.优化器可能会将RLS谓词推入主查询中，但由于RLS的工作方式，我认为这不太可能:它避免将策略的标准向一般视线透露(这使调试RLS操作成为真正的PITN).
The good news is the execution of such a function is unlikely to be costly in itself. The bad news is the performance may still be Teh Suck! anyway, if the ratio of live records to historical records is unfavourable. You will probably end up retrieving all the records and then filtering out the historical ones. The optimizer might push the RLS predicate into the main query but I think it's unlikely because of the way RLS works: it avoids revealing the criteria of the policy to the general gaze (which makes debugging RLS operations a real PITN).
您的用户将为您糟糕的设计决策付出代价.最好使用日记表或历史记录表来存储旧记录，并仅将实时数据保留在实际表中.与现场记录一起保留历史记录很少能扩展规模.
Your users will pay the price of your poor design decision. It is much better to have journalling or history tables to store old records and keep only live data in the real tables. Retaining historical records alongside live ones is rarely a solution which scales.
DBMS_RLS需要企业版许可证.
DBMS_RLS requires an Enterprise Edition license.
这篇关于使用(DBMS_RLS)Oracle行级安全性(RLS)对性能有何影响?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！