Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl→      все
 CBO и порядок условий в WHERE  [new]
Школяр
Guest 		Зависит ли результат работы CBO от порядка расположения условий в WHERE?

Вариант 1

...
WHERE
<условие 1> AND
<условие 2>

Вариант 2

...
WHERE
<условие 2> AND
<условие 1>

Может ли так получиться, что план для Варианта 2 будет другим, чем для Варианта 1 при всех прочих равных условиях?

А для RBO?

В доке ссылок по теме не нашел.

Вопрос вознику в силу того, что один гуру утверждает, что одно из моих условий нужно поставить первым, т.к. "всегда так надо делать". На моей базе строится тот же план, но гуру говорит, что в общем случае план может поменяться в лучшую сторону, поэтому условие надо обязательно переставить.

Сомнительно, однако, что план CBO зависит от порядка условий. Или как?
13 июл 05, 12:38    [1699410]     Ответить | Цитировать Сообщить модератору
 Re: CBO и порядок условий в WHERE  [new]
DinoRay
Member

Откуда: Киев
Сообщений: 108
 		Непомню где читал кажется Секреты Oracle SQL
разбор секции WHERE ничинается с конца

А план менятся не должен..
Другое дело FROM

Пусть твой гуру тебе докажет что план меняется...
13 июл 05, 12:59    [1699551]     Ответить | Цитировать Сообщить модератору
 Re: CBO и порядок условий в WHERE  [new]
Viewer
Member

Откуда: Самара
Сообщений: 5369
 		посмотри подборку из FAQ
13 июл 05, 13:01    [1699568]     Ответить | Цитировать Сообщить модератору
 Re: CBO и порядок условий в WHERE  [new]
Elic
Member

Откуда:
Сообщений: 30028
 		Порядок соединения таблиц и обработки условий
13 июл 05, 13:10    [1699638]     Ответить | Цитировать Сообщить модератору
 Re: CBO и порядок условий в WHERE  [new]
johanna
Member

Откуда: заслуженная батаничка в третьем поколении
Сообщений: 7400
 		недавно(неделю назад) меня учили ораклисты, что никакой роли порядок в WHERE не играет.
А вопрос я специально задала.
13 июл 05, 23:59    [1702169]     Ответить | Цитировать Сообщить модератору
 Re: CBO и порядок условий в WHERE  [new]
Elic
Member

Откуда:
Сообщений: 30028
johanna
недавно(неделю назад) меня учили ораклисты, что никакой роли порядок в WHERE не играет.
А вопрос я специально задала.
И ты будешь слепо верить неким "ораклистам" вместо того, чтобы проверить?!
SQL> select * from dual where 1 < 0 and 1/0 > 0;
ERROR:
ORA-01476: divisor is equal to zero



no rows selected

SQL> select * from dual where 1/0 > 0 and 1 < 0;

no rows selected
14 июл 05, 09:21    [1702521]     Ответить | Цитировать Сообщить модератору
 Re: CBO и порядок условий в WHERE  [new]
kosour
Member

Откуда:
Сообщений: 236
Elic
И ты будешь слепо верить неким "ораклистам" вместо того, чтобы проверить?!
SQL> select * from dual where 1 < 0 and 1/0 > 0;
ERROR:
ORA-01476: divisor is equal to zero
no rows selected

SQL> select * from dual where 1/0 > 0 and 1 < 0;
no rows selected


К сожалению данный пример показывает различия при выполнении запроса (точнее, что используется "быстрая проверка условий" с конце where (т.е. до первого FALSE), а не при построении его плана. Кстати в данном примере в обоих случаях планы одинаковы :)
14 июл 05, 09:54    [1702656]     Ответить | Цитировать Сообщить модератору
 Re: CBO и порядок условий в WHERE  [new]
kosour
Member

Откуда:
Сообщений: 236
DinoRay
Непомню где читал кажется Секреты Oracle SQL
разбор секции WHERE ничинается с конца

А план менятся не должен..
Другое дело FROM

Пусть твой гуру тебе докажет что план меняется...


от порядка во FROM тоже не зависит (если нет хинта ORDERED) почти. Если оптимизатор успеет все возможные планы запросов построить (количество < OPTIMIZER_PLAN_PERMUTATIONS)
14 июл 05, 09:56    [1702668]     Ответить | Цитировать Сообщить модератору
 Re: CBO и порядок условий в WHERE  [new]
Я и ёжик
Member

Откуда: СПб
Сообщений: 1815
kosour

К сожалению данный пример показывает различия при выполнении запроса (точнее, что используется "быстрая проверка условий" с конце where (т.е. до первого FALSE), а не при построении его плана. Кстати в данном примере в обоих случаях планы одинаковы :)

Порядок и место вычисления предикатов тоже часть плана. Так что всё таки разные.

Школяр
В доке ссылок по теме не нашел.

Oracle® Data Cartridge Developer's Guide.10g Release 1 (10.1). Part Number B10800-01. Predicate Ordering

Metalink Note:276877.1 How the performance of the query is influenced by its predicate order
14 июл 05, 10:22    [1702781]     Ответить | Цитировать Сообщить модератору
 Re: CBO и порядок условий в WHERE  [new]
johanna
Member

Откуда: заслуженная батаничка в третьем поколении
Сообщений: 7400
Elic
И ты будешь слепо верить неким "ораклистам" вместо того, чтобы проверить?!

так мы там сразу и проверили, при перестановке в WHERE сгенерированные планы ничем не различались.
14 июл 05, 11:58    [1703352]     Ответить | Цитировать Сообщить модератору
 Re: CBO и порядок условий в WHERE  [new]
kosour
Member

Откуда:
Сообщений: 236
Я и ёжик

Порядок и место вычисления предикатов тоже часть плана. Так что всё таки разные.


А как увидеть это различие в планах ? На примере данных двух селектов.
14 июл 05, 12:02    [1703385]     Ответить | Цитировать Сообщить модератору
 Re: CBO и порядок условий в WHERE  [new]
Я и ёжик
Member

Откуда: СПб
Сообщений: 1815
kosour

А как увидеть это различие в планах ? На примере данных двух селектов.

Посмотреть планы :)

SQL> explain plan for
  2  select * from dual where 1 < 0 and 1/0 > 0;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  FILTER              |             |       |       |       |
|   2 |   TABLE ACCESS FULL  | DUAL        |       |       |       |
--------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter(1/0>0 AND 1<0)

Note: rule based optimization

15 rows selected.

SQL> explain plan for
  2  select * from dual where 1/0 > 0 and 1 < 0;

Explained.

SQL>  select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  FILTER              |             |       |       |       |
|   2 |   TABLE ACCESS FULL  | DUAL        |       |       |       |
--------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter(1<0 AND 1/0>0)

Note: rule based optimization

15 rows selected.
14 июл 05, 12:14    [1703445]     Ответить | Цитировать Сообщить модератору
 Re: CBO и порядок условий в WHERE  [new]
Школяр
Guest
Я и ёжик
kosour

К сожалению данный пример показывает различия при выполнении запроса (точнее, что используется "быстрая проверка условий" с конце where (т.е. до первого FALSE), а не при построении его плана. Кстати в данном примере в обоих случаях планы одинаковы :)

Порядок и место вычисления предикатов тоже часть плана. Так что всё таки разные.

Школяр
В доке ссылок по теме не нашел.

Oracle® Data Cartridge Developer's Guide.10g Release 1 (10.1). Part Number B10800-01. Predicate Ordering

Metalink Note:276877.1 How the performance of the query is influenced by its predicate order


Спасибо, на Металинке все хорошо обьяснено.
14 июл 05, 12:28    [1703510]     Ответить | Цитировать Сообщить модератору
 Re: CBO и порядок условий в WHERE  [new]
johanna
Member

Откуда: заслуженная батаничка в третьем поколении
Сообщений: 7400
Я и ёжик
1 - filter(1/0>0 AND 1<0)

Note: rule based optimization

так это у вас не CBO а RBO(Rule Based Optimizator). Вопрос же был про CBO.
14 июл 05, 12:44    [1703589]     Ответить | Цитировать Сообщить модератору
 Re: CBO и порядок условий в WHERE  [new]
Я и ёжик
Member

Откуда: СПб
Сообщений: 1815
 		P.S.
Вообще говоря, предикаты могут быть и несколько сложнее чем 1 < 0, и содержать в том числе подзапросы.
Ниже пример с двумя предикатами:
( select count(*) from table_big tb where tb.f1 = table1.object_id) > 0
и
( select count(*) from table_small ts where ts.f1 = table1.object_id) > 0.
Условия обоих никогда не выполняются, оба выполняют full scan ( индексы преднамеренно не построены ), один по большой таблице, другой по маленькой.
Различия при перестановке этих предикатов явно видны и в плане ( порядок обращения к таблицам ) и особенно во времени и статистике выполнения:



SQL> set timing on
SQL> set autotrace traceonly

SQL>  select * from table1
  2   where
  3     ( select count(*) from table_big tb where tb.f1 = table1.object_id) > 0
  4     and
  5     ( select count(*) from table_small ts where ts.f1 = table1.object_id) > 0
  6     and rownum < 100;

no rows selected

Elapsed: 00:17:41.57

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=799 Card=99 Bytes=
          8514)

   1    0   COUNT (STOPKEY)
   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'TABLE1' (TABLE) (Cost=546 Card
          =99 Bytes=8514)

   4    2       SORT (AGGREGATE)
   5    4         TABLE ACCESS (FULL) OF 'TABLE_BIG' (TABLE) (Cost=246
           Card=51053 Bytes=204212)

   6    2       SORT (AGGREGATE)
   7    6         TABLE ACCESS (FULL) OF 'TABLE_SMALL' (TABLE) (Cost=6
           Card=4 Bytes=16)





Statistics
----------------------------------------------------------
        759  recursive calls
          0  db block gets
   12304575  consistent gets
        940  physical reads
          0  redo size
        954  bytes sent via SQL*Net to client
        373  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>  select * from table1
  2   where
  3   ( select count(*) from table_small ts where ts.f1 = table1.object_id) > 0
  4    and
  5   ( select count(*) from table_big tb where tb.f1 = table1.object_id) > 0;

no rows selected

Elapsed: 00:00:01.95

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=957 Card=128 Bytes
          =11008)

   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'TABLE1' (TABLE) (Cost=704 Card=1
          28 Bytes=11008)

   3    1     SORT (AGGREGATE)
   4    3       TABLE ACCESS (FULL) OF 'TABLE_SMALL' (TABLE) (Cost=6 C
          ard=4 Bytes=16)

   5    1     SORT (AGGREGATE)
   6    5       TABLE ACCESS (FULL) OF 'TABLE_BIG' (TABLE) (Cost=246 C
          ard=51053 Bytes=204212)





Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     153861  consistent gets
        699  physical reads
          0  redo size
        954  bytes sent via SQL*Net to client
        373  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
14 июл 05, 12:50    [1703622]     Ответить | Цитировать Сообщить модератору
 Re: CBO и порядок условий в WHERE  [new]
Я и ёжик
Member

Откуда: СПб
Сообщений: 1815
johanna
так это у вас не CBO а RBO(Rule Based Optimizator). Вопрос же был про CBO.

Вот вам CBO:

SQL> explain plan for 
  2    select /*+ dynamic_sampling(2)*/ *
  3   from  dual where 1/0 > 0 and 1 < 0;

Explained.

SQL> select * from  table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |     2 |    11 |
|*  1 |  FILTER              |             |       |       |       |
|   2 |   TABLE ACCESS FULL  | DUAL        |     1 |     2 |    11 |
--------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter(1<0 AND 1/0>0)

Note: cpu costing is off

15 rows selected.

SQL>  explain plan for 
  2    select /*+ dynamic_sampling(2)*/ *
  3   from  dual where 1 < 0 and 1/0 > 0;

Explained.

SQL>  select * from  table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |     2 |    11 |
|*  1 |  FILTER              |             |       |       |       |
|   2 |   TABLE ACCESS FULL  | DUAL        |     1 |     2 |    11 |
--------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter(1/0>0 AND 1<0)

Note: cpu costing is off

15 rows selected.

В идеале конечно порядок предикатов влияния оказывать не должен, но пока мы имеем дело с неидеальным оптимизатором.
Важный, на мой взгляд, состоит в том, что не стоит закладываться на определенный порядок обработки придекатов запроса, писать запрос лучше так, что бы правильность его выполнения не зависила от порядка вычисления предикатов. Обработка может менятся от версии к версии, добавляются новые возможности и.т.д и.т.п.
14 июл 05, 13:17    [1703789]     Ответить | Цитировать Сообщить модератору
 Re: CBO и порядок условий в WHERE  [new]
johanna
Member

Откуда: заслуженная батаничка в третьем поколении
Сообщений: 7400
Я и ёжик
Вот вам CBO:

ну и чем они отличаются?

кстати один и тот же statement обычно первый раз выполняется дольше(это по-вашему примеру выше)
14 июл 05, 13:25    [1703841]     Ответить | Цитировать Сообщить модератору
 Re: CBO и порядок условий в WHERE  [new]
Я и ёжик
Member

Откуда: СПб
Сообщений: 1815
johanna
Я и ёжик
Вот вам CBO:

ну и чем они отличаются?

Порядком предикатов :)
   1 - filter(1<0 AND 1/0>0)
   1 - filter(1/0>0 AND 1<0)


johanna
кстати один и тот же statement обычно первый раз выполняется дольше(это по-вашему примеру выше)

Сравните число логических чтений в обоих случаях.
В первом выполняется full scan по большой таблице ( table_big, ~26 тыс. записей), выполняется он для каждой строки из исходной таблицы ( table1, тоже ~ 26 тыс. раз ) всегда возвращает 0 и следующий предикат ( по малой таблице с 10-ю записями) присоедененный по and не проверяется.
Во втором первым выполняется full scan по малой таблице ( table_small, 10-записей), который также всегда возыращает 0 и предикат с сканированием большой таблицы не выполняется. т.е. мы имеем всего ~26 тыс. сканирований по малой таблице.
Эффекта от кеширования при повторных выполнениях здесь заметно не будет.
14 июл 05, 14:14    [1704144]     Ответить | Цитировать Сообщить модератору
 Re: CBO и порядок условий в WHERE  [new]
Andrew Max
Member

Откуда:
Сообщений: 1045
 		Интересная тема.

Итак, все пришли к выводу, что для CBO порядок предикатов важен?
И кажется, что эксперимент, который выполнил Я и ежик, только подтверждает это.

Однако, меня несколько смущает строка cpu costing is off в его примере.
К тому же, зачем брать вырожденный случай и экспериментировать с dual? Давайте возьмем более реальную таблицу:
SQL*Plus: Release 9.2.0.1.0 - Production on Чтв Июл 14 15:54:27 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect max
Введите пароль:
Соединено.
SQL>
SQL> select * from v$version where rownum = 1;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production

SQL> create table b(
  2   col1 integer not null,
  3   col2 varchar2(10) not null);

Таблица создана.

SQL> insert /*+ APPEND */ into b
  2  select rownum, 'Test ' || to_char(rownum)
  3   from all_objects;

35118 строк создано.

SQL> commit;

Фиксация обновлений завершена.

SQL> -- Тест для RBO
SQL> explain plan for
  2  select * from b
  3   where col2 > 'Test' and col1 < 10;

Объяснено.

SQL> @utlxpls

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  TABLE ACCESS FULL   | B           |       |       |       |
--------------------------------------------------------------------

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

   1 - filter("B"."COL1"<10 AND "B"."COL2">'Test')

Note: rule based optimization

14 строк выбрано.

SQL> explain plan for
  2  select * from b
  3   where col1 < 10 and col2 > 'Test';

Объяснено.

SQL> @utlxpls

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  TABLE ACCESS FULL   | B           |       |       |       |
--------------------------------------------------------------------

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

   1 - filter("B"."COL2">'Test' AND "B"."COL1"<10)

Note: rule based optimization

14 строк выбрано.

Итак, для RBO порядок предикатов, действительно, имеет значение. И похоже, что предикаты «вычисляются» в порядке справа – налево. Теперь посмотрим, как поведет себя CBO.

SQL> -- Тест CBO
SQL> exec dbms_stats.gather_table_stats(ownname => user, tabname => 'B');

Процедура PL/SQL успешно завершена.

SQL> explain plan for
  2  select * from b
  3   where col2 > 'Test' and col1 < 10;

Объяснено.

SQL> @utlxpls

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------

-------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     9 |   135 |    63  (32)|
|*  1 |  TABLE ACCESS FULL   | B           |     9 |   135 |    63  (32)|
-------------------------------------------------------------------------

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

   1 - filter("B"."COL1"<10 AND "B"."COL2">'Test')

12 строк выбрано.

SQL> explain plan for
  2  select * from b
  3   where col1 < 10 and col2 > 'Test';

Объяснено.

SQL> @utlxpls

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------

-------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     9 |   135 |    63  (32)|
|*  1 |  TABLE ACCESS FULL   | B           |     9 |   135 |    63  (32)|
-------------------------------------------------------------------------

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

   1 - filter("B"."COL1"<10 AND "B"."COL2">'Test')

12 строк выбрано.

Вот те на! В обоих случаях план одинаков… :)
Или я что-то сделал не так?...

Кстати, для того, чтобы все-таки «заставить» Oracle сначала «вычислять» условие COL2 > ‘Test’, можно использовать известный хинт:

SQL> explain plan for
  2  select /*+ ORDERED_PREDICATES */ * from b
  3   where col2 > 'Test' and col1 < 10;

Объяснено.

SQL> @utlxpls

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------

-------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     9 |   135 |    67  (36)|
|*  1 |  TABLE ACCESS FULL   | B           |     9 |   135 |    67  (36)|
-------------------------------------------------------------------------

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

   1 - filter("B"."COL2">'Test' AND "B"."COL1"<10)

12 строк выбрано.
14 июл 05, 16:10    [1704932]     Ответить | Цитировать Сообщить модератору
 Re: CBO и порядок условий в WHERE  [new]
johanna
Member

Откуда: заслуженная батаничка в третьем поколении
Сообщений: 7400
автор
Интересная тема.

Итак, все пришли к выводу, что для CBO порядок предикатов важен?
И кажется, что эксперимент, который выполнил Я и ежик, только подтверждает это.

нет
так как "я и ежик" использовал в качестве предикатов подзапросы, а это уже другой случай.
14 июл 05, 16:26    [1705047]     Ответить | Цитировать Сообщить модератору
 Re: CBO и порядок условий в WHERE  [new]
Я и ёжик
Member

Откуда: СПб
Сообщений: 1815
Andrew Max
Интересная тема.
Итак, все пришли к выводу, что для CBO порядок предикатов важен?

Нет, не так, порядок может быть важен.

Andrew Max

Однако, меня несколько смущает строка cpu costing is off в его примере.
К тому же, зачем брать вырожденный случай и экспериментировать с dual?

Включение cpu costing для предикатов типа 1<0 и 1/0>0 ничего не изменяет.
Просили показать на примере , что привел Elic.


Andrew Max

Вот те на! В обоих случаях план одинаков… :)
Или я что-то сделал не так?...

Всё так, в данном случае CBO в состоянии различить селективность предикатов и выстраивает их в соответствующем порядке.
Можете посмотреть у Льюиса в Understanding System Statistics, раздел Predicate Order.

Предикаты бывают разные, в общем случае говорить, что порядок влияет или не влияет нельзя. Кроме того надо учитывать, что оптимизатор может перемещать предикаты по дереву запроса, вставлять предикаты, удалять предикаты, могут быть изменены параметры, кроме того запрос может быть преобразован, отхинтован (часть из этих возможностей Вы и продемонстрировали) и.т.д и.т.п см. ноту.
Я уже писал выше, что запрос писать надо так, чтобы порядок выполнения предикатов не влиял на правильность выполнения запроса. А будет ли зависить эфективность выполнения запроса от порядка предикатов надо смотреть в каждом конкретном случае, на конкретном сервере с конкретными настройками.
14 июл 05, 16:40    [1705142]     Ответить | Цитировать Сообщить модератору
 Re: CBO и порядок условий в WHERE  [new]
Andrew Max
Member

Откуда:
Сообщений: 1045
Я и ёжик

... в данном случае CBO в состоянии различить селективность предикатов и выстраивает их в соответствующем порядке. Можете посмотреть у Льюиса в Understanding System Statistics, раздел Predicate Order.

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


Все так, согласен. :)

Льюиса сейчас читать некогда, но я и так верю, что если оптимизатор может оценить селективность предикатов, он "выстроит" их в наиболее оптимальном порядке. Было бы весьма печально, если бы Оракл этого не умел.

Ясно, что в этом Вашем запросе:
select * from table1
  where
  (select count(*) from table_big tb where tb.f1 = table1.object_id) > 0
  and
  (select count(*) from table_small ts where ts.f1 = table1.object_id) > 0
  and rownum < 100;
оптимизатор просто не в состоянии заранее узнать, какой подзапрос "тяжелее". Поэтому в данном случае, конечно же, порядок имеет существенное значение.
14 июл 05, 16:53    [1705220]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: CBO и порядок условий в WHERE  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 117674
 		Прошу прощения, что поднял тему со дна морского :-)

У меня в программе динамически генерируется запрос такого типа

INSERT INTO
SELECT .... FROM ..... WHERE ......... AND 1 = 0
UNION ALL
INSERT INTO
SELECT .... FROM ..... WHERE ......... AND 1 = 0
UNION ALL
INSERT INTO
SELECT .... FROM ..... WHERE ......... AND 1 = 1
UNION ALL

Последний нолик или единичка поступают как элементы ассоциированного массива из
внешнего приложения. Они сообщают нам, что некое условие или выполнено (1) или
не выполнено (0) и как бы стОит проверять дальше или ни к чему ...
В последнее время стала подседать производительность. Появилось предположение,
что производительность может в частности упасть из за того, что другие условия
в запросах с 1=0 тем не менее выполняются, хотя это и абсолютно ни к чему.
Эксперсс-тесты в 10.2.0.4 показали, что последнее условие по прежнему вроде
бы выполняется первым. Но насколько надежно это знание ?
Очень интересует Ваше мнение. В качестве альтернативы продумывается идея, просто
распарсивать запрос так, чтобы при 0 вообще "выкидывать" это условие из
цепочки UNION. Это изменение довольно неприятное и громоздкое, и хочется убедиться,
что усилия будут затрачены не напрасно...

Каково Ваше мнение / знание , привязанное возможно к версии 10.2.0.4 ?
- Порядок случаен
- Порядок вообще то от конца к началу но рассчитывать на это не стоит
- Порядок жесткий - от конца к началу
- Что то иное :-)

Заранее спасибо, в эти 2 дня почти не буду на форуме, поэтому не взыщите - я потом все
прочитаю и напишу :-)
29 июн 09, 22:11    [7356361]     Ответить | Цитировать Сообщить модератору
 Re: CBO и порядок условий в WHERE  [new]
wildwind
Member

Откуда: Москва
Сообщений: 1296
dmidek
Появилось предположение,
что производительность может в частности упасть из за того, что другие условия
в запросах с 1=0 тем не менее выполняются, хотя это и абсолютно ни к чему.
Эксперсс-тесты в 10.2.0.4 показали, что последнее условие по прежнему вроде
бы выполняется первым. Но насколько надежно это знание ?

Смотря как получено и в каком виде. Если в виде разницы в количестве LIO, то полагаю надежно.
29 июн 09, 22:34    [7356421]     Ответить | Цитировать Сообщить модератору
 Re: CBO и порядок условий в WHERE  [new]
bigsov
Member

Откуда:
Сообщений: 282
 		не судите строго, попробовал привести пример, когда на 10ке порядок предикатов меняет план запроса:
+ запрос



alter system flush shared_pool;

SET linesize 1000
SET pagesize 0
SET echo on

DROP  TABLE scott.t;

CREATE TABLE scott.t
 AS SELECT LEVEL l,MOD(ROWNUM,2) r, 1 c FROM DUAL CONNECT BY LEVEL<=100000;

CREATE INDEX Idx1 ON scott.T (L);

CREATE INDEX Idx2 ON scott.T (r);

CREATE INDEX Idx3 ON scott.T (c);


EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname=> 'scott', tabname => 't', cascade => true);

-- сорри за  ужасные предикаты, пытался обмануть cbo

EXPLAIN  PLAN FOR
 SELECT * FROM scott.t WHERE  ( c>=2 or r>11)  AND r<=1 AND (l=1 or r>11) ;

SELECT *
  FROM TABLE (DBMS_XPLAN.display);

EXPLAIN   PLAN FOR
 SELECT * FROM scott.t WHERE          r<=1 AND (l=1 or r>11) AND ( c>=2 or r>11) ;

SELECT *
  FROM TABLE (DBMS_XPLAN.display);
-- судя по всему наличие статистики помогло оптимизатору разобраться с предикатами, удалим ее и посмотрим что будет

EXEC DBMS_STATS.delete_TABLE_STATS (ownname=> 'scott', tabname => 't');


EXPLAIN PLAN FOR
 SELECT * FROM scott.t WHERE ( c>=2 or r>11) AND r<=1 AND (l=1 or r>11) ;

SELECT *
  FROM TABLE (DBMS_XPLAN.display);

EXPLAIN   PLAN FOR
 SELECT * FROM scott.t WHERE    r<=1 AND (l=1 or r>11) AND ( c>=2 or r>11)  ;

SELECT *
  FROM TABLE (DBMS_XPLAN.display);

-- отсутствие статистики привело к разным планам, хотя логически запросы идентичны. dynamic sampling не позволил разобраться с предикатами


+ результаты



System switch log altered.
SQL> DROP  TABLE scott.t
Table dropped.
SQL> CREATE TABLE scott.t
 AS SELECT LEVEL l,MOD(ROWNUM,2) r, 1 c FROM DUAL CONNECT BY LEVEL<=100000
Table created.
SQL> CREATE INDEX Idx1 ON scott.T (L)
Index created.
SQL> CREATE INDEX Idx2 ON scott.T (r)
Index created.
SQL> CREATE INDEX Idx3 ON scott.T (c)
Index created.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname=> 'scott', tabname => 't', cascade => true)
PL/SQL procedure successfully completed.
SQL> EXPLAIN  PLAN FOR
 SELECT * FROM scott.t WHERE  ( c>=2 or r>11)  AND r<=1 AND (l=1 or r>11)
Explain complete.
SQL> SELECT *
  FROM TABLE (DBMS_XPLAN.display)
Plan hash value: 2954704728                                                                                                                                                                                                                                                                                 
                                                                                                                                                                                                                                                                                                            
-----------------------------------------------------------------------------------------                                                                                                                                                                                                                   
| Id  | Operation                        | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                                   
-----------------------------------------------------------------------------------------                                                                                                                                                                                                                   
|   0 | SELECT STATEMENT                 |      |     1 |    10 |     4  (25)| 00:00:01 |                                                                                                                                                                                                                   
|*  1 |  TABLE ACCESS BY INDEX ROWID     | T    |     1 |    10 |     4  (25)| 00:00:01 |                                                                                                                                                                                                                   
|   2 |   BITMAP CONVERSION TO ROWIDS    |      |       |       |            |          |                                                                                                                                                                                                                   
|   3 |    BITMAP OR                     |      |       |       |            |          |                                                                                                                                                                                                                   
|   4 |     BITMAP CONVERSION FROM ROWIDS|      |       |       |            |          |                                                                                                                                                                                                                   
|*  5 |      INDEX RANGE SCAN            | IDX1 |       |       |     1   (0)| 00:00:01 |                                                                                                                                                                                                                   
|   6 |     BITMAP CONVERSION FROM ROWIDS|      |       |       |            |          |                                                                                                                                                                                                                   
|   7 |      SORT ORDER BY               |      |       |       |            |          |                                                                                                                                                                                                                   
|*  8 |       INDEX RANGE SCAN           | IDX2 |       |       |     2   (0)| 00:00:01 |                                                                                                                                                                                                                   
-----------------------------------------------------------------------------------------                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                                                                            
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                         
---------------------------------------------------                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                            
   1 - filter(("C">=2 OR "R">11) AND "R"<=1)                                                                                                                                                                                                                                                                
   5 - access("L"=1)                                                                                                                                                                                                                                                                                        
   8 - access("R">11)                                                                                                                                                                                                                                                                                       
       filter("R">11)                                                                                                                                                                                                                                                                                       


23 rows selected.
SQL> EXPLAIN   PLAN FOR
 SELECT * FROM scott.t WHERE          r<=1 AND (l=1 or r>11) AND ( c>=2 or r>11)
Explain complete.
SQL> SELECT *
  FROM TABLE (DBMS_XPLAN.display)
Plan hash value: 2954704728                                                                                                                                                                                                                                                                                 
                                                                                                                                                                                                                                                                                                            
-----------------------------------------------------------------------------------------                                                                                                                                                                                                                   
| Id  | Operation                        | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                                   
-----------------------------------------------------------------------------------------                                                                                                                                                                                                                   
|   0 | SELECT STATEMENT                 |      |     1 |    10 |     4  (25)| 00:00:01 |                                                                                                                                                                                                                   
|*  1 |  TABLE ACCESS BY INDEX ROWID     | T    |     1 |    10 |     4  (25)| 00:00:01 |                                                                                                                                                                                                                   
|   2 |   BITMAP CONVERSION TO ROWIDS    |      |       |       |            |          |                                                                                                                                                                                                                   
|   3 |    BITMAP OR                     |      |       |       |            |          |                                                                                                                                                                                                                   
|   4 |     BITMAP CONVERSION FROM ROWIDS|      |       |       |            |          |                                                                                                                                                                                                                   
|*  5 |      INDEX RANGE SCAN            | IDX1 |       |       |     1   (0)| 00:00:01 |                                                                                                                                                                                                                   
|   6 |     BITMAP CONVERSION FROM ROWIDS|      |       |       |            |          |                                                                                                                                                                                                                   
|   7 |      SORT ORDER BY               |      |       |       |            |          |                                                                                                                                                                                                                   
|*  8 |       INDEX RANGE SCAN           | IDX2 |       |       |     2   (0)| 00:00:01 |                                                                                                                                                                                                                   
-----------------------------------------------------------------------------------------                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                                                                            
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                         
---------------------------------------------------                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                            
   1 - filter(("C">=2 OR "R">11) AND "R"<=1)                                                                                                                                                                                                                                                                
   5 - access("L"=1)                                                                                                                                                                                                                                                                                        
   8 - access("R">11)                                                                                                                                                                                                                                                                                       
       filter("R">11)                                                                                                                                                                                                                                                                                       


23 rows selected.
SQL> EXEC DBMS_STATS.delete_TABLE_STATS (ownname=> 'scott', tabname => 't')
PL/SQL procedure successfully completed.
SQL> EXPLAIN PLAN FOR
 SELECT * FROM scott.t WHERE ( c>=2 or r>11) AND r<=1 AND (l=1 or r>11)
Explain complete.
SQL> SELECT *
  FROM TABLE (DBMS_XPLAN.display)
Plan hash value: 2374804741                                                                                                                                                                                                                                                                                 
                                                                                                                                                                                                                                                                                                            
-------------------------------------------------------------------------------------                                                                                                                                                                                                                       
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                                       
-------------------------------------------------------------------------------------                                                                                                                                                                                                                       
|   0 | SELECT STATEMENT             |      |     6 |   234 |    16   (0)| 00:00:01 |                                                                                                                                                                                                                       
|   1 |  CONCATENATION               |      |       |       |            |          |                                                                                                                                                                                                                       
|*  2 |   TABLE ACCESS BY INDEX ROWID| T    |     3 |   117 |     6   (0)| 00:00:01 |                                                                                                                                                                                                                       
|*  3 |    INDEX RANGE SCAN          | IDX2 |    83 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                                                       
|*  4 |   TABLE ACCESS BY INDEX ROWID| T    |     3 |   117 |    10   (0)| 00:00:01 |                                                                                                                                                                                                                       
|*  5 |    INDEX RANGE SCAN          | IDX3 |   165 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                                                       
-------------------------------------------------------------------------------------                                                                                                                                                                                                                       
                                                                                                                                                                                                                                                                                                            
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                         
---------------------------------------------------                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                            
   2 - filter("R">11 OR "L"=1)                                                                                                                                                                                                                                                                              
   3 - access("R">11 AND "R"<=1)                                                                                                                                                                                                                                                                            
   4 - filter("R"<=1 AND ("R">11 OR "L"=1) AND LNNVL("R">11))                                                                                                                                                                                                                                               
   5 - access("C">=2)                                                                                                                                                                                                                                                                                       
                                                                                                                                                                                                                                                                                                            
Note                                                                                                                                                                                                                                                                                                        
-----                                                                                                                                                                                                                                                                                                       
   - dynamic sampling used for this statement                                                                                                                                                                                                                                                               


24 rows selected.
SQL> EXPLAIN   PLAN FOR
 SELECT * FROM scott.t WHERE    r<=1 AND (l=1 or r>11) AND ( c>=2 or r>11)
Explain complete.
SQL> SELECT *
  FROM TABLE (DBMS_XPLAN.display)
Plan hash value: 1614534569                                                                                                                                                                                                                                                                                 
                                                                                                                                                                                                                                                                                                            
-------------------------------------------------------------------------------------                                                                                                                                                                                                                       
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                                       
-------------------------------------------------------------------------------------                                                                                                                                                                                                                       
|   0 | SELECT STATEMENT             |      |     5 |   195 |    11   (0)| 00:00:01 |                                                                                                                                                                                                                       
|   1 |  CONCATENATION               |      |       |       |            |          |                                                                                                                                                                                                                       
|*  2 |   TABLE ACCESS BY INDEX ROWID| T    |     1 |    39 |     5   (0)| 00:00:01 |                                                                                                                                                                                                                       
|*  3 |    INDEX RANGE SCAN          | IDX1 |    74 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                                                       
|*  4 |   TABLE ACCESS BY INDEX ROWID| T    |     4 |   156 |     6   (0)| 00:00:01 |                                                                                                                                                                                                                       
|*  5 |    INDEX RANGE SCAN          | IDX2 |    83 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                                                       
-------------------------------------------------------------------------------------                                                                                                                                                                                                                       
                                                                                                                                                                                                                                                                                                            
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                         
---------------------------------------------------                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                            
   2 - filter("R"<=1 AND ("C">=2 OR "R">11))                                                                                                                                                                                                                                                                
   3 - access("L"=1)                                                                                                                                                                                                                                                                                        
   4 - filter(("C">=2 OR "R">11) AND LNNVL("L"=1))                                                                                                                                                                                                                                                          
   5 - access("R">11 AND "R"<=1)                                                                                                                                                                                                                                                                            
                                                                                                                                                                                                                                                                                                            
Note                                                                                                                                                                                                                                                                                                        
-----                                                                                                                                                                                                                                                                                                       
   - dynamic sampling used for this statement                                                                                                                                                                                                                                                               


24 rows selected.





BANNER                                                          
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production                          
CORE	10.2.0.1.0	Production                                      
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production         
NLSRTL Version 10.2.0.1.0 - Production
30 июн 09, 02:31    [7356954]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl→      все
Все форумы / Oracle Ответить