ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Переписать запрос
25 сообщений из 27, страница 1 из 2
  1  все
Переписать запрос
    #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:38
| Ответить | Цитировать | Написать  
Переписать запрос
    #39435136
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
В данном примере (если ты юзаешь действительно case, а не какую-то свою самонаписанную функцию) case является частью языка SQL и никаких значимых тормозов в общее время не вносит (можно конечно поспорить, что в машинных кодах это выглядит по-разному)
...
Рейтинг: 0 / 0
08.04.2017, 09:45
| Ответить | Цитировать | Написать  
Переписать запрос
    #39435137
Rouga
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Rouga
Гость
Вячеслав Любомудров, конечно на нынешних скоростях разницы между обоими вариантами нет,
хотя первый вариант будет более затратрым имхо если оракл вдруг внутрях сам не оптимизирует и не вынесет кейс по f1 на уровень всей строки.
То вопрос наверно больше в том, можно ли вынести f1 на уровень строки, не разбивая его на части по примеру второго варианта.
...
Рейтинг: 0 / 0
08.04.2017, 09:54
| Ответить | Цитировать | Написать  
Переписать запрос
    #39435143
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
Разница между обоими вариантами есть и весьма существенная (те самые 2 прохода)
Не надо бояться использовать SQL-конструкции (выражения, а даже не функции) в SQL-запросе
...
Рейтинг: 0 / 0
08.04.2017, 11:09
| Ответить | Цитировать | Написать  
Переписать запрос
    #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
08.04.2017, 12:32
| Ответить | Цитировать | Написать  
Переписать запрос
    #39435677
Vint
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vint
Участник
JaRo,
а не проще себе сразу по колокольчикам ударить?
в первом примере посмотри план. на что твой or может иногда разложиться. и иногда... это будет не самая лучшая производительность. тем более изначальных условий where автор не привел, но они на 99% есть в реальном примере.
во втором примере если везде будет null то и сумма будет null что закономерно может привести к неправильной интерпритации на клиенте. когда действительно надо будет увидеть 0.
думай прежде чем такие советы давать.
...
Рейтинг: 0 / 0
10.04.2017, 10:34
| Ответить | Цитировать | Написать  
Переписать запрос
    #39435753
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Vint,

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

Может быть по-разному, если и развернёт, то возможно так и оптимальнее будет. Всё зависит от конкретностей.
...
Рейтинг: 0 / 0
10.04.2017, 12:23
| Ответить | Цитировать | Написать  
Переписать запрос
    #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:39
| Ответить | Цитировать | Написать  
Переписать запрос
    #39435773
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
dbms_photoshopКасательно кейсов, можно влиять на скорость меняя порядок.
Особеннно если одни предикаты "легче" других.

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

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

В первом случае power вычисляется для id < 4, во втором случае - всегда.Совпадение.
Short-circuit evaluation гарантировано только для веток case-а.
Внитри compound condition - нигде не гарантирован порядок вычисления.Да, это обсуждалось здесь неоднократно.
Но есть то, что есть.
...
Рейтинг: 0 / 0
10.04.2017, 13:19
| Ответить | Цитировать | Написать  
Переписать запрос
    #39435798
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
dbms_photoshopНо есть то, что есть.Это крайне отвратительный принцип: "Что вижу - то пою"
...
Рейтинг: 0 / 0
10.04.2017, 13:36
| Ответить | Цитировать | Написать  
Переписать запрос
    #39435802
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
…тем более, что есть способ гарантировать.
...
Рейтинг: 0 / 0
10.04.2017, 13:37
| Ответить | Цитировать | Написать  
Переписать запрос
    #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, 13:42
| Ответить | Цитировать | Написать  
Переписать запрос
    #39435829
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
dbms_photoshopОзнакомление с твоей системой ценностей очень ценно для меня, спасибо. :)Т.е. ты не только используешь, но и проповедуешь шаткий говнокод?
...
Рейтинг: 0 / 0
10.04.2017, 14:01
| Ответить | Цитировать | Написать  
Переписать запрос
    #39435846
Фотография AmKad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AmKad
Участник
ElicТ.е. ты не только используешь, но и проповедуешь шаткий говнокод?По-моему ты несколько передергиваешь, называя это говнокодом. Если смотреть с точки зрения, что порядок вычисления условий не определен, то без разницы, какое условие впаять первым, а какое вторым. Ну а если оптимизатор посчитает их в порядке следования, то фотошоп получит выигрыш, пусть даже и незначительный.
...
Рейтинг: 0 / 0
10.04.2017, 14:15
| Ответить | Цитировать | Написать  
Переписать запрос
    #39435858
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
AmKadПо-моему ты несколько передергиваешь, называя это говнокодом.Возможно. С точки зрения намеренной перестановки предикатов только ради надежды на большую вероятность слева-направо - это не говнокод, а бессмысленная экономия на эфемерных спичках.
...
Рейтинг: 0 / 0
10.04.2017, 14:33
| Ответить | Цитировать | Написать  
Переписать запрос
    #39435880
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Elic,

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

После озвучивания подобного, вопрошающий может прийти к определенным выводам, а может не прийти.
Твои желчные комментарии выглядят несколько нелепо так же как и making conclusions based on assumptions.
...
Рейтинг: 0 / 0
10.04.2017, 14:48
| Ответить | Цитировать | Написать  
Переписать запрос
    #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:32
| Ответить | Цитировать | Написать  
Переписать запрос
    #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:44
| Ответить | Цитировать | Написать  
Переписать запрос
    #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, 16:49
| Ответить | Цитировать | Написать  
Переписать запрос
    #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
10.04.2017, 17:27
| Ответить | Цитировать | Написать  
25 сообщений из 27, страница 1 из 2
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Переписать запрос
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39435811&tid=1886108]:
 0ms
get settings:
 7ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 152ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 62ms
get tp. blocked users:
 1ms
others: 216ms
total:  471ms
созд. / загр.: 471ms
x
x
Закрыть


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