Определить порядок предикатов в oracle / Oracle

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Определить порядок предикатов в oracle

11 сообщений из 11, страница 1 из 1

Определить порядок предикатов в oracle

#39986987

Pavel_PV

Гость

Всем привет!
Делал тут одну задачку и что-то подвис. Соль в том, что оракл case обрабатывает как единый целый организм, а хотелось бы видеть классическую схему: первое условие проверили, потом пошли на второе и т.д. Ниже запрос как пример. Если убрать "when 1=2 ****", то время выполнения многократно увеличивается. Если у кого-то боевые комплексы или мощные, то поставьте level <100 допустим. Итак:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

select count(*)
  from dba_objects,
       (select level || 'LVL' agent_name from dual connect by level < 40) mp
 where case
         when 1 = 1 then
          1
         when 1 = 2
              and instr(agent_name,
                        OBJECT_NAME) > 0 then
          2
         else
          0
       end = 1;

План приложу, он адаптивный, но думаю тут роли не играет:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.

Plan hash value: 4015914166
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                   |     1 |    89 |   154K  (1)| 00:00:31 |
|   1 |  SORT AGGREGATE                          |                   |     1 |    89 |            |          |
|   2 |   NESTED LOOPS                           |                   |   840 | 74760 |   154K  (1)| 00:00:31 |
|   3 |    VIEW                                  |                   |     1 |    23 |     2   (0)| 00:00:01 |
|*  4 |     CONNECT BY WITHOUT FILTERING         |                   |       |       |            |          |
|   5 |      FAST DUAL                           |                   |     1 |       |     2   (0)| 00:00:01 |
|*  6 |    VIEW                                  | DBA_OBJECTS       |   840 | 55440 |   154K  (1)| 00:00:31 |
|   7 |     UNION-ALL                            |                   |       |       |            |          |
|*  8 |      FILTER                              |                   |       |       |            |          |
|*  9 |       HASH JOIN                          |                   |   111K|  8708K|   615   (6)| 00:00:01 |
|  10 |        INDEX FAST FULL SCAN              | I_USER2           |   371 |  1484 |     2   (0)| 00:00:01 |
|* 11 |        HASH JOIN                         |                   |   111K|  8273K|   611   (6)| 00:00:01 |
|  12 |         INDEX FAST FULL SCAN             | I_USER2           |   371 |  8533 |     2   (0)| 00:00:01 |
|* 13 |         TABLE ACCESS FULL                | OBJ$              |   111K|  5769K|   607   (5)| 00:00:01 |
|  14 |       NESTED LOOPS                       |                   |     1 |    34 |     4   (0)| 00:00:01 |
|  15 |        NESTED LOOPS                      |                   |     1 |    25 |     3   (0)| 00:00:01 |
|  16 |         TABLE ACCESS BY INDEX ROWID      | IND$              |     1 |    12 |     2   (0)| 00:00:01 |
|* 17 |          INDEX UNIQUE SCAN               | I_IND1            |     1 |       |     1   (0)| 00:00:01 |
|* 18 |         TABLE ACCESS CLUSTER             | TAB$              |     1 |    13 |     1   (0)| 00:00:01 |
|* 19 |        INDEX RANGE SCAN                  | I_OBJ1            |     1 |     9 |     1   (0)| 00:00:01 |
|* 20 |       TABLE ACCESS CLUSTER               | TAB$              |     1 |    13 |     2   (0)| 00:00:01 |
|* 21 |        INDEX UNIQUE SCAN                 | I_OBJ#            |     1 |       |     1   (0)| 00:00:01 |
|* 22 |       TABLE ACCESS BY INDEX ROWID        | SEQ$              |     1 |     9 |     2   (0)| 00:00:01 |
|* 23 |        INDEX UNIQUE SCAN                 | I_SEQ1            |     1 |       |     1   (0)| 00:00:01 |
|* 24 |       TABLE ACCESS BY INDEX ROWID BATCHED| USER_EDITIONING$  |     1 |     7 |     3   (0)| 00:00:01 |
|* 25 |        INDEX RANGE SCAN                  | I_USER_EDITIONING |       |       |     1   (0)| 00:00:01 |
|* 26 |       TABLE ACCESS BY INDEX ROWID BATCHED| USER_EDITIONING$  |     1 |     7 |     3   (0)| 00:00:01 |
|* 27 |        INDEX RANGE SCAN                  | I_USER_EDITIONING |       |       |     1   (0)| 00:00:01 |
|  28 |       NESTED LOOPS SEMI                  |                   |     1 |    30 |     4   (0)| 00:00:01 |
|* 29 |        INDEX RANGE SCAN                  | I_OBJ4            |     1 |    10 |     3   (0)| 00:00:01 |
|* 30 |        INDEX RANGE SCAN                  | I_USER2           |     1 |    20 |     1   (0)| 00:00:01 |
|* 31 |      HASH JOIN                           |                   |    26 |   494 |     3   (0)| 00:00:01 |
|  32 |       INDEX FULL SCAN                    | I_LINK1           |    26 |   390 |     1   (0)| 00:00:01 |
|  33 |       INDEX FAST FULL SCAN               | I_USER2           |   371 |  1484 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter(LEVEL<40)
   6 - filter(CASE  WHEN 1=1 THEN 1 WHEN (1=2 AND INSTR("AGENT_NAME","OBJECT_NAME")>0) THEN 2 ELSE 0 
              END =1)
   8 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>2 AND "O"."TYPE#"<>6 OR "O"."TYPE#"=1 AND  NOT EXISTS 
              (SELECT 0 FROM "SYS"."OBJ$" "IO","SYS"."TAB$" "T","SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND 
              "I"."BO#"="T"."OBJ#" AND BITAND("T"."PROPERTY",36893488147419103232)=36893488147419103232 AND 
              "IO"."OBJ#"="I"."BO#" AND "IO"."TYPE#"=2) OR "O"."TYPE#"=2 AND  (SELECT 1 FROM "SYS"."TAB$" "T" WHERE 
              "T"."OBJ#"=:B2 AND BITAND("T"."PROPERTY",36893488147419103232)=0)=1 OR "O"."TYPE#"=6 AND  (SELECT 1 
              FROM "SYS"."SEQ$" "S" WHERE "S"."OBJ#"=:B3 AND (BITAND("S"."FLAGS",1024)=0 OR "S"."FLAGS" IS 
              NULL))=1) AND (BITAND("U"."SPARE1",16)=0 OR BITAND("O"."FLAGS",1048576)=1048576 OR "O"."TYPE#"<>88 
              AND  NOT EXISTS (SELECT 0 FROM "SYS"."USER_EDITIONING$" "UE" WHERE "UE"."USER#"=:B4 AND "TYPE#"=:B5) 
              OR  EXISTS (SELECT 0 FROM "SYS"."USER_EDITIONING$" "UE" WHERE "UE"."USER#"=:B6 AND "UE"."TYPE#"=:B7) 
              AND (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR "U"."TYPE#"=2 AND 
              "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR  EXISTS (SELECT 0 FROM 
              "SYS"."USER$" "U2","SYS"."OBJ$" "O2" WHERE "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B8 AND "U2"."TYPE#"=2 
              AND "O2"."OWNER#"="U2"."USER#" AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')
              )))))
   9 - access("O"."SPARE3"="U"."USER#")
  11 - access("O"."OWNER#"="U"."USER#")
  13 - filter("O"."LINKNAME" IS NULL AND "O"."TYPE#"<>10 AND "O"."NAME"<>'_NEXT_OBJECT' AND 
              "O"."NAME"<>'_default_auditing_options_' AND BITAND("O"."FLAGS",128)=0)
  17 - access("I"."OBJ#"=:B1)
  18 - filter("I"."BO#"="T"."OBJ#" AND BITAND("T"."PROPERTY",36893488147419103232)=3689348814741910323
              2)
  19 - access("IO"."OBJ#"="I"."BO#" AND "IO"."TYPE#"=2)
       filter("IO"."TYPE#"=2)
  20 - filter(BITAND("T"."PROPERTY",36893488147419103232)=0)
  21 - access("T"."OBJ#"=:B1)
  22 - filter(BITAND("S"."FLAGS",1024)=0 OR "S"."FLAGS" IS NULL)
  23 - access("S"."OBJ#"=:B1)
  24 - filter("TYPE#"=:B1)
  25 - access("UE"."USER#"=:B1)
  26 - filter("UE"."TYPE#"=:B1)
  27 - access("UE"."USER#"=:B1)
  29 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88)
  30 - access("O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND 
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
       filter("U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
  31 - access("L"."OWNER#"="U"."USER#")
 
Note
-----
   - this is an adaptive plan

Имхо всё дело в этой строке плана:
6 - filter(CASE WHEN 1=1 THEN 1 WHEN (1=2 AND INSTR("AGENT_NAME","OBJECT_NAME")>0) THEN 2 ELSE 0
END =1)
т.е. оракл все делает именно за раз.

Может кто-то сталкивался уже с таким поведением, а как избежать такого поведения через хинты или может переписать запрос( !!без конструкции with!! ). Через with понятно, что проблем нет, но в моем случае подобных условий ровно три и как-то ну сильно читаемость кода теряется.
Я сам пробовал и через decode, coalesce, через case вложенный в case. Что-та не вышло.

...

Рейтинг:

0 / 0

06.08.2020, 10:58

| Ответить | Цитировать | Написать

Определить порядок предикатов в oracle

#39986993

Вячеслав Любомудров

Участник

Откуда: Владивосток

Сообщения: 18 655

Рейтинг: 0 / 0

Был древний хинт ORDERED_PREDICATE
Но с представлиниями он может не дать ожидаемого результата
Еще есть всякие rownum>100000000, materialized и т.п.

...

Рейтинг:

0 / 0

06.08.2020, 11:04

| Ответить | Цитировать | Написать

Определить порядок предикатов в oracle

#39986997

Pavel_PV

Гость

Вячеслав Любомудров, ORDERED_PREDICATE я первым попробовал, но...А что касается materialized и т.п. они тут профита точно не дадут т.к. тогда нужна обертка через with. Про rownum не понял если честно, но думаю это из другой оперы.

...

Рейтинг:

0 / 0

06.08.2020, 11:15

| Ответить | Цитировать | Написать

Определить порядок предикатов в oracle

#39987003

Вячеслав Любомудров

Участник

Откуда: Владивосток

Сообщения: 18 655

Рейтинг: 0 / 0

Любой подзапрос можно оформить с materialized
Ограничение по rownum запрещает слияние условий основного запроса с представлением, что, как обычно, и приводит к неоптимальным планам

with -- оно только для красоты, select from (select) мало чем отличается

...

Рейтинг:

0 / 0

06.08.2020, 11:23

| Ответить | Цитировать | Написать

Определить порядок предикатов в oracle

#39987036

Pavel_PV

Гость

Вячеслав Любомудров, ну конечно имелось ввиду без использования with или подзапросов. Смысл materialized в материализации запроса, без конструкции with или подзапросов особого смысла в ней не вижу. Хотя написать вам никто не мешает.

...

Рейтинг:

0 / 0

06.08.2020, 12:28

| Ответить | Цитировать | Написать

Определить порядок предикатов в oracle

#39987048

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

Pavel_PV

Имхо всё дело в этой строке плана:
6 - filter(CASE WHEN 1=1 THEN 1 WHEN (1=2 AND INSTR("AGENT_NAME","OBJECT_NAME")>0) THEN 2 ELSE 0
END =1)
т.е. оракл все делает именно за раз.

нет, ты не правильно понял причины. Oracle не вызывает INSTR, дело совсем в другом:
при простом

Код: plsql

1.
2.
3.
4.

case
         when 1 = 1 then 1
         else 0
       end = 1;

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

А в случае

Код: plsql

1.
2.
3.
4.
5.

case
         when 1 = 1 then 1
         when 1 = 2 and instr(agent_name, OBJECT_NAME) > 0 then 2
         else 0
       end = 1;

оптимизатор видит, что для выражения требуются поля (agent_name, OBJECT_NAME) и строит другой план с передачей этих полей (см. секцию projection)

Проверяется крайне легко: создай свою функцию

Код: plsql

1.
2.
3.
4.
5.
6.
7.

create or replace function f_instr(v1 varchar2, v2 varchar2) return int deterministic
as
begin
  dbms_output.put_line('fired');
  return instr(v1,v2);
end;
/

и подсунь ее в запрос и увидишь, что она не вызывалась:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

select count(*)
  from dba_objects,
       (select level || 'LVL' agent_name from dual connect by level < 40) mp
 where case
         when 1 = 1 then
          1
         when 1 = 2 and f_instr(agent_name,OBJECT_NAME) > 0 then
          2
         else
          0
       end = 1;

...

Рейтинг:

0 / 0

06.08.2020, 12:50

| Ответить | Цитировать | Написать

Определить порядок предикатов в oracle

#39987078

Dshedoo

Участник

Сообщения: 310

Рейтинг: 0 / 0

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

Всегда можно сделать кейс в несколько уровней, где каждое условие будет выдавать булину, по которой мы либо идём на выход, либо проверяем дальше.

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.

select count(*)
  from dba_objects,
       (select level || 'LVL' agent_name from dual connect by level < 40) mp
 where case
         when 1 = 1 then 1
         when 1 = 2 then case when instr(agent_name,OBJECT_NAME) > 0 
                              then 2 end
         else 0
       end = 1;

...

Рейтинг:

0 / 0

06.08.2020, 13:31

| Ответить | Цитировать | Написать

Определить порядок предикатов в oracle

#39987086

Dshedoo

Участник

Сообщения: 310

Рейтинг: 0 / 0

Офк основной ELSE нужно выносить в каждый такой кейс, пересекающиеся условия должны быть под одним уровнем и т.д.
Например, условия:

Код: plsql

1.
2.
3.
4.
5.
6.
7.

 where case
         when 1 = 1 then 1
         when 1 = 2 and instr(agent_name,OBJECT_NAME) > 0 then 2 
         when 1 = 2 and instr(agent_name,OBJECT_NAME) = 0 then 3
         when 1 = 2 and instr(agent_name,OBJECT_NAME) < 0 then 4
         else 0
       end

Должны выглядеть как:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.

 where case
         when 1 = 1 then 1
         when 1 = 2 then case when instr(agent_name,OBJECT_NAME) > 0 then 2 
                              when instr(agent_name,OBJECT_NAME) = 0 then 3
                              when instr(agent_name,OBJECT_NAME) < 0 then 4
                                                                     else 0 
                                                                     end
         else 0
       end

Но в целом, как я и написал выше, если нужно получать булину после проверки каждого условия, то так и пиши.

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

select count(*)
  from dba_objects,
       (select level || 'LVL' agent_name from dual connect by level < 40) mp
 where case
         when 1 = 1 then 1
         when 1 = 2 then case when instr(agent_name,OBJECT_NAME) > 0 then 2 
                              when instr(agent_name,OBJECT_NAME) = 0 then 3
                              when instr(agent_name,OBJECT_NAME) < 0 then 4
                                                                     else 0 
                                                                     end
         else 0
       end = 1;

...

Рейтинг:

0 / 0

06.08.2020, 13:44

| Ответить | Цитировать | Написать

Определить порядок предикатов в oracle

#39987309

Pavel_PV

Гость

Ребята, спасибо что по делу!

xtender, я понял о чем ты. Блин, возможно пример аналогичный я не совсем удачный привёл. Т.е. суть в том, что да получается тут instr не вызывается, но бегло я не смог объяснить откуда получился такой лаг во времени. Если убрать instr, то у меня запрос обрабатывается 0.5сек, а с ним уже 14сек. Но в этом случае наверное дело именно в просто "транспортировке" 2х колонок и не важно, что дальше они не нужны.

Dshedoo, хех. Ну да хитро, только тут немного так сказать меняется суть. В твоем случае нужно 1=2 заменить на 2=2, чтобы именно показать что со вторым условием время существенно изменяется:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.

 select count(*)
  from dba_objects,
       (select level || 'LVL' agent_name from dual connect by level < 40) mp
 where case
         when 1 = 1 then 1
         when 2 = 2 then case when instr(agent_name,OBJECT_NAME) > 0 
                              then 2 end
         else 0
       end = 1;

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

...

Рейтинг:

0 / 0

07.08.2020, 04:37

| Ответить | Цитировать | Написать

Определить порядок предикатов в oracle

#39987452

andreymx

Участник

Откуда: Запорожье

Сообщения: 56 486

Рейтинг: 0 / 0

на оракле 11.2

какие-то другие результаты?

Код: sql

SELECT COUNT(*)
  FROM all_objects,
       (SELECT LEVEL || 'LVL' agent_name FROM dual connect BY LEVEL < 40) mp
2435550
3 сек

SELECT COUNT(*)
  FROM all_objects,
       (SELECT LEVEL || 'LVL' agent_name FROM dual connect BY LEVEL < 40) mp
 WHERE CASE
         when 1 = 1 then           1
         WHEN 1 = 2              and 
              INSTR(agent_name,                        OBJECT_NAME) > 0 
              THEN 2
         ELSE 0
       end = 1;
2435550
3 сек

SELECT COUNT(*)
  FROM all_objects,
       (SELECT LEVEL || 'LVL' agent_name FROM dual connect BY LEVEL < 40) mp
 WHERE CASE
         when 1 = 1 then           1
         WHEN --1 = 2              and 
              INSTR(agent_name,                        OBJECT_NAME) > 0 
              THEN 2
         ELSE 0
       end = 1;
2435550
3 сек

SELECT COUNT(*)
  FROM all_objects,
       (SELECT LEVEL || 'LVL' agent_name FROM dual connect BY LEVEL < 40) mp
 WHERE CASE
         when 1 = 1 then           1
         WHEN 1 = 2              /*and 
              INSTR(agent_name,                        OBJECT_NAME) > 0*/ 
              THEN 2
         ELSE 0
       end = 1;
2435550
3 сек

SELECT COUNT(*)
  FROM all_objects,
       (SELECT LEVEL || 'LVL' agent_name FROM dual connect BY LEVEL < 40) mp
 WHERE CASE
         when 1 = 1 then           1
         /*WHEN --1 = 2              and 
              INSTR(agent_name,                        OBJECT_NAME) > 0 
              THEN 2
         ELSE 0*/
       end = 1;
2435550
3 сек

SELECT COUNT(*)
  FROM all_objects,
       (SELECT LEVEL || 'LVL' agent_name FROM dual connect BY LEVEL < 40) mp
 WHERE CASE
         --when 1 = 1 then           1
         WHEN --1 = 2              and 
              INSTR(agent_name,                        OBJECT_NAME) > 0 
              THEN 2
         ELSE 0
       end = 1;
2435550
0.5 сек

...

Рейтинг:

0 / 0

07.08.2020, 13:41

| Ответить | Цитировать | Написать

Определить порядок предикатов в oracle

#39987531

booby

Участник

Сообщения: 2 667

Рейтинг: 1 / 0

andreymx

на оракле 11.2

какие-то другие результаты?
...

почему бы им и не быть другими,
если, например, "в отсутствии instr" у него, например, join elimination возникает, а в присутствии нет.

И вообще, в условиях, когда "case обрабатывает как единый целый организм",
может быть вообще всё, что угодно.

...

Рейтинг:

0 / 0

07.08.2020, 15:37

| Ответить | Цитировать | Написать

11 сообщений из 11, страница 1 из 1

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Определить порядок предикатов в oracle

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=52&msg=39987452&tid=1880996]:	0ms
get settings:	9ms
get forum list:	13ms
check forum access:	3ms
check topic access:	3ms
track hit:	61ms
get topic data:	12ms
get forum data:	2ms
get page messages:	49ms
get tp. blocked users:	1ms
others:	318ms

total:	471ms