针对静态优化器策略导致的缓慢参数化/过滤查询的Oracle SQL策略
针对静态优化器策略导致的缓慢参数化/过滤查询的Oracle SQL策略
提问于 2014-03-13 12:44:21
比下面简单得多:如果一个或多个查询参数，例如(或报表/表函数参数)是性能关键的(例如，可以使用一些主键索引)，那么可能是(取决于应用的用例/报告过滤器，.)其中之一
null- 一个完全匹配的(例如，一些唯一的id)
- 一个类似的表达式
- 甚至是regexp表达式
然后，如果所有这些可能性都是在单个查询中编码的，我只看到并知道优化器将
- 生成一个独立于实际参数运行时值的唯一静态计划。
- 因此，不能假设在
x_id上使用某种索引，尽管它可能是某些精确匹配的
是处理这个问题的方法，而不是。
n允许一些PL/SQL代码从预定义中选择，并且用例优化查询/视图？- 一个人的参数越灵活，它就越大。
- 或者一些手动的字符串构造和动态编译的查询？
基本上，我有两个略有不同的用例/问题，如下所示：
A - select * from tf_sel
B - select * from data_union
这可能通过SQL提示或使用其他技巧来解决。
为了加快这些查询的速度，我是，目前在特定的实现级别(表函数)上分离了“合并查询”，这是非常麻烦的，而且很难维护，但是由于它们更好的执行计划，所以确保查询运行得相当快。
在我看来，的主要问题似乎是优化器sql 的静态性质，如果它考虑一些“查询时间常量”的筛选参数，那么它总是一样的，而且效率会高得多。
with
-- Question A: What would be a good strategy to make tf_sel with tf_params nearly as fast as query_use_case_1_eq
-- which actually provides the same result?
--
-- - a complex query should be used in various reports with filters
-- - we want to keep as much as possible filter functionality on the db side (not the report engine side)
-- to be able to utilize the fast and efficient db engine and for loosely coupled software design
complex_query as ( -- just some imaginable complex query with a lot of table/view joins, aggregation/analytical functions etc.
select 1 as id, 'ab12' as indexed_val, 'asdfasdf' x from dual
union all select 2, 'ab34', 'a uiop345' from dual
union all select 3, 'xy34', 'asdf 0u0duaf' from dual
union all select 4, 'xy55', ' asdja´sf asd' from dual
)
-- <<< comment the following lines in to test it with the above
-- , query_use_case_1_eq as ( -- quite fast and maybe the 95% use case
-- select * from complex_query where indexed_val = 'ab12'
-- )
--select * from query_use_case_1_eq
-- >>>
-- ID INDEXED_VAL X
-- -- ----------- --------
-- 1 ab12 asdfasdf
-- <<< comment the following lines in to test it with the above
-- , query_use_case_2_all as ( -- significantly slower due to a lot of underlying calculations
-- select * from complex_query
-- )
--select * from query_use_case_2_all
-- >>>
-- ID INDEXED_VAL X
-- -- ----------- -------------
-- 1 ab12 asdfasdf
-- 2 ab34 a uiop345
-- 3 xy34 asdf 0u0duaf
-- 4 xy55 asdja´sf asd
-- <<< comment the following lines in to test it with the above
-- , query_use_case_3_like as (
-- select * from complex_query where indexed_val like 'ab%'
-- )
--select * from query_use_case_3_like
-- >>>
-- ID INDEXED_VAL X
-- -- ----------- ---------
-- 1 ab12 asdfasdf
-- 2 ab34 a uiop345
-- <<< comment the following lines to simulate the table function
, tf_params as ( -- table function params: imagine we have a table function where these are passed depending on the report
select 'ab12' p_indexed_val, 'eq' p_filter_type from dual
)
, tf_sel as ( -- table function select: nicely integrating all query possiblities, but beeing veeery slow :-(
select q.*
from
tf_params p -- just here so this example works without the need for the actual function
join complex_query q on (1=1)
where
p_filter_type = 'all'
or (p_filter_type = 'eq' and indexed_val = p_indexed_val)
or (p_filter_type = 'like' and indexed_val like p_indexed_val)
or (p_filter_type = 'regexp' and regexp_like(indexed_val, p_indexed_val))
)
-- actually we would pass the tf_params above if it were a real table function
select * from tf_sel
-- >>>
-- ID INDEXED_VAL X
-- -- ----------- --------
-- 1 ab12 asdfasdf
-- Question B: How can we speed up data_union with dg_filter to be as fast as the data_group1 query which
-- actually provides the same result?
--
-- A very similar approach is considered in other scenarios where we like to join the results of
-- different queries (>5) returning joinable data and beeing filtered based on the same parameters.
-- <<< comment the following lines to simulate the union problem
-- , data_group1 as ( -- may run quite fast
-- select 'dg1' dg_id, q.* from complex_query q where x < 'a' -- just an example returning some special rows that should be filtered later on!
-- )
--
-- , data_group2 as ( -- may run quite fast
-- select 'dg2' dg_id, q.* from complex_query q where instr(x,'p') >= 0 -- just an example returning some special rows that should be filtered later on!
-- )
--
--
-- , dg_filter as ( -- may be set by a report or indirectly by user filters
-- select 'dg1' dg_id from dual
-- )
--
-- , data_union as ( -- runs much slower due to another execution plan
-- select * from (
-- select * from data_group1
-- union all select * from data_group2
-- )
-- where dg_id in (select dg_id from dg_filter)
-- )
--
--select * from data_union
-- >>>
-- DG_ID ID INDEXED_VAL X
-- ----- -- ----------- -------------
-- dg1 4 xy55 asdja´sf asd这是对jonearles提供的示例代码和答案的注释。
实际上，您的回答是我的(不相关的，虽然在某些场景中一起出现)用例A和B的混合，尽管您提到优化器具有动态FILTER和其他功能是很重要的。
用例B(“数据分区/组联合”)
实际上，用例B(基于您的示例表)看起来更像这样，但是我仍然需要检查实际场景中的性能问题。也许你已经可以看到一些问题了？
select * from (
select 'dg1' data_group, x.* from sample_table x
where mod(to_number(some_other_column1), 100000) = 0 -- just some example restriction
--and indexed_val = '3635' -- commenting this in and executing this standalone returns:
----------------------------------------------------------------------------------------
--| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------
--| 0 | SELECT STATEMENT | | 1 | 23 | 2 (0)|
--| 1 | TABLE ACCESS BY INDEX ROWID| SAMPLE_TABLE | 1 | 23 | 2 (0)|
--| 2 | INDEX RANGE SCAN | SAMPLE_TABLE_IDX1 | 1 | | 1 (0)|
----------------------------------------------------------------------------------------
union all
select 'dg2', x.* from sample_table x
where mod(to_number(some_other_column2), 9999) = 0 -- just some example restriction
union all
select 'dg3', x.* from sample_table x
where mod(to_number(some_other_column3), 3635) = 0 -- just some example restriction
)
where data_group in ('dg1') and indexed_val = '35'
-------------------------------------------------------------------------------------------
--| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------
--| 0 | SELECT STATEMENT | | 3 | 639 | 2 (0)|
--| 1 | VIEW | | 3 | 639 | 2 (0)|
--| 2 | UNION-ALL | | | | |
--| 3 | TABLE ACCESS BY INDEX ROWID | SAMPLE_TABLE | 1 | 23 | 2 (0)|
--| 4 | INDEX RANGE SCAN | SAMPLE_TABLE_IDX1 | 1 | | 1 (0)|
--| 5 | FILTER | | | | |
--| 6 | TABLE ACCESS BY INDEX ROWID| SAMPLE_TABLE | 1 | 23 | 2 (0)|
--| 7 | INDEX RANGE SCAN | SAMPLE_TABLE_IDX1 | 1 | | 1 (0)|
--| 8 | FILTER | | | | |
--| 9 | TABLE ACCESS BY INDEX ROWID| SAMPLE_TABLE | 1 | 23 | 2 (0)|
--| 10 | INDEX RANGE SCAN | SAMPLE_TABLE_IDX1 | 1 | | 1 (0)|
-------------------------------------------------------------------------------------------用例A(按列查询类型进行筛选)
根据你的样本表，这更像是我想做的事。正如您可以看到的那样，使用fast where p.ft_id = 'eq' and x.indexed_val = p.val where 的查询显示了索引的使用情况，但是在where子句中使用所有不同的筛选器选项将导致计划切换始终使用完整的表扫描:-/ (即使我在SQL中的任何地方都使用:p_filter_type和:p_indexed_val_filter，它不会改变)。
with
filter_type as (
select 'all' as id from dual
union all select 'eq' as id from dual
union all select 'like' as id from dual
union all select 'regexp' as id from dual
)
, params as (
select
(select * from filter_type where id = :p_filter_type) as ft_id,
:p_indexed_val_filter as val
from dual
)
select *
from params p
join sample_table x on (1=1)
-- the following with the above would show the 'eq' use case with a fast index scan (plan id 14/15)
--where p.ft_id = 'eq' and x.indexed_val = p.val
------------------------------------------------------------------------------------------
--| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------
--| 0 | SELECT STATEMENT | | 1 | 23 | 12 (0)|
--| 1 | VIEW | | 4 | 20 | 8 (0)|
--| 2 | UNION-ALL | | | | |
--| 3 | FILTER | | | | |
--| 4 | FAST DUAL | | 1 | | 2 (0)|
--| 5 | FILTER | | | | |
--| 6 | FAST DUAL | | 1 | | 2 (0)|
--| 7 | FILTER | | | | |
--| 8 | FAST DUAL | | 1 | | 2 (0)|
--| 9 | FILTER | | | | |
--| 10 | FAST DUAL | | 1 | | 2 (0)|
--| 11 | FILTER | | | | |
--| 12 | NESTED LOOPS | | 1 | 23 | 4 (0)|
--| 13 | FAST DUAL | | 1 | | 2 (0)|
--| 14 | TABLE ACCESS BY INDEX ROWID| SAMPLE_TABLE | 1 | 23 | 2 (0)|
--| 15 | INDEX RANGE SCAN | SAMPLE_TABLE_IDX1 | 1 | | 1 (0)|
--| 16 | VIEW | | 4 | 20 | 8 (0)|
--| 17 | UNION-ALL | | | | |
--| 18 | FILTER | | | | |
--| 19 | FAST DUAL | | 1 | | 2 (0)|
--| 20 | FILTER | | | | |
--| 21 | FAST DUAL | | 1 | | 2 (0)|
--| 22 | FILTER | | | | |
--| 23 | FAST DUAL | | 1 | | 2 (0)|
--| 24 | FILTER | | | | |
--| 25 | FAST DUAL | | 1 | | 2 (0)|
------------------------------------------------------------------------------------------
where
--mod(to_number(some_other_column1), 3000) = 0 and -- just some example restriction
(
p.ft_id = 'all'
or
p.ft_id = 'eq' and x.indexed_val = p.val
or
p.ft_id = 'like' and x.indexed_val like p.val
or
p.ft_id = 'regexp' and regexp_like(x.indexed_val, p.val)
)
-- with the full flexibility of the filter the plan shows a full table scan (plan id 13) :-(
--------------------------------------------------------------------------
--| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
--| 0 | SELECT STATEMENT | | 1099 | 25277 | 115 (3)|
--| 1 | VIEW | | 4 | 20 | 8 (0)|
--| 2 | UNION-ALL | | | | |
--| 3 | FILTER | | | | |
--| 4 | FAST DUAL | | 1 | | 2 (0)|
--| 5 | FILTER | | | | |
--| 6 | FAST DUAL | | 1 | | 2 (0)|
--| 7 | FILTER | | | | |
--| 8 | FAST DUAL | | 1 | | 2 (0)|
--| 9 | FILTER | | | | |
--| 10 | FAST DUAL | | 1 | | 2 (0)|
--| 11 | NESTED LOOPS | | 1099 | 25277 | 115 (3)|
--| 12 | FAST DUAL | | 1 | | 2 (0)|
--| 13 | TABLE ACCESS FULL| SAMPLE_TABLE | 1099 | 25277 | 113 (3)|
--| 14 | VIEW | | 4 | 20 | 8 (0)|
--| 15 | UNION-ALL | | | | |
--| 16 | FILTER | | | | |
--| 17 | FAST DUAL | | 1 | | 2 (0)|
--| 18 | FILTER | | | | |
--| 19 | FAST DUAL | | 1 | | 2 (0)|
--| 20 | FILTER | | | | |
--| 21 | FAST DUAL | | 1 | | 2 (0)|
--| 22 | FILTER | | | | |
--| 23 | FAST DUAL | | 1 | | 2 (0)|
--------------------------------------------------------------------------回答 2
Stack Overflow用户
回答已采纳
发布于 2014-04-04 07:08:39
(更多关于情景A)，但也适用于B) .)
我现在使用的是一些混合方法(我问题中的1点和2点的组合)，实际上非常喜欢它，因为它还提供了很好的调试和封装可能性，和优化器根本不需要处理基于更大的查询中基本上逻辑分离的查询找到最佳策略的问题，例如内部FILTER规则，这可能是好的，或者最坏的是更低的效率：
- 在报告中使用此方法 从表中选择*(my_report_data_func_sql( :val1，:val1_filter_type，:val2 ))
- 表函数的定义如下 创建或替换函数my_report_data_func_sql( p_val1整型默认值1234，p_val1_filter_type varchar2默认值'eq'，p_val2 varchar2默认null )返回varchar2是查询varchar2(4000) :=，参数为(-*)，默认param选择‘而行其是’，- eq*‘’x_x_p_val1_filter_filter‘p_val1_filter_type，- eq，所有*，比如，p_val2 -空*从dual )选择x.*从params p --为独立的解决方法-sql-使用"with“语句( 1=1 )上面的”with“语句调试，其中1=1-易于在下面添加筛选表达式‘- ####过滤器准则附在这里，#### - val1( p_val1)“否则”结束-全部；开始返回查询；结束；
- 并将通过实例产生以下内容： 从表(my_report_data_func_sql( 1234，'eq'，'someval2‘)中选择* /*，其参数为(-*)，默认参数选择1 p_val1，- eq* 'eq’p_val1_filter_type，- eq，all*，类似，regexp 'someval2‘p_val2 -- null* from dual )选择x.* from params p --解决独立的-sql-使用连接my_report_data_base_view x ( 1=1 )上面的"with“语句进行调试，1=1 --便于筛选表达式添加到下面，val1 = p_val1 */
Stack Overflow用户
发布于 2014-03-14 04:44:45
有几个特性使优化器能够生成动态计划。最常见的特性是FILTER操作，不应将其与过滤器谓词混淆。FILTER操作允许Oracle基于动态值在运行时启用或禁用部分计划。此特性通常适用于绑定变量，其他类型的动态查询可能不会使用它。
样例模式
create table sample_table
(
indexed_val varchar2(100),
some_other_column1 varchar2(100),
some_other_column2 varchar2(100),
some_other_column3 varchar2(100)
);
insert into sample_table
select level, level, level, level
from dual
connect by level <= 100000;
create index sample_table_idx1 on sample_table(indexed_val);
begin
dbms_stats.gather_table_stats(user, 'sample_table');
end;
/使用绑定变量的示例查询
explain plan for
select * from sample_table where :p_filter_type = 'all'
union all
select * from sample_table where :p_filter_type = 'eq' and indexed_val = :p_indexed_val
union all
select * from sample_table where :p_filter_type = 'like' and indexed_val like :p_indexed_val
union all
select * from sample_table where :p_filter_type = 'regexp' and regexp_like(indexed_val, :p_indexed_val);
select * from table(dbms_xplan.display(format => '-cost -bytes -rows'));样例计划
这演示了根据输入使用的截然不同的计划。单个=将使用INDEX RANGE SCAN，没有谓词将使用TABLE ACCESS FULL。正则表达式还使用完整的表扫描，因为没有方法索引正则表达式。尽管取决于表达式的确切类型，可以通过基于函数的索引或Oracle文本索引启用有用的索引。
Plan hash value: 100704550
------------------------------------------------------------------------------
| Id | Operation | Name | Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 00:00:01 |
| 1 | UNION-ALL | | |
|* 2 | FILTER | | |
| 3 | TABLE ACCESS FULL | SAMPLE_TABLE | 00:00:01 |
|* 4 | FILTER | | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| SAMPLE_TABLE | 00:00:01 |
|* 6 | INDEX RANGE SCAN | SAMPLE_TABLE_IDX1 | 00:00:01 |
|* 7 | FILTER | | |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| SAMPLE_TABLE | 00:00:01 |
|* 9 | INDEX RANGE SCAN | SAMPLE_TABLE_IDX1 | 00:00:01 |
|* 10 | FILTER | | |
|* 11 | TABLE ACCESS FULL | SAMPLE_TABLE | 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:P_FILTER_TYPE='all')
4 - filter(:P_FILTER_TYPE='eq')
6 - access("INDEXED_VAL"=:P_INDEXED_VAL)
7 - filter(:P_FILTER_TYPE='like')
9 - access("INDEXED_VAL" LIKE :P_INDEXED_VAL)
filter("INDEXED_VAL" LIKE :P_INDEXED_VAL)
10 - filter(:P_FILTER_TYPE='regexp')
11 - filter( REGEXP_LIKE ("INDEXED_VAL",:P_INDEXED_VAL))页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/22379074
复制相关文章
相似问题
腾讯云开发者
