Стратегия Oracle SQL для медленных параметризованных / отфильтрованных запросов благодаря стратегии статического оптимизатора
Проще говоря, чем ниже: если у вас есть один или несколько параметров запроса, например x_id, (или параметры функции отчета / табличной функции), которые имеют решающее значение для производительности (например, может использоваться некоторый индекс первичного ключа), и он может быть (в зависимости от примененного варианта использования / фильтров отчета, ...) одним из
null- точное совпадение (например, некоторый уникальный идентификатор)
- похожее выражение
- или даже выражение регулярного выражения
тогда, если все эти возможности закодированы в одном запросе, я только вижу и знаю, что оптимизатор будет
- генерировать уникальный статический план, не зависящий от фактического значения времени выполнения параметра
- и, следовательно, не может использовать какой-либо индекс на
x_idхотя это может быть, например, какое-то точное совпадение
Есть ли другие способы справиться с этим, чем
- пусть какой-нибудь PL/SQL-код выбирается из
nпредопределенные и оптимизированные для случая запросы / представления?- который может быть довольно большим, тем больше таких гибких параметров
- или какой-то вручную построенный на строку и динамически скомпилированный запрос?
По сути, у меня есть два слегка отличающихся варианта использования / вопроса, которые описаны и описаны ниже:
А - select * from tf_sel
Б - 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 (фильтрация по типу запроса столбца)
Судя по вашему образцу таблицы, это больше похоже на то, что я хочу сделать. Как вы можете видеть запрос только с быстрым where p.ft_id = 'eq' and x.indexed_val = p.val показывает использование индекса, но с различными параметрами фильтра в where Предложение заставит переключатель плана всегда использовать полное сканирование таблицы: - / (Даже если я использую :p_filter_type а также :p_indexed_val_filter везде в SQL, чем просто в одном месте, я его не изменю.)
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 ответа
(больше для ситуации A), но также применимо к B) таким образом...)
Сейчас я использую некоторый гибридный подход (комбинация пунктов 1. и 2. в моем вопросе) и на самом деле он мне очень нравится, потому что он также предоставляет хорошие возможности отладки и инкапсуляции, и оптимизатору совсем не приходится сталкиваться с поиском лучшего стратегия, основанная на логически разделенных запросах в большем запросе, например, на внутреннем FILTER правила, которые могут быть хорошими или в худшем случае невероятно более неэффективными:
используя это в отчете
select * from table(my_report_data_func_sql( :val1, :val1_filter_type, :val2 ))где табличная функция определяется следующим образом
create or replace function my_report_data_func_sql( p_val1 integer default 1234, p_val1_filter_type varchar2 default 'eq', p_val2 varchar2 default null ) return varchar2 is query varchar2(4000) := ' with params as ( -- *: default param select ''||p_val1||'' p_val1, -- eq* '''||p_val1_filter_type||''' p_val1_filter_type, -- [eq, all*, like, regexp] '''||p_val2||''' p_val2 -- null* from dual ) select x.* from params p -- workaround for standalone-sql-debugging using "with" statement above join my_report_data_base_view x on (1=1) where 1=1 -- ease of filter expression adding below ' -- #### FILTER CRITERIAS are appended here #### -- val1-filter ||case p_val1_filter_type when 'eq' then ' and val1 = p_val1 ' when 'like' then ' and val1 like p_val1 ' when 'regexp' then ' and regexp_like(val1, p_val1) ' else '' end -- all ; begin return query; end; ;и будет производить следующее в качестве примера:
select * from table(my_report_data_func_sql( 1234, 'eq', 'someval2' )) /* with params as ( -- *: default param select 1 p_val1, -- eq* 'eq' p_val1_filter_type, -- [eq, all*, like, regexp] 'someval2' p_val2 -- null* from dual ) select x.* from params p -- workaround for standalone-sql-debugging using "with" statement above join my_report_data_base_view x on (1=1) where 1=1 -- ease of filter expression adding below and val1 = p_val1 */
Несколько функций позволяют оптимизатору создавать динамические планы. Наиболее распространенная особенность 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 Text.
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))