Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Определить порядок предикатов в oracle / 11 сообщений из 11, страница 1 из 1
06.08.2020, 10:58
    #39986987
Pavel_PV
Pavel_PV
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить порядок предикатов в oracle
Всем привет!
Делал тут одну задачку и что-то подвис. Соль в том, что оракл 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, 11:04
    #39986993
Вячеслав Любомудров
Вячеслав Любомудров
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить порядок предикатов в oracle
Был древний хинт ORDERED_PREDICATE
Но с представлиниями он может не дать ожидаемого результата
Еще есть всякие rownum>100000000, materialized и т.п.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.08.2020, 11:15
    #39986997
Pavel_PV
Pavel_PV
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить порядок предикатов в oracle
Вячеслав Любомудров, ORDERED_PREDICATE я первым попробовал, но...А что касается materialized и т.п. они тут профита точно не дадут т.к. тогда нужна обертка через with. Про rownum не понял если честно, но думаю это из другой оперы.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.08.2020, 11:23
    #39987003
Вячеслав Любомудров
Вячеслав Любомудров
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить порядок предикатов в oracle
Любой подзапрос можно оформить с materialized
Ограничение по rownum запрещает слияние условий основного запроса с представлением, что, как обычно, и приводит к неоптимальным планам

with -- оно только для красоты, select from (select) мало чем отличается
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.08.2020, 12:28
    #39987036
Pavel_PV
Pavel_PV
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить порядок предикатов в oracle
Вячеслав Любомудров, ну конечно имелось ввиду без использования with или подзапросов. Смысл materialized в материализации запроса, без конструкции with или подзапросов особого смысла в ней не вижу. Хотя написать вам никто не мешает.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.08.2020, 12:50
    #39987048
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить порядок предикатов в oracle
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, 13:31
    #39987078
Dshedoo
Dshedoo
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить порядок предикатов в oracle
Если нужно получить ответ уже после первого условия, то нет смысла указывать несколько условий в качестве одного.

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

Код: 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:44
    #39987086
Dshedoo
Dshedoo
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить порядок предикатов в oracle
Офк основной 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
| Ответить | Цитировать | Написать  
07.08.2020, 04:37
    #39987309
Pavel_PV
Pavel_PV
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить порядок предикатов в oracle
Ребята, спасибо что по делу!

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, 13:41
    #39987452
andreymx
andreymx
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить порядок предикатов в oracle
на оракле 11.2

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

Код: sql
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.
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, 15:37
    #39987531
booby
booby
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Определить порядок предикатов в oracle
andreymx
на оракле 11.2

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

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

И вообще, в условиях, когда "case обрабатывает как единый целый организм",
может быть вообще всё, что угодно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Определить порядок предикатов в oracle / 11 сообщений из 11, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 415ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1880996]:
 0ms
get settings:
 9ms
get forum list:
 10ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 25ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 42ms
get tp. blocked users:
 1ms
others: 312ms
total:  415ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]