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

Откуда:
Сообщений: 351
Есть запрос для отчета сверки:
+
select 
 a1.mag,
 sum(a1.prih_do)+sum(a1.vozv_do)-sum(a1.rash_do)-sum(a1.prod_do)-sum(a1.rash_vper)+sum(a1.prih_vper)-sum(a1.prod_vper)+sum(a1.vozv_vper) ostss
from
(select 
  decode(f.salelocationfrom,-2,f.salelocationto,f.salelocationfrom) mag,
  case when f.saledate<to_date('01.11.2017','DD.MM.YYYY') then
   sum(decode(f.saletype,'WO',f.primecost,0)) else 0 end rash_do,
  case when f.saledate<to_date('01.11.2017','DD.MM.YYYY') then
   sum(decode(f.saletype,'WI',f.primecost,0)) else 0 end prih_do,
  case when f.saledate<to_date('01.11.2017','DD.MM.YYYY') then
   sum(decode(f.saletype,'CS',f.primecost,0)) else 0 end prod_do,
  case when f.saledate<to_date('01.11.2017','DD.MM.YYYY') then
   sum(decode(f.saletype,'CR',f.primecost,0)) else 0 end vozv_do,
  case when f.saledate between to_date('01.11.2017','DD.MM.YYYY') and to_date('30.11.2017','DD.MM.YYYY') then
   sum(decode(f.saletype,'WO',f.primecost,0)) else 0 end rash_vper,
  case when f.saledate between to_date('01.11.2017','DD.MM.YYYY') and to_date('30.11.2017','DD.MM.YYYY') then
   sum(decode(f.saletype,'WI',f.primecost,0)) else 0 end prih_vper,
  case when f.saledate between to_date('01.11.2017','DD.MM.YYYY') and to_date('30.11.2017','DD.MM.YYYY') then
   sum(decode(f.saletype,'CS',f.primecost,0)) else 0 end prod_vper,
  case when f.saledate between to_date('01.11.2017','DD.MM.YYYY') and to_date('30.11.2017','DD.MM.YYYY') 
  then sum(decode(f.saletype,'CR',f.primecost,0)) else 0 end vozv_vper,
  case when f.saledate between to_date('01.11.2017','DD.MM.YYYY') and to_date('30.11.2017','DD.MM.YYYY') then
   sum(decode(f.saletype,'CS',f.salesum,0)) else 0 end prod_vper3,
  case when f.saledate between to_date('01.11.2017','DD.MM.YYYY') and to_date('30.11.2017','DD.MM.YYYY') 
  then  sum(decode(f.saletype,'CR',f.salesum,0)) else 0 end vozv_vper3
from supermag.ffmaprep f
where f.rectype=1
 and f.saledate<=to_date('30.11.2017','DD.MM.YYYY')
 and decode(f.salelocationfrom,-2,f.salelocationto,f.salelocationfrom) in (111,112,120,121,122)
group by decode(f.salelocationfrom,-2,f.salelocationto,f.salelocationfrom), f.saledate) a1
group by a1.mag

Суммирует себестоимость за месяц и остатки за весь период по магазинам 111,112,120,121,122
Выполняется полным сканированием таблицы.
Таблица supermag.ffmaprep гигов десять.
Запрос выполняется минут 10 (диски ssd)
Можно как-то с помощью аналитических функций или других способов оптимизировать запрос?
(Oracle 10g)
7 дек 17, 10:28    [21013836]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Добрый Э - Эх
Guest
lYY,

запрос у тебя линейный. там оптимизировать совершенно нечего. тут, скорее, подход к получению нужных данных надо менять, а не запрос оптимизировать...
7 дек 17, 11:25    [21014144]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром началась. КЯЗ
Сообщений: 27502
lYY
по магазинам 111,112,120,121,122
Какой это процент от всех данных?
7 дек 17, 11:31    [21014188]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 4904
Добрый Э - Эх
lYY,

запрос у тебя линейный. там оптимизировать совершенно нечего. тут, скорее, подход к получению нужных данных надо менять, а не запрос оптимизировать...
Ну если он уходит в темп, то оптимизировать есть чего.
10 гигов за 10 минут на SSD это что-то не вполне адекватное.
7 дек 17, 11:32    [21014191]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
lYY
Member

Откуда:
Сообщений: 351
Elic
lYY
по магазинам 111,112,120,121,122
Какой это процент от всех данных?

там может быть любой список магазинов
7 дек 17, 11:33    [21014198]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
lYY
Member

Откуда:
Сообщений: 351
dbms_photoshop
10 гигов за 10 минут на SSD это что-то не вполне адекватное.

да, странно это
7 дек 17, 11:35    [21014213]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
lYY
Member

Откуда:
Сообщений: 351
Добрый Э - Эх
lYY,

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

Тут как обычно всё. Разработчики написали эту хрень, которая забивает весь ввод-вывод и им плевать. А с меня пользователи спрашивают, почему сервер тормозит?!
7 дек 17, 11:38    [21014228]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Dshedoo
Member

Откуда:
Сообщений: 103
Я бы предложил агрегировать данные на ежемесячной основе.
Затем уже выбирать из этих данных необходимые + месяц из supermag.ffmaprep

З.Ы.
f.saledate between to_date('01.11.2017','DD.MM.YYYY') and to_date('30.11.2017','DD.MM.YYYY')

=
f.saledate >= to_date('01.11.2017','DD.MM.YYYY')
where f.saledate<=to_date('30.11.2017','DD.MM.YYYY')
7 дек 17, 11:46    [21014262]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 626
lYY,

смотреть индексы какие есть, мож придется декоде переписать

или скажем для
'WI','CS','CR' тож раз посчитать на начало года

нет f.saledate>= поетому беда

.....
stax
7 дек 17, 11:51    [21014280]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
lYY
Member

Откуда:
Сообщений: 351
Dshedoo,

подумаем.

Уточнил объем таблицы, 48 Гб, 275 миллионов строк.
7 дек 17, 11:52    [21014282]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
lYY
Member

Откуда:
Сообщений: 351
индексы
create index SUPERMAG.FFMAPREP_ARTICLE on SUPERMAG.FFMAPREP (ARTICLE)
create index SUPERMAG.FFMAPREP_DOC on SUPERMAG.FFMAPREP (SALEID, SALETYPE, SALESPECITEM)
create index SUPERMAG.FFMAPREP_LOCFROM on SUPERMAG.FFMAPREP (SALELOCATIONFROM, SALEDATE)
create index SUPERMAG.FFMAPREP_LOCTO on SUPERMAG.FFMAPREP (SALELOCATIONTO, SALEDATE)
create index SUPERMAG.FFMAPREP_SALEDATE on SUPERMAG.FFMAPREP (SALEDATE)
create index SUPERMAG.FFMAPREP_SUPPLIER on SUPERMAG.FFMAPREP (INCOMECLIENTINDEX)
7 дек 17, 11:55    [21014299]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 626
lYY
индексы
create index SUPERMAG.FFMAPREP_ARTICLE on SUPERMAG.FFMAPREP (ARTICLE)
create index SUPERMAG.FFMAPREP_DOC on SUPERMAG.FFMAPREP (SALEID, SALETYPE, SALESPECITEM)
create index SUPERMAG.FFMAPREP_LOCFROM on SUPERMAG.FFMAPREP (SALELOCATIONFROM, SALEDATE)
create index SUPERMAG.FFMAPREP_LOCTO on SUPERMAG.FFMAPREP (SALELOCATIONTO, SALEDATE)
create index SUPERMAG.FFMAPREP_SALEDATE on SUPERMAG.FFMAPREP (SALEDATE)
create index SUPERMAG.FFMAPREP_SUPPLIER on SUPERMAG.FFMAPREP (INCOMECLIENTINDEX)


убрать decode(f.salelocationfrom,-2,f.salelocationto,f.salelocationfrom)

считать отдельно для f.salelocationfrom=-2 in f.salelocationto (...
union all
f.salelocationfrom<>-2 in f.salelocationfrom (...


ps
автор
BETWEEN

Оптимизатор всегда заменяет условие, использующее оператор
сравнения BETWEEN, эквивалентным условием, использующим
операторы сравнения <= и >=. Например, оптимизатор заменяет
первое из двух следующих условий на второе:

sal BETWEEN 2000 AND 3000

sal >= 2000 AND sal <= 3000


.....
stax
7 дек 17, 12:08    [21014379]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 4904
lYY
dbms_photoshop
10 гигов за 10 минут на SSD это что-то не вполне адекватное.

да, странно это
Версия какая?
Приложи в спойлер вывод
select dbms_sqltune.report_sql_monitor('твой SQL ID') from dual
7 дек 17, 12:14    [21014426]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Dshedoo
Member

Откуда:
Сообщений: 103
select 
 a1.mag,
 sum(a1.prih_do+a1.vozv_do-a1.rash_do-a1.prod_do-a1.rash_vper+a1.prih_vper-a1.prod_vper+a1.vozv_vper) ostss
from
(select 
  case when f.salelocationfrom = -2 then f.salelocationto else f.salelocationfrom end mag,
  case when f.saledate<to_date('01.11.2017','DD.MM.YYYY') and f.saletype = 'WO'
    then sum(f.primecost) else 0 end rash_do,
  case when f.saledate<to_date('01.11.2017','DD.MM.YYYY') and f.saletype = 'WI'
    then sum(f.primecost) else 0 end prih_do,   
  case when f.saledate<to_date('01.11.2017','DD.MM.YYYY') and f.saletype = 'CS'
    then sum(f.primecost) else 0 end prod_do,   
  case when f.saledate<to_date('01.11.2017','DD.MM.YYYY') and f.saletype = 'CR'
    then sum(f.primecost) else 0 end vozv_do,   
  case when f.saledate >= to_date('01.11.2017','DD.MM.YYYY') and f.saletype = 'WO'
    then sum(f.primecost) else 0 end rash_vper,   
  case when f.saledate >= to_date('01.11.2017','DD.MM.YYYY') and f.saletype = 'WI'
    then sum(f.primecost) else 0 end prih_vper,   
  case when f.saledate >= to_date('01.11.2017','DD.MM.YYYY') and f.saletype = 'CS'
    then sum(f.primecost) else 0 end prod_vper,   
  case when f.saledate >= to_date('01.11.2017','DD.MM.YYYY') and f.saletype = 'CR'
    then sum(f.primecost) else 0 end vozv_vper,    
  case when f.saledate >= to_date('01.11.2017','DD.MM.YYYY') and f.saletype = 'CS'
    then sum(f.salesum) else 0 end prod_vper3,   
  case when f.saledate >= to_date('01.11.2017','DD.MM.YYYY') and f.saletype = 'CR'
    then sum(f.salesum) else 0 end vozv_vper3    
from supermag.ffmaprep f
where f.rectype=1
 and f.saledate<=to_date('30.11.2017','DD.MM.YYYY')
 and f.saletype in ('WO','WI','CS','CR')
 and --case when f.salelocationfrom = -2 then f.salelocationto else f.salelocationfrom end in (111,112,120,121,122)
     (
      (f.salelocationfrom = -2 and f.salelocationto in (111,112,120,121,122))
     or
      (f.salelocationfrom != -2 and f.salelocationfrom in (111,112,120,121,122))
      )
      
group by case when f.salelocationfrom = -2 then f.salelocationto else f.salelocationfrom end, f.saledate) a1
group by a1.mag
7 дек 17, 12:36    [21014579]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Dshedoo
Member

Откуда:
Сообщений: 103
Dshedoo
     (
      (f.salelocationfrom = -2 and f.salelocationto in (111,112,120,121,122))
     or
      (f.salelocationfrom != -2 and f.salelocationfrom in (111,112,120,121,122))
      )


Dshedoo
     (
      (f.salelocationfrom = -2 and f.salelocationto in (111,112,120,121,122))
     or
      (f.salelocationfrom in (111,112,120,121,122))
      )
7 дек 17, 12:51    [21014702]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 626
Dshedoo
Dshedoo
     (
      (f.salelocationfrom = -2 and f.salelocationto in (111,112,120,121,122))
     or
      (f.salelocationfrom in (111,112,120,121,122))
      )


імхо плохо
f.salelocationfrom = -2 f.salelocationto = 999 f.salelocationfrom =111


ps
оракля юзает ф-цію
LNNVL(f.salelocationfrom = -2) and

....
stax
7 дек 17, 13:28    [21014906]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
MaximaXXL
Member

Откуда: Киев
Сообщений: 389
lYY,

Я сейчас что-то набросаю, но вот вопрос, А зачем делать group by по f.saledate если больше его не использовать?
7 дек 17, 13:58    [21015025]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Dshedoo
Member

Откуда:
Сообщений: 103
Dshedoo
select 
 a1.mag,
 sum(a1.prih_do+a1.vozv_do-a1.rash_do-a1.prod_do-a1.rash_vper+a1.prih_vper-a1.prod_vper+a1.vozv_vper) ostss
from
(select 
  case when f.salelocationfrom = -2 then f.salelocationto else f.salelocationfrom end mag,
  sum(case when f.saledate<to_date('01.11.2017','DD.MM.YYYY') and f.saletype = 'WO'
    then f.primecost else 0 end) rash_do,
  sum(case when f.saledate<to_date('01.11.2017','DD.MM.YYYY') and f.saletype = 'WI'
    then f.primecost else 0 end) prih_do,   
  sum(case when f.saledate<to_date('01.11.2017','DD.MM.YYYY') and f.saletype = 'CS'
    then f.primecost else 0 end) prod_do,   
  sum(case when f.saledate<to_date('01.11.2017','DD.MM.YYYY') and f.saletype = 'CR'
    then f.primecost else 0 end) vozv_do,   
  sum(case when f.saledate >= to_date('01.11.2017','DD.MM.YYYY') and f.saletype = 'WO'
    then f.primecost else 0 end) rash_vper,   
  sum(case when f.saledate >= to_date('01.11.2017','DD.MM.YYYY') and f.saletype = 'WI'
    then f.primecost else 0 end) prih_vper,   
  sum(case when f.saledate >= to_date('01.11.2017','DD.MM.YYYY') and f.saletype = 'CS'
    then f.primecost else 0 end) prod_vper,   
  sum(case when f.saledate >= to_date('01.11.2017','DD.MM.YYYY') and f.saletype = 'CR'
    then f.primecost else 0 end) vozv_vper,    
  sum(case when f.saledate >= to_date('01.11.2017','DD.MM.YYYY') and f.saletype = 'CS'
    then f.salesum else 0 end) prod_vper3,   
  sum(case when f.saledate >= to_date('01.11.2017','DD.MM.YYYY') and f.saletype = 'CR'
    then f.salesum else 0 end) vozv_vper3    
from supermag.ffmaprep f
where f.rectype=1
 and f.saledate<=to_date('30.11.2017','DD.MM.YYYY')
 and f.saletype in ('WO','WI','CS','CR')
 and 
--
--1)
    case when f.salelocationfrom = -2 then f.salelocationto else f.salelocationfrom end in (111,112,120,121,122)
--2)
     (
      (f.salelocationfrom = -2 and f.salelocationto in (111,112,120,121,122))
     or
      (f.salelocationfrom in (111,112,120,121,122))
      )
--
group by case when f.salelocationfrom = -2 then f.salelocationto else f.salelocationfrom end) a1
group by a1.mag


Так лучше?
7 дек 17, 14:03    [21015042]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
MaximaXXL
Member

Откуда: Киев
Сообщений: 389
lYY,

По быстрому получилось что-то такое:

select 
  decode(f.salelocationfrom,-2,f.salelocationto,f.salelocationfrom) mag,
  sum( case f.saletype when 'WO' then -1*f.primecost /*-1 rash_do_vper,*/
                       when 'WI' then    f.primecost /*+1 prih_do_vper,*/
                       when 'CS' then -1*f.primecost /*-1 prod_do_vper,*/
                       when 'CR' then    f.primecost /*+1 vozv_do_vper,*/
                       else 0 end 
                       ) ostss
from supermag.ffmaprep f
where f.rectype=1
 and f.saledate<=to_date('30.11.2017','DD.MM.YYYY')
 /*and decode(f.salelocationfrom,-2,f.salelocationto,f.salelocationfrom) in (111,112,120,121,122)*/
      (
      (f.salelocationfrom = -2 and f.salelocationto in (111,112,120,121,122))
     or
      (f.salelocationfrom in (111,112,120,121,122))
      )
group by decode(f.salelocationfrom,-2,f.salelocationto,f.salelocationfrom)


Хотя и не проверял =)
7 дек 17, 14:17    [21015104]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
lYY
Member

Откуда:
Сообщений: 351
MaximaXXL
lYY,

Я сейчас что-то набросаю, но вот вопрос, А зачем делать group by по f.saledate если больше его не использовать?

это не я писал, я админ просто)

Всем спасибо, я позже протестирую и по результатам отпишусь.
7 дек 17, 14:31    [21015187]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Dshedoo
Member

Откуда:
Сообщений: 103
MaximaXXL,

Else 0 подменить на where f.saletype in ('WO','WI','CS','CR')

А так прям перфекто)
7 дек 17, 14:54    [21015284]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
lYY
Member

Откуда:
Сообщений: 351
Взял исходный запрос на один магазин - 2 минуты, полное сканирование.
Сделал через union all, как выше посоветовали. Идет по индексам, выполняется 2 секунды.
Отправил разработчикам, путь тестируют.
7 дек 17, 15:00    [21015320]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
lYY
Member

Откуда:
Сообщений: 351
MaximaXXL
lYY,

По быстрому получилось что-то такое:

select 
  decode(f.salelocationfrom,-2,f.salelocationto,f.salelocationfrom) mag,
  sum( case f.saletype when 'WO' then -1*f.primecost /*-1 rash_do_vper,*/
                       when 'WI' then    f.primecost /*+1 prih_do_vper,*/
                       when 'CS' then -1*f.primecost /*-1 prod_do_vper,*/
                       when 'CR' then    f.primecost /*+1 vozv_do_vper,*/
                       else 0 end 
                       ) ostss
from supermag.ffmaprep f
where f.rectype=1
 and f.saledate<=to_date('30.11.2017','DD.MM.YYYY')
 /*and decode(f.salelocationfrom,-2,f.salelocationto,f.salelocationfrom) in (111,112,120,121,122)*/
      (
      (f.salelocationfrom = -2 and f.salelocationto in (111,112,120,121,122))
     or
      (f.salelocationfrom in (111,112,120,121,122))
      )
group by decode(f.salelocationfrom,-2,f.salelocationto,f.salelocationfrom)


Хотя и не проверял =)


Не проходит вот эта строка:
(f.salelocationfrom = -2 and f.salelocationto in (120))
7 дек 17, 15:03    [21015342]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
lYY
Member

Откуда:
Сообщений: 351
вот так работает:
+
select 
  decode(f.salelocationfrom,-2,f.salelocationto,f.salelocationfrom) mag,
  sum( case f.saletype when 'WO' then -1*f.primecost /*-1 rash_do_vper,*/
                       when 'WI' then    f.primecost /*+1 prih_do_vper,*/
                       when 'CS' then -1*f.primecost /*-1 prod_do_vper,*/
                       when 'CR' then    f.primecost /*+1 vozv_do_vper,*/
                       else 0 end 
                       ) ostss
from supermag.ffmaprep f
where f.rectype=1
 and f.saledate<=to_date('30.11.2017','DD.MM.YYYY')
 /*and decode(f.salelocationfrom,-2,f.salelocationto,f.salelocationfrom) in (111,112,120,121,122)*/
 and
      (
      (f.salelocationfrom = -2 and f.salelocationto in (120))
     or
      (f.salelocationfrom in (120))
      )
group by decode(f.salelocationfrom,-2,f.salelocationto,f.salelocationfrom)

но дольше, 86 секунд, идет bitmap convertion
7 дек 17, 15:08    [21015371]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
MaximaXXL
Member

Откуда: Киев
Сообщений: 389
Dshedoo,

Там есть куда руки приложить ...
else 0 можно игнорить ... все равно null под суммой будет проигнорирован.
если добавлять
f.saletype in ('WO','WI','CS','CR')

то тогда уже так:
select 
  decode(f.salelocationfrom,-2,f.salelocationto,f.salelocationfrom) mag,
  sum(decode(f.saletype, 'WO', -1, 'CS', -1 , 1)*f.primecost) ostss

from supermag.ffmaprep f
where f.rectype=1
 and f.saledate<=to_date('30.11.2017','DD.MM.YYYY')
 and f.saletype in ('WO','WI','CS','CR')
 /*and decode(f.salelocationfrom,-2,f.salelocationto,f.salelocationfrom) in (111,112,120,121,122)*/
      (
      (f.salelocationfrom = -2 and f.salelocationto in (111,112,120,121,122))
     or
      (f.salelocationfrom in (111,112,120,121,122))
      )
group by decode(f.salelocationfrom,-2,f.salelocationto,f.salelocationfrom)


Хотя так я бы прокоментировал что хотел сказать автор
7 дек 17, 15:08    [21015372]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Oracle Ответить