Oracle mechanics

29.08.2012

Операция filter(NULL IS NOT NULL) и запросы со связанными переменными

Filed under: bind variables,heuristics,Oracle,SQL — Игорь Усольцев @ 23:31
Tags: ,

При выполнении запроса с NULL-евыми значениями связанных переменных условия типа COLUMN1 = :VAR1 превращаются в COLUMN1 = NULL и по определению становятся невыполнимыми (в том смысле, что запрос с таким условием в WHERE не возвращает строк), но при построении плана / выполнении запроса оптимизатор не всегда использует эту возможность сэкономить ресурсы

Например, при выполнении практическоого запроса (судя по тексту, выполняющему проверку на совпадение введённого в поле формы текста с ID либо логином клиента) с пустым значением переменной:

11.2.0.3.@ SQL> var P25_CLIENTX char;
SQL> exec :P25_CLIENTX := null;

PL/SQL procedure successfully completed.

SQL> select count(*)
  2    from dual
  3   where exists (SELECT id
  4            FROM (SELECT c.id, c.name, a.login
  5                    FROM T_CLIENT c, T_ACCOUNT a
  6                   WHERE c.id = a.CLIENT_ID(+))
  7           WHERE TO_CHAR(ID) = :P25_CLIENTX
  8              OR LOGIN = :P25_CLIENTX)
  9  /

Elapsed: 00:00:03.22

------------------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |             |     1 |       |       |  7208   (1)| 00:01:41 |
|   1 |  SORT AGGREGATE          |             |     1 |       |       |            |          |
|*  2 |   FILTER                 |             |       |       |       |            |          |
|   3 |    FAST DUAL             |             |     1 |       |       |     2   (0)| 00:00:01 |
|*  4 |    FILTER                |             |       |       |       |            |          |
|*  5 |     HASH JOIN OUTER      |             |     2 |    50 |    32M|  7206   (1)| 00:01:41 |
|   6 |      INDEX FULL SCAN     | T_CLIENT_PK |  1891K|    10M|       |   221   (1)| 00:00:04 |
|*  7 |      MAT_VIEW ACCESS FULL| T_ACCOUNT   |  1531K|    27M|       |  3639   (1)| 00:00:51 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter( EXISTS (SELECT 0 FROM "T_ACCOUNT" "A","T_CLIENT" "C" WHERE
              "C"."ID"="A"."CLIENT_ID"(+) AND (TO_CHAR("C"."ID")=:P25_CLIENTX OR
              "A"."LOGIN"=:P25_CLIENTX) AND "A"."CLIENT_ID"(+) IS NOT NULL))
   4 - filter(TO_CHAR("C"."ID")=:P25_CLIENTX OR "A"."LOGIN"=:P25_CLIENTX)
   5 - access("C"."ID"="A"."CLIENT_ID"(+))
   7 - filter("A"."CLIENT_ID"(+) IS NOT NULL)

Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
      13828  consistent gets -- куча GETS
      11617  physical reads  -- и даже дисковых чтений

— запрос выполняется так же, как и для не NULL-евых значений переменных, с тем же значительным количеством излишних в этом случае чтений блоков бд

Тот же запрос без использования связанных переменных отрабатывает намного эффективнее:

SQL> select count(*)
  2    from dual
  3   where exists (SELECT id
  4            FROM (SELECT c.id, c.name, a.login
  5                    FROM T_CLIENT c, T_ACCOUNT a
  6                   WHERE c.id = a.CLIENT_ID(+))
  7           WHERE TO_CHAR(ID) = ''
  8              OR LOGIN = '')
  9  /

Elapsed: 00:00:00.13

---------------------------------------------------------------------------------------------
| Id  | Operation             | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                     |     1 |       |     2   (0)| 00:00:01 | -- Cost=2
|   1 |  SORT AGGREGATE       |                     |     1 |       |            |          |
|*  2 |   FILTER              |                     |       |       |            |          |
|   3 |    FAST DUAL          |                     |     1 |       |     2   (0)| 00:00:01 |
|*  4 |    FILTER             |                     |       |       |            |          |
|   5 |     NESTED LOOPS OUTER|                     |  1891K|    21M|   232   (5)| 00:00:04 | - Cost=232
|   6 |      INDEX FULL SCAN  | T_CLIENT_PK         |  1891K|    10M|   221   (1)| 00:00:04 |
|*  7 |      INDEX RANGE SCAN | T_ACCOUNT_CLIENT_ID |     1 |     6 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter( EXISTS (SELECT 0 FROM "T_ACCOUNT" "A","T_CLIENT" "C" WHERE
              NULL IS NOT NULL AND "C"."ID"="A"."CLIENT_ID"(+) AND "A"."CLIENT_ID"(+) IS NOT NULL))
   4 - filter(NULL IS NOT NULL)
   7 - access("C"."ID"="A"."CLIENT_ID"(+))
       filter("A"."CLIENT_ID"(+) IS NOT NULL)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads

— общая стоимость выполнения запроса оказывается меньше стоимости слагаемых: при заведомо невыполнимых условиях ненужные операции доступа к бд исключаются оператором filter(NULL IS NOT NULL)

Или на тестовом примере:

SQL> create table T1 (C1 char(1));

Table created.

SQL> insert into T1 values(null); -- содержимое таблицы не имеет значения

1 row created.

SQL> select * from T1 where C1 = '';

no rows selected

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     0   (0)|          | -- Cost=0 !
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   |     1 |     3 |     3   (0)| 00:00:01 | -- Cost=3
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NULL IS NOT NULL)

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
         16  recursive calls
          0  db block gets
         36  consistent gets -- при первом выполнении читают рекурсивные запросы + dynamic sampling
         13  physical reads
          0  redo size
        330  bytes sent via SQL*Net to client
        513  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          0  rows processed

— и опять несмотря на ненулевую стоимость сканирования T1, стоимость всего запроса = 0, что логично — запросу получения результата не нужны данные бд

Поскольку таблица в этом примере «свежая» (без статистики) в соответсвии со значением по умолчанию параметра:

SQL> @param optimizer_dynamic_sampling
NAME                                       VALUE
------------------------------------------ -----
optimizer_dynamic_sampling                 2

дополнительно выполняется избыточная операция dynamic sampling после получения значений связанных переменных (что важно для запросов со связанными переменными), уже на этапе выбора метода доступа к таблице:

*******************************************
Peeked values of the binds in SQL statement
*******************************************
...
Table Stats::
  Table: T1  Alias: T1  (NOT ANALYZED)
    #Rows: 409  #Blks:  5  AvgRowLen:  100.00  ChainCnt:  0.00 -- default статистика неанализорованной таблицы
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T1[T1]
*** 2012-08-26 11:55:17.953
** Performing dynamic sampling initial checks. **
  Column (#1): C1(  NO STATISTICS (using defaults)             -- в трейсе запроса со связанной переменной можно видеть
    AvgLen: 3 NDV: 13 Nulls: 0 Density: 0.078240               -- default статистика неанализорованного столбца типа CHAR
** Dynamic sampling initial checks returning TRUE (level = 2).
** Dynamic sampling updated table stats.: blocks=5

— что добавляет небольшой overhead при разборе (hard parse) во время первого выполнения запроса

Последующие выпонения уже разобранного запроса уже не читают блоки бд и не делают никаких сортировок за ненадобностью, как и ожидается:

SQL> select * from T1 where C1 = '';
no rows selected
...
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        330  bytes sent via SQL*Net to client
        513  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

К сожалению, замечательная операция filter(NULL IS NOT NULL) в явном виде не используется Oracle для того же запроса со связанными переменными:

SQL> var v1 char;

SQL> exec :v1 := null;

PL/SQL procedure successfully completed.

SQL> select * from T1 where C1 = :v1;

no rows selected

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     2 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C1"=:V1)

Statistics
----------------------------------------------------------
          0  recursive calls -- выполнение разобранного запроса - нет рекурсивных вызовов
          0  db block gets
          6  consistent gets -- блоки таблицы читаются :(
          ...

— даже если при первом выполнении/разборе курсора (hard parse) в качестве значения связанной переменной использовался NULL — bind peeking в этом случае не помогает

Однако, при наличии индекса Oracle может выполнять такой запрос разумно и логично, без доступа к блокам бд:

SQL> create index I_T1 on T1(C1);

Index created.

SQL> exec :v1 := null;

PL/SQL procedure successfully completed.

SQL> select * from T1 where C1 = :v1;

no rows selected

SQL> /

no rows selected

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|  0  | SELECT STATEMENT |      |     1 |     2 |     1   (0)| 00:00:01 |
|* 1  |  INDEX RANGE SCAN| I_T1 |     1 |     2 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C1"=:V1)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          0 consistent gets -- нет чтений блоков
          0 physical reads
          ...
          0 rows processed

-- SQL трейс подтверждает: cr=0
FETCH #348876952:c=0,e=7,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2966378588,tim=630613457219

— т.е., отображая в плане обычную операцию INDEX RANGE SCAN, предполагающую доступ к блокам индекса, Oracle в зависимости от значения связанной переменной может полностью исключать обращения к данным бд. Очень похоже на неявное использование аналога операции типа filter(NULL IS NOT NULL)

При не NULL-вом значении переменной индекс честно читается:

SQL> exec :v1 := 'A';

PL/SQL procedure successfully completed.

SQL> select * from T1 where C1 = :v1;

no rows selected

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| I_T1 |     1 |     2 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C1"=:V1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets -- прочитан блок индекса
          0  physical reads
...

-- SQL Trace: cr=1
FETCH #430738584:c=0,e=38,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=2966378588,tim=631402085997

Важно отметить, что при выборе способа выполнения запроса (читать или не читать блоки) Oracle не использует статистику таблицы:

SQL> exec dbms_stats.delete_table_stats('','T1');

PL/SQL procedure successfully completed.

SQL> exec :v1 := null;

PL/SQL procedure successfully completed.

SQL> select * from T1 where C1 = :v1;

no rows selected

SQL> /

no rows selected

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     3 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| I_T1 |     1 |     3 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1"=:V1)

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads

— т.о. в этом месте поведение Oracle основано на анализе значений связанных переменных (bind peeking) при каждом выполнении курсора на этапе soft parse:

SQL> exec :v1 := 'a';

PL/SQL procedure successfully completed.

SQL> select * from T1 where C1 = :v1;

no rows selected

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     3 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| I_T1 |     1 |     3 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1"=:V1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets -- читает блок при ненулевом значении :v1
          1  physical reads

SQL> exec :v1 := null;

PL/SQL procedure successfully completed.

SQL> select * from T1 where C1 = :v1;

no rows selected

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     3 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| I_T1 |     1 |     3 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1"=:V1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets -- не читает блок при :v1 = NULL независимо от порядка использования значений переменной
          0  physical reads

Для первоначального практического запроса добиться того же эффекта можно с помощью пары дополнительных индексов + эквивалентно переформулировав текст запроса — заменив LEFT OUTER JOIN на UNION, таким образом приводя сложный запрос к объединению двух запросов с простыми одиночными предикатами:

SQL> create index I_T_ACC_XXX on T_ACCOUNT(LOGIN, CLIENT_ID) tablespace users
  2  /

Index created.

SQL> create index i_t_CLI_XXX on T_CLIENT(TO_CHAR(ID)) tablespace users
  2  /

Index created.

SQL> exec :P25_CLIENTX := null;

PL/SQL procedure successfully completed.

SQL> SELECT case
  2           when count(id) = 0 then 0 else 1 end as "COUNT"
  3    FROM
  4    (
  5     select /*+ index(C I_T_CLI_XXX) */ c.id from T_CLIENT c
  6     WHERE to_char(c.id) = :P25_CLIENTX
  7     union
  8     select c.id from T_CLIENT c, T_ACCOUNT a
  9     WHERE c.id = a.CLIENT_ID
 10     and LOGIN = :P25_CLIENTX
 11     )
 12  /

Elapsed: 00:00:00.13

--------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |             |     1 |    13 |   135   (3)| 00:00:02 |
|   1 |  SORT AGGREGATE                    |             |     1 |    13 |            |          |
|   2 |   VIEW                             |             | 18920 |   240K|   135   (3)| 00:00:02 |
|   3 |    SORT UNIQUE                     |             | 18920 |   517K|   135   (4)| 00:00:02 |
|   4 |     UNION-ALL                      |             |       |       |            |          |
|   5 |      MAT_VIEW ACCESS BY INDEX ROWID| T_CLIENT    | 18919 |   517K|   130   (0)| 00:00:02 |
|*  6 |       INDEX RANGE SCAN             | I_T_CLI_XXX |  7568 |       |     1   (0)| 00:00:01 |
|   7 |      NESTED LOOPS                  |             |     1 |    25 |     2   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN             | I_T_ACC_XXX |     1 |    19 |     1   (0)| 00:00:01 |
|*  9 |       INDEX UNIQUE SCAN            | T_CLIENT_PK |     1 |     6 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access(TO_CHAR("ID")=:P25_CLIENTX)
   8 - access("LOGIN"=:P25_CLIENTX)
       filter("A"."CLIENT_ID" IS NOT NULL)
   9 - access("C"."ID"="A"."CLIENT_ID")

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads

— т.о. для NULL-евых значений связанных переменных Oracle может исключать логически избыточные чтения, заметно улучшая время выполнения запросов: с 3 секунд до 100 мс, например

P.S. Что делает СУБД с пустыми (NULL) значениями BIND-переменных в SQL-запросах?

2 комментария »

  1. Игорь, касательно «COLUMN1 = NULL и по определению становятся невыполнимыми (в смысле всегда возвращающими FALSE)». Разве конструкция smth = null не возвращает null во всех случаях?

    комментарий от Andrew — 07.09.2012 @ 09:14 | Ответить

    • Вы правы, Андрей, строго говоря, COLUMN1 = NULL возвращает NULL (UNKNOWN), что только в простых случаях (как в приведённых примерах) эквивалентно FALSE
      исправил

      комментарий от Igor Usoltsev — 07.09.2012 @ 14:04 | Ответить


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 такие блоггеры, как: