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

Откуда:
Сообщений: 33
Здраствуйте,

целый день сегодня промучился над этой проблемой и решения не нашел. Может из вас кто-то сталкивался с подобным:

есть функция test_func, которая определена очень просто :

function test_func(i integer) return integer as
begin return i;
end;

Вопрос. Можно ли наколдовать, чтобы в следющем запросе функция test_func выполнялась только раз :

SELECT SUM(a.fvalue), COUNT(a.fvalue) 
FROM 
(SELECT test_func(1) fvalue
FROM dual) a

Если да то как ?

У меня она срабатывает 2 раза.
29 дек 06, 17:15    [3598726]     Ответить | Цитировать Сообщить модератору
 Re: многоразовое использование значения функции  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116299
Попробуйте вставить хинт /*+ NO_MERGE */ в подзапрос
29 дек 06, 17:18    [3598739]     Ответить | Цитировать Сообщить модератору
 Re: многоразовое использование значения функции  [new]
grim Dexter
Member

Откуда:
Сообщений: 33
Работает !
Большущее спасибо !
:)
29 дек 06, 17:22    [3598757]     Ответить | Цитировать Сообщить модератору
 Re: многоразовое использование значения функции  [new]
for93t
Member

Откуда:
Сообщений: 260
А опция deterministic не для этого ли предназначена?
29 дек 06, 17:30    [3598783]     Ответить | Цитировать Сообщить модератору
 Re: многоразовое использование значения функции  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116299
for93t
А опция deterministic не для этого ли предназначена?

А зачем гадать ?
Давайте проверим

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
Connected as system


SQL> 
SQL> create or replace function scott.test_func(i integer) return integer as
  2  begin
  3  dbms_output.put_line('Hallo ');
  4  return i;
  5  end;
  6  /

Function created

SQL> set serveroutput on
SQL> 
SQL> SELECT SUM(a.fvalue), COUNT(a.fvalue)
  2  FROM
  3  (SELECT scott.test_func(1) fvalue
  4  FROM dual) a
  5  /

SUM(A.FVALUE) COUNT(A.FVALUE)
------------- ---------------
            1               1

Hallo 
Hallo 

SQL> 
SQL> SELECT SUM(a.fvalue), COUNT(a.fvalue)
  2  FROM
  3  (SELECT /*+ NO_MERGE */ scott.test_func(1) fvalue
  4  FROM dual) a
  5  /

SUM(A.FVALUE) COUNT(A.FVALUE)
------------- ---------------
            1               1

Hallo 

SQL> 
SQL> create or replace function scott.test_func(i integer) return integer deterministic as
  2  begin
  3  dbms_output.put_line('Hallo ');
  4  return i;
  5  end;
  6  /

Function created

SQL> SELECT SUM(a.fvalue), COUNT(a.fvalue)
  2  FROM
  3  (SELECT scott.test_func(1) fvalue
  4  FROM dual) a
  5  /

SUM(A.FVALUE) COUNT(A.FVALUE)
------------- ---------------
            1               1

Hallo 
Hallo 

SQL> 
29 дек 06, 17:42    [3598820]     Ответить | Цитировать Сообщить модератору
 Re: многоразовое использование значения функции  [new]
for93t
Member

Откуда:
Сообщений: 260
Спасибо Вам, dmidek. В Новый год вхожу с новыми знаниями. Почему-то все время считал, что deterministic-функции с одинаковыми аргументами вызываются только один раз.
С наступающим!!!
29 дек 06, 17:52    [3598850]     Ответить | Цитировать Сообщить модератору
 Re: многоразовое использование значения функции  [new]
mgtu
Member

Откуда:
Сообщений: 123
dmidek а что делает NO_MERGE ? я из документации не очень понял
29 дек 06, 18:20    [3598902]     Ответить | Цитировать Сообщить модератору
 Re: многоразовое использование значения функции  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116299
mgtu
dmidek а что делает NO_MERGE ? я из документации не очень понял

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

SELECT SUM(test_func(1)), COUNT(test_func(1)) 
FROM 
DUAL

Это только один из возможных методов замораживания или
материализации представления. Многие любят пользоваться
псевдоусловием rownum > 0
29 дек 06, 18:29    [3598930]     Ответить | Цитировать Сообщить модератору
 Re: многоразовое использование значения функции  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10051
Well, NO_MERGE and deterministic are two different animals. Try some multirow table instead of DUAL.

SY.
29 дек 06, 18:40    [3598959]     Ответить | Цитировать Сообщить модератору
 Re: многоразовое использование значения функции  [new]
mgtu
Member

Откуда:
Сообщений: 123
Это касается и подзапросов с ИН ?
29 дек 06, 18:42    [3598961]     Ответить | Цитировать Сообщить модератору
 Re: многоразовое использование значения функции  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116299
mgtu
Это касается и подзапросов с ИН ?

В общем случае да, безотносительно к данной задаче
29 дек 06, 18:59    [3599000]     Ответить | Цитировать Сообщить модератору
 Re: многоразовое использование значения функции  [new]
mgtu
Member

Откуда:
Сообщений: 123
dmidek
mgtu
Это касается и подзапросов с ИН ?

В общем случае да, безотносительно к данной задаче

Ну спасибо за новогодний подарок dmidek !!! Пьяный и с хинтом я применил этот хинт, и получил новые знания к давнишней проблеме и сооптмизировал запрос с 20 минут до 5 секуд. Вы мой кумир теперь!!!!
29 дек 06, 19:03    [3599003]     Ответить | Цитировать Сообщить модератору
 Re: многоразовое использование значения функции  [new]
mgtu
Member

Откуда:
Сообщений: 123
CREATE OR REPLACE VIEW vw_user_stocks (stock_group_id,
                                       ID,
                                       NAME,
                                       code,
                                       permission_mask,
                                       user_name,
                                       deleted
                                      )
AS
   (SELECT stock_group_id, ID, NAME, code, permission_mask, user_name, deleted
      FROM (SELECT s.stock_group_id, s.ID, s.NAME, s.code, p.permission_mask,
                   p.user_id AS user_name, s.deleted,
                   DECODE (p.stock_id, 0, 'Group_stock', 'Stock') AS TYPE,
                   p.stock_group_id AS child_stock_group_id,
                   ROW_NUMBER () OVER (PARTITION BY s.stock_group_id, s.ID, s.NAME, s.code, p.user_id ORDER BY DECODE
                                                                         (p.stock_id,
                                                                          0, 'Group_stock',
                                                                          'Stock'
                                                                         ) DESC) AS rw_type,
                   ROW_NUMBER () OVER (PARTITION BY s.stock_group_id, s.ID, s.NAME, s.code, p.user_id, DECODE
                                                                                                         (p.stock_id,
                                                                                                          0, 'Group_stock',
                                                                                                          'Stock'
                                                                                                         ) ORDER BY p.stock_group_id DESC)
                                                                                        AS rw_child
              FROM stock_group_permission p, stock s
             WHERE UPPER (p.user_id) IN (SELECT --+ NO_MERGE
                                                UPPER (security_management.get_context ('user_id'))
                                           FROM DUAL)
               AND (   (p.stock_id <> 0 AND p.stock_id = s.ID)
                    OR (    p.stock_id = 0
                        AND s.stock_group_id IN (SELECT     sg.ID
                                                       FROM stock_group sg
                                                 START WITH sg.ID = p.stock_group_id
                                                 CONNECT BY PRIOR sg.ID = sg.stock_group_id
                                                   GROUP BY sg.ID)
                       )
                   )
               AND permission_mask > 0)
     WHERE rw_type = rw_child AND rw_type = 1)
/

29 дек 06, 19:10    [3599018]     Ответить | Цитировать Сообщить модератору
 Re: многоразовое использование значения функции  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116299
mgtu
dmidek
mgtu
Это касается и подзапросов с ИН ?

В общем случае да, безотносительно к данной задаче

Ну спасибо за новогодний подарок dmidek !!! Пьяный

Ага, ага, так я был прав пару постов назад
mgtu

и с хинтом я применил этот хинт, и получил новые знания к давнишней проблеме и сооптмизировал запрос с 20 минут до 5 секуд.


Очень рад, что удалось Вам помочь и спасибо за добрые слова.
Без кокетства хочу сказать, что особенно в этом вопросе я
только "стою на плечах гигантов", которые в форуме многократно
освещали этот вопрос.
Лучше всего меня познакомили с этой технологией andrey_anonymous и Владимор Конев,
которых, пользуясь случаем, вместе с Вами я от всей души поздравляю
с новым 2007 годом !!!
29 дек 06, 19:12    [3599020]     Ответить | Цитировать Сообщить модератору
 Re: многоразовое использование значения функции  [new]
kvak
Member

Откуда: Москва
Сообщений: 35
Такой вариант
SELECT SUM(a.fvalue), COUNT(a.fvalue)
FROM (SELECT (SELECT test_func(1) from dual) fvalue from dual) a
тоже отрабатывает один раз.
30 дек 06, 01:45    [3599784]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить