Oracle mechanics

08.07.2008

Conditional SQL: OR, DECODE, CASE

Filed under: CBO,heuristics — Игорь Усольцев @ 18:44
Tags: , ,

Вопрос об использовании в SQL запросах конструкций вида:

where (:v1 = 1 OR table1.company_id = :v1) and ...

Если переменная :v1 = 1, показать всё, иначе — только часть. Сама по себе конструкция логически корректна, но порождает FULL SCAN’ы и может значительно замедлять время выполнения запросов.

Итак, тестовая среда:

SQL> create table t1 as select * from dba_objects where object_id <= 10000;
SQL> create index t1_idx on t1(object_id);
SQL> begin dbms_stats.gather_table_stats(
ownname => SYS_CONTEXT('USERENV', 'SESSION_USER'),
tabname => 'T1');
end;

SQL> exec :v1 := 3614;
SQL> SELECT T1.OBJECT_NAME FROM t1 WHERE (:v1 = 1 OR t1.object_id = :v1);

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    40 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |    98 |  2254 |    40   (0)| 00:00:01 |
--------------------------------------------------------------------------
   1 - filter((:V1=1 OR "T1"."OBJECT_ID"=:V1))

CBO выбирает «беспроигрышный» универсальный план ( несмотря на используемое значение :v1 <> 1) — пока ничего интересного (хотя было бы правильнее выбирать план в зависимости от значения :v1). Пробуем модифицировать условие на эквивалентное (учитывая, что поле object_id is not NULL, результат не изменится) с использованием функции DECODE:

SQL> SELECT T1.OBJECT_NAME FROM t1
WHERE decode(:v1, 1, t1.object_id, :v1) = t1.object_id;
----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |       |       |    42 (100)|          |
|   1 |  CONCATENATION                |        |       |       |            |          |
|*  2 |   FILTER                      |        |       |       |            |          |
|*  3 |    TABLE ACCESS FULL          | T1     |  9717 |   218K|    40   (0)| 00:00:01 |
|*  4 |   FILTER                      |        |       |       |            |          |
|   5 |    TABLE ACCESS BY INDEX ROWID| T1     |     1 |    23 |     2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | T1_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
   2 - filter(SYS_OP_MAP_NONNULL(:V1)=HEXTORAW('C10200') )
   3 - filter("T1"."OBJECT_ID" IS NOT NULL)
   4 - filter(SYS_OP_MAP_NONNULL(:V1)<>HEXTORAW('C10200') )
   6 - access("T1"."OBJECT_ID"=:V1)

Искусственный интеллект CBO в действии: в зависимости от значения :v1 с помощью фильтров в строках 2 и 4 выбирается соответствующий план:

либо TABLE ACCESS FULL при :v1 = 1

SQL> exec :v1 := 1;
SQL> /
...
776  consistent gets
...

либо INDEX RANGE SCAN при :v1 <> 1

SQL> exec :v1 := 3614;
...
4  consistent gets
...

Для «чистоты эксперимента» можно попробовать поменять DECODE на логически эквивалентное условие с использованием функции CASE. Получаем тот же результат (план выполнения не меняется в зависимости от значения :v1), что и при использовании начальной конструкции с OR:

SQL> exec :v1 := 3614;
SQL> SELECT T1.OBJECT_NAME FROM t1 WHERE (case when :v1 = 1 then t1.object_id else :v1 end) = t1.object_id;
8	--------------------------------------------------------------------------
9	| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
10	--------------------------------------------------------------------------
11	|   0 | SELECT STATEMENT  |      |       |       |    40 (100)|          |
12	|*  1 |  TABLE ACCESS FULL| T1   |     1 |    23 |    40   (0)| 00:00:01 |
13	--------------------------------------------------------------------------
18	   1 - filter("T1"."OBJECT_ID"=CASE :V1 WHEN 1 THEN "T1"."OBJECT_ID"
19	              ELSE :V1 END )
...
        134  consistent gets
...
SQL> exec :v1 := 1;
SQL> /
...
        776  consistent gets

Получается, что поведение Oracle CBO зависит от функций, используемых при формулировании условий. Так, наиболее «дружественной» к CBO является функция DECODE (и NVL — см.блог J. Lewis’s Conditional SQL), при использовании которых CBO использует операцию объединения (выбора) планов выполнения — CONCATENATION. А конструкции CASE и OR в этом примере порождают простые, но неэффективные планы.

Примеры проверялись на версиях Oracle 10.2.0.4 и 11.1.0.6.

P.S. В вышеупомянутом блоге Дж.Льюиса есть забавные термины — screen painters и screen generators :)

P.P.S. Конструкции CASE и OR при другой формулировке условий могут порождать plan CONCATENATION

Добавить комментарий »

Комментариев нет.

RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Создайте бесплатный сайт или блог на WordPress.com.

%d такие блоггеры, как: