Проблема с переменными связывания Oracle, неправильно использующими индекс
В моем сценарии следующий запрос выполняется быстро (0,5 секунды для таблицы с 70 миллионами строк):
select * from Purchases
where (purchase_id = 1700656396)
и он даже работает быстро, используя переменные связывания:
var purchase_id number := 1700656396
select * from Purchases
where (purchase_id = :purchase_id)
Они работают быстро, потому что у меня есть индекс на purchase_id колонка. (Продолжай читать...)
Мне нужно создать запрос, который позволяет "фильтровать" по произвольным столбцам. Это означает предоставление нескольких входных переменных и фильтрацию по каждой из них, если это не null, Это работает хорошо на первых порах.
Например, следующий запрос также выполняется быстро (0,5 секунды):
select * from Purchases
where (1700656396 IS NULL OR purchase_id = 1700656396)
and (NULL IS NULL OR purchase_name = NULL)
and (NULL IS NULL OR purchase_price = NULL)
Но когда я пытаюсь параметризовать запрос с помощью переменных связывания или хранимой процедуры, запрос резко замедляется (1,5 минуты), как будто он игнорирует любые индексы:
var purchase_id number := 1700656396
var purchase_name varchar2 := NULL
var purchase_price number := NULL
select * from Purchases
where (:purchase_id IS NULL OR purchase_id = :purchase_id)
and (:purchase_name IS NULL OR purchase_name = :purchase_name)
and (:purchase_price IS NULL OR purchase_price = :purchase_price)
Прямо сейчас, в моем приложении, я вынужден динамически создавать свой запрос во время выполнения, чтобы получить достойную производительность. Это означает, что я теряю все преимущества параметризованных запросов и заставляет меня беспокоиться о внедрении SQL.
Можно ли избежать динамически сконструированных запросов, сохраняя при этом ту же логику?
4 ответа
На самом деле это большая тема, но я думаю, что такой подход проще всего реализовать и работает хорошо. Хитрость заключается в том, чтобы использовать динамический SQL, но реализовать его таким образом, чтобы вы всегда передавали одинаковое количество параметров (необходимо), И позволяли Oracle закорачивать, когда у вас нет значения для параметра (чего вам не хватает в ваш текущий подход). Например:
set serveroutput on
create or replace procedure test_param(p1 in number default null, p2 in varchar2 default null) as
l_sql varchar2(4000);
l_cur sys_refcursor;
l_rec my_table%rowtype;
l_ctr number := 0;
begin
l_sql := 'select * from my_table where 1=1';
if (p1 is not null) then
l_sql := l_sql || ' and my_num_col = :p1';
else
-- short circuit for optimizer (1=1)
l_sql := l_sql || ' and (1=1 or :p1 is null)';
end if;
if (p2 is not null) then
l_sql := l_sql || ' and name like :p2';
else
-- short circuit for optimizer (1=1)
l_sql := l_sql || ' and (1=1 or :p2 is null)';
end if;
-- show what the SQL query will be
dbms_output.put_line(l_sql);
-- note always have same param list (using)
open l_cur for l_sql using p1,p2;
-- could return this cursor (function), or simply print out first 10 rows here for testing
loop
l_ctr := l_ctr + 1;
fetch l_cur
into l_rec;
exit when l_cur%notfound OR l_ctr > 10;
dbms_output.put_line('Name is: ' || l_rec.name || ', Address is: ' || l_rec.address1);
end loop;
close l_cur;
end;
Чтобы проверить, просто запустите его. Например:
set serveroutput on
-- using 0 param
exec test_param();
-- using 1 param
exec test_param(123456789);
-- using 2 params
exec test_param(123456789, 'ABC%');
В моей системе таблица содержит более 100 мм строк с индексом в поле чисел и имени. Возвращается почти мгновенно. Также обратите внимание, что вы, возможно, не захотите делать select *, если вам не нужны все столбцы, но я немного ленив и использую%rowtype для этого примера.
надеюсь, это поможет
Как ни странно, в этом конкретном случае могут помочь два комбинированных перекрестных соединения.
Посмотрите на приведенный ниже пример.
Пример таблицы данных:
select * from all_tables;
drop table Purchases;
create table Purchases as
select zx.object_id + (lev-1) * 100000 purchase_id,
object_name purchase_name,
round( dbms_random.value( 1, 200 )) purchase_price,
zx.*
from all_objects zx
cross join (select level lev from dual connect by level <= 170);
create unique index purchases_id_ix on Purchases( Purchase_id );
exec dbms_stats.gather_table_stats( user, 'Purchases' );
select count(*) from Purchases;
COUNT(*)
----------
10316620
Запрос:
var Purchase_id varchar2( 4000 )
var Purchase_name varchar2( 4000 )
var Purchase_price varchar2( 4000 )
begin
:Purchase_id := '1139';
:Purchase_name := NULL;
:Purchase_price := NULL;
end;
/
explain plan for
select p.*
from Purchases p
cross join (
select 1 from dual d
where :Purchase_id is not null
) part_1
where Purchase_id = to_number( :Purchase_id )
and ( :Purchase_name is null or Purchase_name = :Purchase_name )
and ( :Purchase_price is null or purchase_price = to_number( :Purchase_price ) )
union all
select p.*
from Purchases p
cross join (
select 1 from dual d
where :Purchase_id is null
) part_2
where
( :Purchase_name is null or Purchase_name = :Purchase_name )
and ( :Purchase_price is null or purchase_price = to_number( :Purchase_price ) )
;
План объяснения:
Plan hash value: 460094106
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28259 | 5546K| 54093 (1)| 00:10:50 |
| 1 | NESTED LOOPS | | 28259 | 5546K| 54093 (1)| 00:10:50 |
| 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 3 | VIEW | VW_JF_SET$96C1679A | 28259 | 5546K| 54091 (1)| 00:10:50 |
| 4 | UNION-ALL | | | | | |
|* 5 | FILTER | | | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| PURCHASES | 1 | 132 | 3 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PURCHASES_ID_IX | 1 | | 2 (0)| 00:00:01 |
|* 8 | FILTER | | | | | |
|* 9 | TABLE ACCESS FULL | PURCHASES | 28258 | 3642K| 54088 (1)| 00:10:50 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(:PURCHASE_ID IS NOT NULL)
6 - filter((:PURCHASE_NAME IS NULL OR "P"."PURCHASE_NAME"=:PURCHASE_NAME) AND
(:PURCHASE_PRICE IS NULL OR "P"."PURCHASE_PRICE"=TO_NUMBER(:PURCHASE_PRICE)))
7 - access("P"."PURCHASE_ID"=TO_NUMBER(:PURCHASE_ID))
8 - filter(:PURCHASE_ID IS NULL)
9 - filter((:PURCHASE_NAME IS NULL OR "P"."PURCHASE_NAME"=:PURCHASE_NAME) AND
(:PURCHASE_PRICE IS NULL OR "P"."PURCHASE_PRICE"=TO_NUMBER(:PURCHASE_PRICE)))
27 wierszy zosta│o wybranych.
Тест для:Purchase_id <> NULL
SQL> set pagesize 0
SQL> set linesize 200
SQL> set timing on
SQL> set autotrace traceonly
SQL>
SQL> begin
2 :Purchase_id := '163027';
3 :Purchase_name := NULL;
4 :Purchase_price := NULL;
5 end;
6 /
Procedura PL/SQL zosta│a zako˝czona pomyťlnie.
Ca│kowity: 00:00:00.00
SQL> select p.*
2 from Purchases p
3 cross join (
4 select 1 from dual d
5 where :Purchase_id is not null
6 ) part_1
7 where Purchase_id = to_number( :Purchase_id )
8 and ( :Purchase_name is null or Purchase_name = :Purchase_name )
9 and ( :Purchase_price is null or purchase_price = to_number( :Purchase_price ) )
10 union all
11 select p.*
12 from Purchases p
13 cross join (
14 select 1 from dual d
15 where :Purchase_id is null
16 ) part_2
17 where
18 ( :Purchase_name is null or Purchase_name = :Purchase_name )
19 and ( :Purchase_price is null or purchase_price = to_number( :Purchase_price ) )
20 ;
Ca│kowity: 00:00:00.09
Plan wykonywania
----------------------------------------------------------
Plan hash value: 460094106
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28259 | 5546K| 54093 (1)| 00:10:50 |
| 1 | NESTED LOOPS | | 28259 | 5546K| 54093 (1)| 00:10:50 |
| 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 3 | VIEW | VW_JF_SET$96C1679A | 28259 | 5546K| 54091 (1)| 00:10:50 |
| 4 | UNION-ALL | | | | | |
|* 5 | FILTER | | | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| PURCHASES | 1 | 132 | 3 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PURCHASES_ID_IX | 1 | | 2 (0)| 00:00:01 |
|* 8 | FILTER | | | | | |
|* 9 | TABLE ACCESS FULL | PURCHASES | 28258 | 3642K| 54088 (1)| 00:10:50 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(:PURCHASE_ID IS NOT NULL)
6 - filter((:PURCHASE_NAME IS NULL OR "P"."PURCHASE_NAME"=:PURCHASE_NAME) AND
(:PURCHASE_PRICE IS NULL OR "P"."PURCHASE_PRICE"=TO_NUMBER(:PURCHASE_PRICE)))
7 - access("P"."PURCHASE_ID"=TO_NUMBER(:PURCHASE_ID))
8 - filter(:PURCHASE_ID IS NULL)
9 - filter((:PURCHASE_NAME IS NULL OR "P"."PURCHASE_NAME"=:PURCHASE_NAME) AND
(:PURCHASE_PRICE IS NULL OR "P"."PURCHASE_PRICE"=TO_NUMBER(:PURCHASE_PRICE)))
Statystyki
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
2 physical reads
0 redo size
1865 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Тест для:Purchase_id = NULL
SQL> begin
2 :Purchase_id := NULL;
3 :Purchase_name := 'DBMS_CUBE_UTIL';
4 :Purchase_price := NULL;
5 end;
6 /
Procedura PL/SQL zosta│a zako˝czona pomyťlnie.
Ca│kowity: 00:00:00.00
SQL> select p.*
2 from Purchases p
3 cross join (
4 select 1 from dual d
5 where :Purchase_id is not null
6 ) part_1
7 where Purchase_id = to_number( :Purchase_id )
8 and ( :Purchase_name is null or Purchase_name = :Purchase_name )
9 and ( :Purchase_price is null or purchase_price = to_number( :Purchase_price ) )
10 union all
11 select p.*
12 from Purchases p
13 cross join (
14 select 1 from dual d
15 where :Purchase_id is null
16 ) part_2
17 where
18 ( :Purchase_name is null or Purchase_name = :Purchase_name )
19 and ( :Purchase_price is null or purchase_price = to_number( :Purchase_price ) )
20 ;
510 wierszy zosta│o wybranych.
Ca│kowity: 00:00:11.90
Plan wykonywania
----------------------------------------------------------
Plan hash value: 460094106
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28259 | 5546K| 54093 (1)| 00:10:50 |
| 1 | NESTED LOOPS | | 28259 | 5546K| 54093 (1)| 00:10:50 |
| 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 3 | VIEW | VW_JF_SET$96C1679A | 28259 | 5546K| 54091 (1)| 00:10:50 |
| 4 | UNION-ALL | | | | | |
|* 5 | FILTER | | | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| PURCHASES | 1 | 132 | 3 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PURCHASES_ID_IX | 1 | | 2 (0)| 00:00:01 |
|* 8 | FILTER | | | | | |
|* 9 | TABLE ACCESS FULL | PURCHASES | 28258 | 3642K| 54088 (1)| 00:10:50 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(:PURCHASE_ID IS NOT NULL)
6 - filter((:PURCHASE_NAME IS NULL OR "P"."PURCHASE_NAME"=:PURCHASE_NAME) AND
(:PURCHASE_PRICE IS NULL OR "P"."PURCHASE_PRICE"=TO_NUMBER(:PURCHASE_PRICE)))
7 - access("P"."PURCHASE_ID"=TO_NUMBER(:PURCHASE_ID))
8 - filter(:PURCHASE_ID IS NULL)
9 - filter((:PURCHASE_NAME IS NULL OR "P"."PURCHASE_NAME"=:PURCHASE_NAME) AND
(:PURCHASE_PRICE IS NULL OR "P"."PURCHASE_PRICE"=TO_NUMBER(:PURCHASE_PRICE)))
Statystyki
----------------------------------------------------------
0 recursive calls
0 db block gets
197993 consistent gets
82655 physical reads
0 redo size
16506 bytes sent via SQL*Net to client
882 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
510 rows processed
Чтобы узнать реальное время исполнения, не смотрите на планы, они лгут, содержат только оценки (как оракул думает, что это будет). Посмотрите на строки с "Ca│kowity", это означает "Общее время выполнения" (я не знаю, как изменить кодовую страницу на английский в sqlplus). Также обратите внимание на "непротиворечивое получение", это количество логических непротиворечивых блоков, которые читает запрос.
Первый запрос (purchase_id <> null)
Ca│kowity: 00:00:00.09
4 consistent gets
2 physical reads
очевидно, он использует индекс, время 90 мс
Второй запрос (purchase_id = null)
Ca│kowity: 00:00:11.90
197993 consistent gets
82655 physical reads
этот запрос выполняет полное сканирование таблицы.
Принимая другой подход к tboneВ ответ я понял, что могу динамически создавать запрос в коде и по-прежнему использовать переменные связывания (и, таким образом, получить гибкость с помощью индексов и при этом быть на 100% защищенным от внедрения SQL).
В моем коде я могу сделать что-то вроде этого:
string sql = "select * from Purchases where 1 = 1";
if(purchase_id != null) sql += " and (purchase_id = :purchase_id)";
if(purchase_name != null) sql += " and (purchase_name = :purchase_name)";
if(purchase_price != null) sql += " and (purchase_price = :purchase_price)";
Я проверил это, и это решает мою проблему.
Просто быстрый вопрос: я думаю, что следующий непараметрический запрос также будет работать в течение 1,5 минут?
select * from Purchases
where (1700656396 IS NULL OR purchase_id = 1700656396)
and ('some-name' IS NULL OR purchase_name = 'some-name')
and (12 IS NULL OR purchase_price = 12)
Если да, то проблема не в переменных связывания, а в отсутствии индексов.
РЕДАКТИРОВАТЬ Проблема заключается в том, что Oracle не может решить использовать индекс при создании плана для параметризованного запроса