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

Откуда:
Сообщений: 179
Есть следующий запрос
 SELECT 
     dt as "Дата",
     val as "Валюта",
     sum(case  when ( term <= 1 and sumexp = 0) then sums
           else 0
      end) as "Сумма до 1 года",       
     sum(case  when ( term <= 1 and sumexp = 0) then 1
           else 0
      end) as "Кол-во до 1 года",
     sum(case when (term > 1 and term <= 2 and sumexp = 0) then sums
           else 0
      end) as "Сумма до 2 лет",
     sum(case when (term > 1 and term <= 2 and sumexp = 0) then 1
           else 0
      end) as "Кол-во до 2 лет",
     sum(case when (term > 2 and term <= 3 and sumexp = 0) then sums
           else 0
      end) as "Сумма до трех лет",
     sum(case when (term > 2 and term <= 3 and sumexp = 0) then 1
           else 0
      end) as "Кол-во до 3 лет",
     sum(case when (term > 3 ) then sums
           else 0
      end) as "Сумма свыше 3 лет",
     sum(case when (term > 3 ) then 1
           else 0
      end) as "Кол-во свыше 3 лет",
     sum(case when (sumexp > 0) then sumexp
           else 0
      end) as "Сумма по должникам",
     sum(case when (sumexp > 0) then 1
           else 0
      end) as "Кол-во по должникам",
     sum(sums + sumexp) as "Сумма всего",
     count(1) as "Кол-во всего"
from (        
        SELECT
          func(t.id,'Получить сумму задолженности') as sumexp,
          func(t.id,'Сумму проплаты') as sums,
          t.val,
          (t.DTTERM - d.dt)/365 as term ,
          d.dt
        FROM
              tables t,
              (select * from 'массив дат') D 
          WHERE  
            d.dt between t.dtopen and nvl(t.dtclose,d.dt) AND    
            (t.types = 1  OR t.types = 2 OR t.types = 3)  
) inner
group by 
dt,kv   
На рисунке ниже, то что получается.

Так вот подзапрос inner, в котором дергается долго выполняющаяся ф-ция func,
возвращает на одну дату (d.dt) 15 - 20 тыс. строк.
Получается огромное результирующее множество в подзапросе
inner, которое потом суммируется. И вот мне
сказали, что возможно(!) Oracle с трудом прохавывает это.
Так ли это? И как это оценить. Видимых тормозов сервера я не наблюдала.

К сообщению приложен файл. Размер - 0Kb
8 янв 08, 10:42    [5125302]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте запрос  [new]
Stax..
Guest
1) ф-ция не может возврвщать "15 - 20 тыс. строк"
2) ф-ция не запвисит от даты поетому вызывать на уровне tables
3) долгоіграющая ф-ция вызывыется ДВА раза (если есть возможность) преписать ф-цию
на ретурн обектного типа (два значения сразу)
.....
stax
8 янв 08, 12:50    [5125579]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте запрос  [new]
JUNIORik
Member

Откуда:
Сообщений: 179
Stax..
1) ф-ция не может возврвщать "15 - 20 тыс. строк"

Опаньки, а вы не внимательно читаете

подзапрос inner, ... ,
возвращает на одну дату (d.dt) 15 - 20 тыс. строк

Stax..
2) ф-ция не запвисит от даты поетому вызывать на уровне tables

Sorry,

func(t.id,d.dt,'Получить сумму задолженности') as sumexp,
func(t.id,d.dt,'Сумму проплаты') as sums,

Stax..
3) долгоіграющая ф-ция вызывыется ДВА раза (если есть возможность) преписать ф-цию
на ретурн обектного типа (два значения сразу)


Функцию переписывать нельзя, т.к. она написана давно Гуру.
А мне разрешено только строить отчет.

Есть еще какие-нибудь предложения.
8 янв 08, 13:48    [5125714]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте запрос  [new]
Евгений_25
Member

Откуда: Харьков
Сообщений: 460
Иногда бывает проще и легче для запроса раскрыть содержимое функции и применить напрямую его функционал в текущем запросе.
У меня было так что функция была универсальная и емкая, перебирались различные варианты, мне с нее нужен был кусочек, я выдрал необходимый кусок и применил к отчетному запросу что гораздо увеличило скорость выполнения запроса и расчета итоговых сумм
8 янв 08, 15:20    [5125917]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте запрос  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18343
JUNIORik
Функцию переписывать нельзя, т.к. она написана давно Гуру.

Не только давно, но и для других целей.
Прикручивать ее насильно в другой контекст - иметь проблемы.
Рассмотрите вариант редизайна/рефакторинга функции с тем, чтобы она отвечала в том числе и новым требованиям (например, написать новую табличную или pipelined, превратив оригинальную функцию в обертку для сохранения сложившегося интерфейса).
Если, конечно, вопросы эффективности вашего отчета вообще кого-либо интересуют.
8 янв 08, 15:30    [5125943]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте запрос  [new]
JUNIORik
Member

Откуда:
Сообщений: 179
Давайте оставим ф-ию в покое, переписывать я не буду (да и не реально это
переписать в один запрос;)

Меня в общем-то больше интерисует
огромное результирующее множество в подзапросе
inner
, которое потом суммируется.
Oracle с трудом прохавывает его?
Так ли это? И как это оценить.
8 янв 08, 15:42    [5125977]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте запрос  [new]
ps
Member

Откуда:
Сообщений: 503
А не пробовали trace создать, чтобы узнать наверняка, на что время тратится? На ваш SQL? или на выполнение функции?
8 янв 08, 15:46    [5125989]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте запрос  [new]
по русски пжалста.
Guest
JUNIORik
Давайте оставим ф-ию в покое, переписывать я не буду (да и не реально это
переписать в один запрос;)

Меня в общем-то больше интерисует
огромное результирующее множество в подзапросе
inner
, которое потом суммируется.
Oracle с трудом прохавывает его?
Так ли это? И как это оценить.

Что-что, простите?
set autotrace on / events 10046 level 8 - для оценок.
8 янв 08, 15:49    [5125999]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте запрос  [new]
JUNIORik
Member

Откуда:
Сообщений: 179
а У МЕНЯ НЕТ привилегии PLUSTRACE.
Админы в целях безопастности отрезают все по
максимуму.
И, скажите, как с этим жить?
8 янв 08, 16:46    [5126189]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте запрос  [new]
зы
Guest
JUNIORik
а У МЕНЯ НЕТ привилегии PLUSTRACE.
Админы в целях безопастности отрезают все по
максимуму.
И, скажите, как с этим жить?

сказать админам что бы сами трейсили, оптимизировали и рефакторили...
8 янв 08, 16:50    [5126202]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте запрос  [new]
ps
Member

Откуда:
Сообщений: 503
JUNIORik
Получается огромное результирующее множество в подзапросе inner, которое потом суммируется. И вот мне сказали, что возможно(!) Oracle с трудом прохавывает это.
Так ли это? И как это оценить.

Сначала к вопросу об оценке. Вам надо отделить время выполнения функции от времени агрегации результата подзапроса. Как вариант - создайте сначала таблицу на основе подзапроса.
CREATE table test_inner as
SELECT
	func(t.id,'Получить сумму задолженности') as sumexp,
	func(t.id,'Сумму проплаты') as sums,
	t.val,
	(t.DTTERM - d.dt)/365 as term ,
	d.dt
FROM
	tables t,
	(select * from 'массив дат') D 
WHERE  
	d.dt between t.dtopen and nvl(t.dtclose,d.dt) AND    
	(t.types = 1  OR t.types = 2 OR t.types = 3)
А затем стартуйте аггрегационный запрос на основе новой таблицы. Так вы получите время запроса. Также полезно засечь, сколько времени создавалась эта таблица (врема, потраченоое на выполнение функции).

Но это все очень приблизительно. Правильнее - проанализировать trace. Если вы не имеете прав на его создание, напишите скрипт и передайте админам. Пусть запустят.
8 янв 08, 18:58    [5126613]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте запрос  [new]
kapelan
Member

Откуда: хутор БольшойБугор
Сообщений: 722
FROM
	tables t,
	(select * from 'массив дат') D 
WHERE  
	d.dt between t.dtopen and nvl(t.dtclose,d.dt)
хороше бы явно указать как JOIN ети таблицы
возможно так и надо как написано.
8 янв 08, 19:21    [5126654]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте запрос  [new]
ыефчюю
Guest
JUNIORik

Опаньки, а вы не внимательно читаете

согласен, забанен бил
так вот, еcли
ето не лобороторнаая продолжим
:::::
STAX
8 янв 08, 20:55    [5126809]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте запрос  [new]
stax..
Guest
 SELECT
          func(t.id,'Получить сумму задолженности') as sumexp,
          func(t.id,'Сумму проплаты') as sums,
          t.val,
неужели я настолько пян
но как она зависит о даты
"select * from 'массив дат') D "
ps
звиняюсь
.......
stax
8 янв 08, 21:04    [5126819]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте запрос  [new]
**************
Guest
Читайте staxa внимательнее, господин Junior. И попробуйте сделать так для подзапроса inner
SELECT
          t1.sumexp,
          t1.sums,
          t1.val,
          (t1.DTTERM - d.dt)/365 as term ,
          d.dt
        FROM
              (select func(t1.id,'Получить сумму задолженности') as sumexp, 
func(t1.id,'Сумму проплаты')as sums, 
t1.dtopen,  
t1.dtclose, 
t1.val, 
t1.DTTERM 
from tables t1 where t1.types in (1, 2, 3)) t,
              (select * from 'массив дат') D 
          WHERE  
            d.dt between t1.dtopen and nvl(t1.dtclose,d.dt) 
Прошу прощения, если будут ошибки, но думаю идея понятна.
8 янв 08, 22:11    [5126918]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте запрос  [new]
**************
Guest
точнее так
SELECT
          t.sumexp,
          t.sums,
          t.val,
          (t.DTTERM - d.dt)/365 as term ,
          d.dt
        FROM
              (select func(t1.id,'Получить сумму задолженности') as sumexp, 
func(t1.id,'Сумму проплаты')as sums, 
t1.dtopen,  
t1.dtclose, 
t1.val, 
t1.DTTERM 
from tables t1 where t1.types in (1, 2, 3)) t,
              (select * from 'массив дат') D 
          WHERE  
            d.dt between t1.dtopen and nvl(t1.dtclose,d.dt)
8 янв 08, 22:13    [5126928]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте запрос  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54381
**************
точнее так
SELECT
          t.sumexp,
          t.sums,
          t.val,
          (t.DTTERM - d.dt)/365 as term ,
          d.dt
        FROM
              (select func(t1.id,'Получить сумму задолженности') as sumexp, 
func(t1.id,'Сумму проплаты')as sums, 
t1.dtopen,  
t1.dtclose, 
t1.val, 
t1.DTTERM 
from tables t1 where t1.types in (1, 2, 3)) t,
              (select * from 'массив дат') D 
          WHERE  
            d.dt between t1.dtopen and nvl(t1.dtclose,d.dt)
Всё равно в вашем случае далеко не факт, что функции отработают всего по одному разу. У Оракла на этот счет свое мнение - например, развернет подзапросы в один большой запрос.
8 янв 08, 23:47    [5127080]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте запрос  [new]
Volder
Member

Откуда: Москва
Сообщений: 474
andreymx
Всё равно в вашем случае далеко не факт, что функции отработают всего по одному разу. У Оракла на этот счет свое мнение - например, развернет подзапросы в один большой запрос.
а Оракла разве никак нельзя переубедить? например, добавить rownum в select, где функции участвуют.
9 янв 08, 08:26    [5127413]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте запрос  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54381
Volder
andreymx
Всё равно в вашем случае далеко не факт, что функции отработают всего по одному разу. У Оракла на этот счет свое мнение - например, развернет подзапросы в один большой запрос.
а Оракла разве никак нельзя переубедить? например, добавить rownum в select, где функции участвуют.
вполне. но это будет другой запрос )))
9 янв 08, 08:33    [5127418]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте запрос  [new]
Volder
Member

Откуда: Москва
Сообщений: 474
andreymx
вполне. но это будет другой запрос )))
согласен, но чем-то он все-таки будет похож на оригинальный
9 янв 08, 08:57    [5127440]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте запрос  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54381
Volder
andreymx
вполне. но это будет другой запрос )))
согласен, но чем-то он все-таки будет похож на оригинальный
все они будут потомками самого первого в обсуждении )))
9 янв 08, 09:46    [5127527]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте запрос  [new]
JUNIORik
Member

Откуда:
Сообщений: 179
**************


Я уже (см. выше) писала, что
опечаталась в ф-ции и она зависит от даты

func(t.id,d.dt,'Получить сумму задолженности') as sumexp,
func(t.id,d.dt,'Сумму проплаты') as sums, 
9 янв 08, 11:33    [5128078]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте запрос  [new]
stax..
Guest
JUNIORik
**************


Я уже (см. выше) писала, что
опечаталась в ф-ции и она зависит от даты

func(t.id,d.dt,'Получить сумму задолженности') as sumexp,
func(t.id,d.dt,'Сумму проплаты') as sums, 

если зависит от даты, то что тут посоветовать, кроме тюнить функцию

если ф-цию заменить на
....
1 as sumexp,
2 as sums,
...
запрос выполяется бистро?
.....
stax
9 янв 08, 11:49    [5128202]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте запрос  [new]
JUNIORik
Member

Откуда:
Сообщений: 179
stax..

если ф-цию заменить на
....
1 as sumexp,
2 as sums,
...
запрос выполяется бистро?


Канечно, дорогой.

ps

Сначала к вопросу об оценке. Вам надо отделить время выполнения функции от времени агрегации результата подзапроса. Как вариант - создайте сначала таблицу на основе подзапроса.
CREATE table test_inner as
SELECT
func(t.id,'Получить сумму задолженности') as sumexp,
func(t.id,'Сумму проплаты') as sums,
t.val,
(t.DTTERM - d.dt)/365 as term ,
d.dt
FROM
tables t,
(select * from 'массив дат') D
WHERE
d.dt between t.dtopen and nvl(t.dtclose,d.dt) AND
(t.types = 1 OR t.types = 2 OR t.types = 3)
А затем стартуйте аггрегационный запрос на основе новой таблицы. Так вы получите время запроса. Также полезно засечь, сколько времени создавалась эта таблица (врема, потраченоое на выполнение функции).

Но это все очень приблизительно. Правильнее - проанализировать trace. Если вы не имеете прав на его создание, напишите скрипт и передайте админам. Пусть запустят.


В итоге на одну дату таб. test_inner создавалась 405 секунды (более 700 строк).
Запрос построенный на основе этой таблицы выполнился за 0.3 секунды.

Т. е. по времени выполнения терпимо, но если строить отчет
за 5 и более дат, то строк будет 7000*5(и более).
Oraclу плохо не станет от объема?
9 янв 08, 12:11    [5128365]     Ответить | Цитировать Сообщить модератору
 Re: Покритикуйте запрос  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54381
JUNIORik
Т. е. по времени выполнения терпимо, но если строить отчет
за 5 и более дат, то строк будет 7000*5(и более).
Oraclу плохо не станет от объема?
А что, самому попробовать не получается?
Кстати, Oraclу в твоём случае может стать плохо как раз не от объема
9 янв 08, 13:17    [5128760]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить