Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Переписать запрос / 25 сообщений из 27, страница 1 из 2
  1  все
08.04.2017, 09:38
    #39435135
Rouga
Rouga
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Переписать запрос
Упрощенный пример, в реальность кейсы больше по количеству условий и полей в районе сотни.
В результате с кейсами получается огромная простыня.
Код: 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.
create table test_tab
as
select round(dbms_random.value(1, 2)) f1,
       round(dbms_random.value(1, 1000)) f2,
       round(dbms_random.value(1, 1000)) f3,       
       round(dbms_random.value(1, 1000)) f4
  from dual
  connect by level < 1000001;

-- вариант 1  
select sum(case when f1 = 1 and f2 between 1 and 100 then f2 else 0 end) f2_1,       
       sum(case when f1 = 1 and f3 between 1 and 100 then f3 else 0 end) f3_1,
       sum(case when f1 = 1 and f4 between 1 and 100 then f4 else 0 end) f4_1,
       --         
       sum(case when f1 = 2 and f2 between 1 and 100 then f2 else 0 end) f2_2,
       sum(case when f1 = 2 and f3 between 1 and 100 then f3 else 0 end) f3_2,
       sum(case when f1 = 2 and f4 between 1 and 100 then f4 else 0 end) f4_2
  from test_tab;

-- вариант 2
select sum(f2_1) f2_1,       
       sum(f3_1) f3_1,
       sum(f4_1) f4_1,
       --         
       sum(f2_2) f2_2,
       sum(f3_2) f3_2,
       sum(f4_2) f4_2
  from (select case when f2 between 1 and 100 then f2 else 0 end f2_1,       
               case when f3 between 1 and 100 then f3 else 0 end f3_1,
               case when f4 between 1 and 100 then f4 else 0 end f4_1,
               --         
               0 f2_2,
               0 f3_2,
               0 f4_2
          from test_tab    
         where f1 = 1
         union all
        select 0 f2_1,       
               0 f3_1,
               0 f4_1,
               --         
               sum(case when f2 between 1 and 100 then f2 else 0 end) f2_2,
               sum(case when f3 between 1 and 100 then f3 else 0 end) f3_2,
               sum(case when f4 between 1 and 100 then f4 else 0 end) f4_2
          from test_tab    
         where f1 = 2);


В первом варианте кейс по f1 выполняется для каждого поля строки (возможно, оракл понимает и оптимизирует это, но сильно не уверен). В итоге, т.к. в реальности кейсы сложнее, если писать одно и то же условие для каждого поля, получается каша (по читаемости).
Во втором варианте мы выносим условие по f1 из кейсов, но тогда разрастается сам запрос ну и два прохода.
Рассматривал еще через xml, но тут быстродействие вообще падает, да и потом доставать еще из xml придется.
Вдруг есть еще варианты залезть на елку, чтобы и запрос был читаемый без дублирования кейсов и оптимально работал?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.04.2017, 09:45
    #39435136
Вячеслав Любомудров
Вячеслав Любомудров
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Переписать запрос
В данном примере (если ты юзаешь действительно case, а не какую-то свою самонаписанную функцию) case является частью языка SQL и никаких значимых тормозов в общее время не вносит (можно конечно поспорить, что в машинных кодах это выглядит по-разному)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.04.2017, 09:54
    #39435137
Rouga
Rouga
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Переписать запрос
Вячеслав Любомудров, конечно на нынешних скоростях разницы между обоими вариантами нет,
хотя первый вариант будет более затратрым имхо если оракл вдруг внутрях сам не оптимизирует и не вынесет кейс по f1 на уровень всей строки.
То вопрос наверно больше в том, можно ли вынести f1 на уровень строки, не разбивая его на части по примеру второго варианта.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.04.2017, 11:09
    #39435143
Вячеслав Любомудров
Вячеслав Любомудров
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Переписать запрос
Разница между обоими вариантами есть и весьма существенная (те самые 2 прохода)
Не надо бояться использовать SQL-конструкции (выражения, а даже не функции) в SQL-запросе
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.04.2017, 12:32
    #39435166
JaRo
JaRo
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Переписать запрос
Ну по мелочи (хотя может и не по мелочи, зависит от данных) напрашивается для той самой производительности в 1ом варианте не бояться расширять where. Если брать ваш пример, то
Код: plsql
1.
2.
3.
 from test_tab    
 where f1 in (1,2)
    and (f2 between 1 and 100 or f3 between 1 and 100 or f4 between 1 and 100) -- ну или как у вас в реальности условия выглядят


Вот тут реально можно помочь Ораклу - особенно, если запросом учитывается только малая часть данных, как в приведенном вами pivot.
PS: Ну и можно чуток сэкономить и на тексте :)
Код: plsql
1.
sum(case when f1 = 1 and f2 between 1 and 100 then f2/* else 0*/ end
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.04.2017, 10:34
    #39435677
Vint
Vint
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Переписать запрос
JaRo,
а не проще себе сразу по колокольчикам ударить?
в первом примере посмотри план. на что твой or может иногда разложиться. и иногда... это будет не самая лучшая производительность. тем более изначальных условий where автор не привел, но они на 99% есть в реальном примере.
во втором примере если везде будет null то и сумма будет null что закономерно может привести к неправильной интерпритации на клиенте. когда действительно надо будет увидеть 0.
думай прежде чем такие советы давать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.04.2017, 12:21
    #39435753
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Переписать запрос
Vint,

JARO девушка и весьма толковая, не надо ее обижать. Ну посмешила чуток, бывает.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.04.2017, 12:22
    #39435754
Nobody1111
Nobody1111
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Переписать запрос
У меня на сервере вью с где-то близким синтаксисом компилируется часов 5-6 (так и на 11.2 было, и на 12.1). Работает нормально, пользователи не жалуются.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.04.2017, 12:23
    #39435755
JaRo
JaRo
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Переписать запрос
Vint,
выплеснул - полегчало?

Может быть по-разному, если и развернёт, то возможно так и оптимальнее будет. Всё зависит от конкретностей.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.04.2017, 12:39
    #39435762
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Переписать запрос
dbms_photoshopпос п ешилаFixed.

Rouga,
Касательно кейсов, можно влиять на скорость меняя порядок.
Особеннно если одни предикаты "легче" других.

В первом случае power вычисляется для id < 4, во втором случае - всегда.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
SQL> with t as
  2  (select rownum id from dual connect by level <= 5)
  3  select id, case when id < 4 and power(2, id + 415) > 999 then 1 end x
  4  from t;

        ID          X
---------- ----------
         1          1
         2          1
         3          1
         4
         5

SQL> with t as
  2  (select rownum id from dual connect by level <= 5)
  3  select id, case when power(2, id + 415) > 999 and id < 4 then 1 end x
  4  from t;
ERROR:
ORA-01426: numeric overflow
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.04.2017, 12:59
    #39435773
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Переписать запрос
dbms_photoshopКасательно кейсов, можно влиять на скорость меняя порядок.
Особеннно если одни предикаты "легче" других.

В первом случае power вычисляется для id < 4, во втором случае - всегда.Совпадение.
Short-circuit evaluation гарантировано только для веток case-а.
Внитри compound condition - нигде не гарантирован порядок вычисления.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.04.2017, 13:07
    #39435777
Vint
Vint
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Переписать запрос
dbms_photoshop,
да и не думал, но толковости в глупых советах не увидел.

JaRo,
прежде чем следующий раз давать советы опиши ограничения. а так оба совета бесполезны...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.04.2017, 13:19
    #39435782
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Переписать запрос
Elicdbms_photoshopКасательно кейсов, можно влиять на скорость меняя порядок.
Особеннно если одни предикаты "легче" других.

В первом случае power вычисляется для id < 4, во втором случае - всегда.Совпадение.
Short-circuit evaluation гарантировано только для веток case-а.
Внитри compound condition - нигде не гарантирован порядок вычисления.Да, это обсуждалось здесь неоднократно.
Но есть то, что есть.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.04.2017, 13:36
    #39435798
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Переписать запрос
dbms_photoshopНо есть то, что есть.Это крайне отвратительный принцип: "Что вижу - то пою"
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.04.2017, 13:37
    #39435802
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Переписать запрос
…тем более, что есть способ гарантировать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.04.2017, 13:42
    #39435811
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Переписать запрос
Elicdbms_photoshopНо есть то, что есть.Это крайне отвратительный принцип: "Что вижу - то пою"Ознакомление с твоей системой ценностей очень ценно для меня, спасибо. :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.04.2017, 13:42
    #39435814
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Переписать запрос
JaRoНу по мелочи (хотя может и не по мелочи, зависит от данных) напрашивается для той самой производительности в 1ом варианте не бояться расширять where. Если брать ваш пример, то
Код: plsql
1.
2.
3.
 from test_tab    
 where f1 in (1,2)
    and (f2 between 1 and 100 or f3 between 1 and 100 or f4 between 1 and 100) -- ну или как у вас в реальности условия выглядят

Отфильтровать, выкинуть все, что не нужно - этой действительно то, с чего нужно начать в данном случае. Ведь по сути это общее правило написания SQL-запросов. А если оптимизатор решит сделать concatenation и это будет казаться неоптимальным, то можно прибить гвоздями хинтами. Правда, если будет table fullscan, с учетом того, что следующий за фильтрацией шаг - sort aggregate, выигрыш от where-фильтрации может быть незначительным.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.04.2017, 14:01
    #39435829
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Переписать запрос
dbms_photoshopОзнакомление с твоей системой ценностей очень ценно для меня, спасибо. :)Т.е. ты не только используешь, но и проповедуешь шаткий говнокод?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.04.2017, 14:15
    #39435846
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Переписать запрос
ElicТ.е. ты не только используешь, но и проповедуешь шаткий говнокод?По-моему ты несколько передергиваешь, называя это говнокодом. Если смотреть с точки зрения, что порядок вычисления условий не определен, то без разницы, какое условие впаять первым, а какое вторым. Ну а если оптимизатор посчитает их в порядке следования, то фотошоп получит выигрыш, пусть даже и незначительный.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.04.2017, 14:33
    #39435858
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Переписать запрос
AmKadПо-моему ты несколько передергиваешь, называя это говнокодом.Возможно. С точки зрения намеренной перестановки предикатов только ради надежды на большую вероятность слева-направо - это не говнокод, а бессмысленная экономия на эфемерных спичках.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.04.2017, 14:48
    #39435880
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Переписать запрос
Elic,

Иногда я даю просто другим пищу для размышления.
Вот так же как тут Есть ли хинты оптимизации указывающие порядок применения условий во фразе where?

После озвучивания подобного, вопрошающий может прийти к определенным выводам, а может не прийти.
Твои желчные комментарии выглядят несколько нелепо так же как и making conclusions based on assumptions.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.04.2017, 16:32
    #39436007
MaximaXXL
MaximaXXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Переписать запрос
Rouga,

можно еще через pivot попробовать

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
select *
from (
select f1||'X' X f2 n
from test_tab where f2 between 1 and 100
union all
select f1||'Y' X f3 n
from test_tab where f3 between 1 and 100
union all
select f1||'Z' X f4 n
from test_tab where f4 between 1 and 100
)
pivot( sum(n)
for X in ('1X' "f2_1", '1Y' "f3_1",'1Z' "f4_1",'2X' "f2_2",'2Y' "f3_2",'2Z' "f4_2")
)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.04.2017, 16:44
    #39436016
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Переписать запрос
MaximaXXLRouga,

можно еще через pivot попробовать

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
select *
from (
select f1||'X' X f2 n
from test_tab where f2 between 1 and 100
union all
select f1||'Y' X f3 n
from test_tab where f3 between 1 and 100
union all
select f1||'Z' X f4 n
from test_tab where f4 between 1 and 100
)
pivot( sum(n)
for X in ('1X' "f2_1", '1Y' "f3_1",'1Z' "f4_1",'2X' "f2_2",'2Y' "f3_2",'2Z' "f4_2")
)



А куда дeлись остальные строки?

SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.04.2017, 16:49
    #39436021
MaximaXXL
MaximaXXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Переписать запрос
SYMaximaXXLRouga,

можно еще через pivot попробовать

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
select *
from (
select f1||'X' X f2 n
from test_tab where f2 between 1 and 100
union all
select f1||'Y' X f3 n
from test_tab where f3 between 1 and 100
union all
select f1||'Z' X f4 n
from test_tab where f4 between 1 and 100
)
pivot( sum(n)
for X in ('1X' "f2_1", '1Y' "f3_1",'1Z' "f4_1",'2X' "f2_2",'2Y' "f3_2",'2Z' "f4_2")
)



А куда дeлись остальные строки?

SY.

по желанию заказчика (Rouga) их должно быть 6.
ну и пропустил "," в структуре
Код: plsql
1.
select f1||'X' X f2 n

т.о. корректный вариант
Код: plsql
1.
select f1||'X' X, f2 n
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.04.2017, 17:27
    #39436046
ora601
ora601
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Переписать запрос
RougaВдруг есть еще варианты залезть на елку, чтобы и запрос был читаемый без дублирования кейсов и оптимально работал?

Можно через функцию, если данных не много :

Код: plsql
1.
2.
3.
4.
5.
6.
7.
WITH
  FUNCTION f_case_func(p1 IN NUMBER, p2 IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN CASE WHEN p1 BETWEEN 1 AND 100 THEN p1 ELSE p2 END;
  END;
SELECT SUM(f_case_func(level, level/2)) FROM DUAL 
CONNECT BY LEVEL <500000;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Переписать запрос / 25 сообщений из 27, страница 1 из 2
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 639ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1886108]:
 0ms
get settings:
 11ms
get forum list:
 19ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 236ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 82ms
get tp. blocked users:
 1ms
others: 268ms
total:  639ms
x
x
Закрыть


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