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

Откуда:
Сообщений: 14
Подскажите как можно переделать запрос вида

SELECT s.scr, ...[еще куча полей]
   FROM pm_scr s, pm_contract c, doc_general d
   WHERE   s.contract = c.contract
       AND d.doc = c.doc_contract
       AND d.doc_state <> 14
       AND s.scr IN
                  (SELECT FIRST_VALUE (r.scr)
                             OVER (PARTITION BY contract, doc_no ORDER BY scr)
                   FROM pm_scr r);

Из-за последнего "IN" время выполнения запроса увеличивается в четыре раза, логика естественно должна остаться ) То есть мне нужно выбрать только первые записи pm_scr по конкретным contract и doc_no. Можно ли это сделать вообще без аналитических функций?
13 июл 09, 13:49    [7408375]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции  [new]
Вячеслав Банкет
Member

Откуда: NY (Нефтеюганск)
Сообщений: 139
SELECT s.scr, ...[еще куча полей]
   FROM pm_scr s, pm_contract c, doc_general d
   WHERE   s.contract = c.contract
       AND d.doc = c.doc_contract
       AND d.doc_state <> 14
       AND EXISTS
                  (SELECT FIRST_VALUE (r.scr)
                             OVER (PARTITION BY contract, doc_no ORDER BY scr)
                   FROM pm_scr r
                   WHERE r.scr = s.scr);
13 июл 09, 14:25    [7408662]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции  [new]
AlexFF__|
Member

Откуда:
Сообщений: 2855
To Вячеслав Банкет

Интересный вариант. Правда, условие с подзапросом вернет 100% записей.

To Virooz

А почему подзапрос не вставить в основу?
13 июл 09, 14:30    [7408711]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции  [new]
Virooz
Member

Откуда:
Сообщений: 14
Ну потому что тогда аналитическую функцию придется писать на все поля (их около 15) что только усугубит ситуацию.
13 июл 09, 14:36    [7408768]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции  [new]
AlexFF__|
Member

Откуда:
Сообщений: 2855
Virooz
Ну потому что тогда аналитическую функцию придется писать на все поля (их около 15) что только усугубит ситуацию.

Это как?

select * from (    
      SELECT s.scr, ...[еще куча полей],
             FIRST_VALUE (r.scr)
                OVER (PARTITION BY r.contract, r.doc_no ORDER BY r.scr) first_scr
   FROM pm_scr s, pm_contract c, doc_general d
   WHERE   s.contract = c.contract
       AND d.doc = c.doc_contract
       AND d.doc_state <> 14
               ) where scr = first_scr
13 июл 09, 14:41    [7408819]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции  [new]
Virooz
Member

Откуда:
Сообщений: 14
А вот вариант Вячеслава Банкет вроде бы помог ;) щас до конца результат проверю...
13 июл 09, 14:41    [7408821]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции  [new]
Virooz
Member

Откуда:
Сообщений: 14
AlexFF__|,

Нет, так результат остается прежним, производительность в 4 раза ниже чем без функции
13 июл 09, 14:45    [7408850]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции  [new]
Вячеслав Банкет
Member

Откуда: NY (Нефтеюганск)
Сообщений: 139
Решение "в лоб". Некрасивое и неоптимальное... зато быстрое.
13 июл 09, 14:45    [7408857]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции  [new]
Virooz
Member

Откуда:
Сообщений: 14
И работает в два раза быстрее ) Спасибо.
Будут варианты "красивее" и "оптимальнее" пишите, буду благодарен
13 июл 09, 14:46    [7408874]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции  [new]
дщд
Guest
Вячеслав Банкет,

иди подумай еще...
13 июл 09, 15:35    [7409253]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10051
Вячеслав Банкет,

Analytic function is applied after result set is fetched. Therefore,

SELECT FIRST_VALUE (r.scr)
                             OVER (PARTITION BY contract, doc_no ORDER BY scr)
                   FROM pm_scr r
                   WHERE r.scr = s.scr

will first select all rows WHERE r.scr = s.scr and only then will calculate FIRST_VALUE for each contract, doc_no combination which obviously will be r.scr. Now original code:

SELECT FIRST_VALUE (r.scr)
                             OVER (PARTITION BY contract, doc_no ORDER BY scr)
                   FROM pm_scr r

will calculate FIRST_VALUE for each contract, doc_no combination first and only then compare that first value with s.scr.

2 Virooz :

I would question:

AND s.scr IN
                  (SELECT FIRST_VALUE (r.scr)
                             OVER (PARTITION BY contract, doc_no ORDER BY scr)
                   FROM pm_scr r);

It has no correlation to main query on contract and doc no. Are you saying any src is good as long as it is first even though it matches other contract and/or doc? I would expect something like:

AND s.scr IN
                  (SELECT FIRST_VALUE (r.scr)
                             OVER (ORDER BY scr)
                   WHERE r.contract = s.contract
                     AND r.doc_no = d.doc_no
                   FROM pm_scr r);

SY.
13 июл 09, 17:15    [7410074]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции  [new]
Virooz
Member

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

Sorry for my english at first )

Yes you right. Subquery

SELECT FIRST_VALUE (r.scr)
   OVER (ORDER BY scr)
 WHERE r.contract = s.contract
   AND r.doc_no = s.doc_no -- not d.doc_no
  FROM pm_scr r

would return less rows (only one in the best case), but speed does't inrease.

The problem not in subquery, but in the IN statement i think.

I tried to use modified Вячеслав Банкет method:

AND EXISTS
                  (SELECT * FROM (SELECT FIRST_VALUE (r.scr)
                             OVER (PARTITION BY contract, doc_no ORDER BY scr) scr
                   FROM pm_scr r) q WHERE q.scr=s.scr
                   )

Query with "EXISTS" works 2 seconds, with your solution it works 4.
Without IN/EXISTS statements query works less then 1 second, may be it's normal, because it returns more then 2000 rows with 30 fields


Query is:

SELECT s.scr,
       TO_NUMBER (s.doc_no) AS doc_no,
       s.contract,
       (SELECT c.contract_no
        FROM pm_contract c
        WHERE c.contract = s.contract)
          AS contract_no,
       s.f01 AS group_field,
       s.subject,
       s.schedule_type,
       (SELECT h.schedule_type_name
        FROM pm_schedule_type h
        WHERE h.schedule_type = s.schedule_type)
          AS schedule_type_name,
       s.base_date_start,
       s.base_date_end,
       (SELECT SUM (p.norm_lor)
        FROM pm_scr p
        WHERE     p.scr_type = s.scr_type
              AND p.is_arc = s.is_arc
              AND p.doc_no = s.doc_no
              AND p.contract = s.contract)
          AS total_norm_lor,
       (SELECT MIN (p.plan_date_start)
        FROM pm_scr p
        WHERE     p.scr_type = s.scr_type
              AND p.is_arc = s.is_arc
              AND p.doc_no = s.doc_no
              AND p.contract = s.contract)
          AS plan_date_start,
       (SELECT MAX (p.plan_date_end)
        FROM pm_scr p
        WHERE     p.scr_type = s.scr_type
              AND p.is_arc = s.is_arc
              AND p.doc_no = s.doc_no
              AND p.contract = s.contract)
          AS plan_date_end,
       (SELECT SUM (e.rest_lor)
        FROM v$pm_scr_design_exec e
        WHERE s.doc_no = e.doc_no AND s.contract = e.contract)
          AS rest_lor,
       (SELECT pm_order.job_status (s.doc_no, s.contract)
        FROM DUAL)
          AS resolution,
       (SELECT r.resolution_name
        FROM pm_resolution r
        WHERE r.resolution =
                 (SELECT pm_order.job_status (s.doc_no, s.contract)
                  FROM DUAL))
          AS resolution_name,
       s.reserve_type,
       (SELECT r.reserve_type_name
        FROM pm_reserve_type r
        WHERE r.reserve_type = s.reserve_type)
          AS reserve_type_name,
       s.executor,
       (SELECT u.department
        FROM pm_user u
        WHERE u.user_name = s.executor)
          AS department,
       (SELECT d.department_code
        FROM pm_user u, department d
        WHERE u.user_name = s.executor AND d.department = u.department)
          AS department_code,
       (SELECT d.department_name
        FROM pm_user u, department d
        WHERE u.user_name = s.executor AND d.department = u.department)
          AS department_name,
       (SELECT SUM (NVL (p.plan_lor, 0) + NVL (f11, 0))
        FROM pm_scr p
        WHERE     p.scr_type = s.scr_type
              AND p.is_arc = s.is_arc
              AND p.doc_no = s.doc_no
              AND p.contract = s.contract)
          AS total_plan_lor,
       (SELECT SUM (p.fact_lor)
        FROM pm_scr p
        WHERE     p.scr_type = s.scr_type
              AND p.is_arc = s.is_arc
              AND p.doc_no = s.doc_no
              AND p.contract = s.contract)
          AS total_fact_lor,
       CASE
          WHEN ( (SELECT MIN (e.plan_date_start)
                  FROM v$pm_scr_design_exec e
                  WHERE s.doc_no = e.doc_no AND s.contract = e.contract) <
                   s.base_date_start)
               OR ( (SELECT MAX (e.plan_date_end)
                     FROM v$pm_scr_design_exec e
                     WHERE s.doc_no = e.doc_no AND s.contract = e.contract) >
                      s.base_date_end)
          THEN
             1
          ELSE
             0
       END
          date_is_overlaped,
       DECODE (s.schedule_type,
          'C', s.f12,
          pm_order.get_percent_done_head (s.scr))
       || '%'
          AS done_percent,
       s.f02 AS contractor_doc,
       (SELECT v.doc_no
        FROM doc_general v
        WHERE v.doc = s.f02)
          AS contractor_doc_name,
       s.f03 AS contractor_doc_stage,
       (SELECT v.doc_no
        FROM vd$doc_general v, contract_stage cs
        WHERE v.doc = s.f03)
          AS contractor_doc_stage_name,
       (SELECT cont_full_name
        FROM cont, doc_general v, contract_general cg
        WHERE cg.doc = v.doc AND v.doc = s.f02 AND cont = cg.recipient)
          AS contractor_name
FROM pm_scr s, pm_contract c, doc_general d
WHERE     s.contract = c.contract
      AND d.doc = c.doc_contract
      AND d.doc_state <> 14
      AND EXISTS
            (SELECT *
             FROM (SELECT FIRST_VALUE (r.scr)
                             OVER (PARTITION BY contract, doc_no ORDER BY scr)
                             scr
                   FROM pm_scr r) q
             WHERE q.scr = s.scr)
14 июл 09, 08:35    [7411505]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить