19.05.2022 21:12
Harry
 
Доброго дня господа !

Опишу суть проблемы - есть трудоемкий запрос, его материал-рую hint-ом во временную таблицу
получаю очень незначительное число записей.
Необходимо это число записей визуализировать ИИИ !!!! передать как параметр Ref_Cursor в пользовательскую функцию
где это курсор будет просканен в цикле ....loop.

SQL код:
with REMAIN as (select /*+ Materialize  
                                       */
                                       
*
                                
from БОЛЬШОЙ ЗАПРОС
                           


select A.*
  
from REMAIN A
where Exists
(select 1 from DUAL where SaveCursor(Cursor(select from REMAIN))>0)
-----------------------------------------------------
LOOP
    FETCH  c_sales  INTO r_sales
;
    EXIT 
WHEN c_sales%NOTFOUND;
END LOOP;
CLOSE c_sales
Внимание вопрос !!!!
- в момент ссылки на REMAIN - выполняю запрос и материализую результат
- в момент первого FETCH в цикле польз. функции - СНОВА выполняю запрос и имею УДВОЕННІЕ затраты времени
Т.е. функция курсора игнорирует уже материа-нный результат первого выполнения.
Факт двойного выполнения проверян.

Как этого избежать ?

Заранее благодарен !
19.05.2022 21:49
OlegON
 
Так хинт
Цитата:
Harry /*+ Materialize */
действует в пределах одного запроса, то есть вполне себе логично последующее поведение.
Цитата:
Harry Как этого избежать ?
Поздновато для таких размышлений, но почему бы не использовать временную таблицу-то?
19.05.2022 22:42
Harry
 
Цитата:
OlegON Поздновато для таких размышлений, но почему бы не использовать временную таблицу-то?
Не совсем в пределах запроса - если смотреть ПЛАН оптимизации там явная ссылка на системную таблицу
образованную механизмом материализации.
Т.е. основной селест ссылаеться на эту материализацию и курсор тоже ссылаеться на нее, но на самом деле ...
это не так .
Надеялся, что курсор сумеет подхватить уже готовый результат !
Пробывал Result_Cache - там вроде писалось что используеться глобальные области памяти.
Могу приложить план.


Насчет временой таблицы - суть вопроса именно в ней.

Алгоритм работы
- пользователь должен выполнить запрос
- сохранить его результаты в пользовательскую таблицу
- а также увидеть результат этого запроса.
но все это хотелось бы делать ОДНОЙ командой (SELECT) которая будет запускаться из под некого интерфейса

Т.е. по сути Вы акцентируете внимание на разделение процесса, у меня же проблема как совместить два этапа
Т.е. первый этап - сохранение - неоткуда запускать.

Спасибо
19.05.2022 23:19
OlegON
 
а какая версия вообще СУБД?
зачем вообще отдельно фетчить?
мне какой-то информации не хватает...
почему не использовать датасет первого запроса у пользователя?
20.05.2022 10:43
Harry
 
Версия Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Понял Вас - даю полный пакет объектов

-- Функция нужна для визуализации и отслеживании этапов процесса в буфер
create or replace function sv_ShowMessage(pList in String default Null)
return number
is
begin
dbms_output.put_line('Show '||pList);
return 1;
end;

-- Функция нужна для записи данных курсора в жесткую временную таблицу, пока это цикл
create or replace function Sv_SaveCursor1(pTable Sys_RefCursor,pSave Integer) return Integer
is
pragma autonomous_transaction;
begin
declare
I Integer :=0;
R Integer :=0;
rcTable SV_TT_REMAIN1%RowType;
begin
R:=sv_showmessage('Function');
if pSave=1 then
R:=sv_showmessage('Save');
if pTable%isOpen then
R:=sv_showmessage('Open');
loop
fetch pTable into rcTable;
exit when pTable%NotFound;
--здесь будет INSERT;
I:=I+1;
R:=sv_showmessage('Loop');
end loop;
commit;
R:=sv_showmessage('Commit');
close pTable;
R:=sv_showmessage('Close');
end if;
end if;
R:=sv_showmessage('Return');
return I;
end;
end;

-- Базовое предсталение - где BZ_REMAIN - будет оформлен как БОЛЬШОЙ и
трудоемкий запрос (пока это просто таблица)
create or replace view sv_bo_remain1 as
with REMAIN as (select /*+
Materialize
Result_Cache
No_Merge(Q)
*/
Q.DAY,
Q.PROGRAM,
0 AMOUNT,
Q.PLAN,
0 NBI,
Q.NBI NBI_PLAN,
Q.NOTE
from (
select /*+
No_Merge
*/
*
from BZ_REMAIN
where DAY=To_Date('30-04-2022','DD-MM-YYYY')
and Exists(select 1 from DUAL where Nvl(Sv_ShowMessage('Start'),0)>-1)
) Q
)
select /*+
11Materialize
11Result_Cache
*/
*
from REMAIN
where Exists(select 1 from DUAL where Nvl(Sv_SaveCursor1(Cursor(select * from REMAIN),1),0)<>-1)

--План оптимизации - смотреть временную таблицу SYS_TEMP_0FD9DD305_A0FFC59B
Она в плане используеться дважды - для select и для cursora

Plan hash value: 3606230372

-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 285 | 10 (0)| 00:00:01 |
| 1 | VIEW | SV_BO_REMAIN1 | 5 | 285 | 10 (0)| 00:00:01 |
| 2 | TEMP TABLE TRANSFORMATION | | | | | |
| 3 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9DD305_A0FFC59B | | | | |
| 4 | VIEW | | 5 | 265 | 4 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED | BZ_REMAIN | 5 | 125 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | SYS_C00341088 | 5 | | 1 (0)| 00:00:01 |
|* 8 | FILTER | | | | | |
| 9 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 10 | FILTER | | | | | |
| 11 | VIEW | | 5 | 285 | 2 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9DD305_A0FFC59B | 5 | 255 | 2 (0)| 00:00:01 |
|* 13 | FILTER | | | | | |
| 14 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 15 | VIEW | | 5 | 285 | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9DD305_A0FFC59B | 5 | 255 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - filter( EXISTS (SELECT 0 FROM "SYS"."DUAL" "DUAL" WHERE NVL("SV_SHOWMESSAGE"('Start'),0)>(-1)))
7 - access("DAY"=TO_DATE(' 2022-04-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
8 - filter(NVL("SV_SHOWMESSAGE"('Start'),0)>(-1))
10 - filter( EXISTS (SELECT 0 FROM "SYS"."DUAL" "DUAL" WHERE NVL("SV_SAVECURSOR1"(CURSOR (SELECT
"REMAIN"."DAY","REMAIN"."PROGRAM","REMAIN"."AMOUNT","REMAIN"."PLAN","REMAIN"."NBI","REMAIN"."NBI_PLAN","REMAIN"."
NOTE" FROM (SELECT /*+ CACHE ("T1") */ "C0" "DAY","C1" "PROGRAM","C2" "AMOUNT","C3" "PLAN","C4" "NBI","C5"
"NBI_PLAN","C6" "NOTE" FROM "SYS"."SYS_TEMP_0FD9DD305_A0FFC59B" "T1") "REMAIN"),1),0)<>(-1)))
13 - filter(NVL("SV_SAVECURSOR1"(CURSOR (SELECT "REMAIN"."DAY","REMAIN"."PROGRAM","REMAIN"."AMOUNT","REMAIN"."P
LAN","REMAIN"."NBI","REMAIN"."NBI_PLAN","REMAIN"."NOTE" FROM (SELECT /*+ CACHE ("T1") */ "C0" "DAY","C1"
"PROGRAM","C2" "AMOUNT","C3" "PLAN","C4" "NBI","C5" "NBI_PLAN","C6" "NOTE" FROM
"SYS"."SYS_TEMP_0FD9DD305_A0FFC59B" "T1") "REMAIN"),1),0)<>(-1))

-- Буфер памяти куда выводиться сообщения по этапам выполнения - второго сообщения "Show Start" не должно быть
так как fetch цикла cursora идет по сформированной жесткой таблице SYS_TEMP_0FD9DD305_A0FFC59B

Show Start
Show Function
Show Save
Show Open
Show Start !!!!
Show Commit
Show Close
Show Return

Если это будет БОЛЬШОЙ запроса - это потеря времени

По архитектуре и общей постановке задачи
Пользователь ввел дату в свое клиенское приложения
- если даты такой ранее не было - запустился БОЛЬШОЙ запрос - выполнился и сохранился в
пользовательской буферной таблице
- если такая дата есть запроса тянет данные из пользовательской буферной таблице

Здесь совмещены два этапа - этап обновления данных и этап визуализации, по сути это две
транзакции (курсор предстален в отдельной транзакции = pragma autonomous_transaction)
Но если опустить диррективы pragma и commit курсор все равно повторно выполняем запрос
Можно ли этого избежать ?
Как заставить курсор двигаться по уже матре-ой таблице SYS_TEMP_0FD9DD305_A0FFC59B

Спасибо !
20.05.2022 10:50
Harry
 
Извините не нашел как в момент ответа - выделять SQL блоки руками писать или кнопка есть
И как в момент отправки первичного сообщения - где кнопка ОТПРАВИТЬ ....? нажимал Ctrl + Enter
20.05.2022 10:55
Harry
 
Версия Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Понял Вас - даю полный пакет объектов

SQL код:
-- Функция нужна для визуализации и отслеживании этапов процесса в буфер
create 
or replace function sv_ShowMessage(pList in String default Null)
return 
number
is
  begin
    dbms_output
.put_line('Show '||pList);
    return 
1;
  
end;

-- 
Функция нужна для записи данных курсора в жесткую временную таблицупока это цикл
create 
or replace function Sv_SaveCursor1(pTable Sys_RefCursor,pSave Integer) return Integer
is
   pragma autonomous_transaction
;
begin
  
declare
    
I Integer :=0;
    
R Integer :=0;
    
rcTable SV_TT_REMAIN1%RowType;
  
begin
    R
:=sv_showmessage('Function');
    if 
pSave=1 then
      R
:=sv_showmessage('Save');
      if 
pTable%isOpen then
        R
:=sv_showmessage('Open');
        
loop
          fetch pTable into rcTable
;
          exit 
when pTable%NotFound;
          --
здесь будет INSERT;
          
I:=I+1;
          
R:=sv_showmessage('Loop');
        
end loop;
        
commit;
        
R:=sv_showmessage('Commit');
        
close pTable;
        
R:=sv_showmessage('Close');
      
end if;
    
end if;
    
R:=sv_showmessage('Return');
    return 
I;
  
end;
end
-- Базовое предсталение - где BZ_REMAIN - будет оформлен как БОЛЬШОЙ и
трудоемкий запрос (пока это просто таблица)
SQL код:
create or replace view sv_bo_remain1 as
with REMAIN as (select /*+
                          Materialize
                          Result_Cache
                          No_Merge(Q)
                       */
                       
Q.DAY,
                       
Q.PROGRAM,
                       
0 AMOUNT,
                       
Q.PLAN,
                       
0 NBI,
                       
Q.NBI NBI_PLAN,
                       
Q.NOTE
                  from 
(
                        
select /*+ 
                                   No_Merge
                               */
                               
*
                          
from BZ_REMAIN
                         where DAY
=To_Date('30-04-2022','DD-MM-YYYY')
                           and 
Exists(select 1 from DUAL where Nvl(Sv_ShowMessage('Start'),0)>-1)
                      ) 
Q
                  
)
select /*+
          11Materialize
          11Result_Cache
       */
       
*
  
from REMAIN
 where Exists
(select 1 from DUAL where Nvl(Sv_SaveCursor1(Cursor(select from REMAIN),1),0)<>-1
--План оптимизации - смотреть временную таблицу SYS_TEMP_0FD9DD305_A0FFC59B
Она в плане используеться дважды - для select и для cursora

SQL код:
Plan hash value3606230372
 
-------------------------------------------------------------------------------------------------------------------------
Id  Operation                                 Name                        Rows  Bytes Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------------
|   
SELECT STATEMENT                          |                             |     |   285 |    10   (0)| 00:00:01 |
|   
|  VIEW                                     SV_BO_REMAIN1               |     |   285 |    10   (0)| 00:00:01 |
|   
|   TEMP TABLE TRANSFORMATION               |                             |       |       |            |          |
|   
|    LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9DD305_A0FFC59B |       |       |            |          |
|   
|     VIEW                                  |                             |     |   265 |     4   (0)| 00:00:01 |
|*  
|      FILTER                               |                             |       |       |            |          |
|   
|       TABLE ACCESS BY INDEX ROWID BATCHED BZ_REMAIN                   |     |   125 |     2   (0)| 00:00:01 |
|*  
|        INDEX RANGE SCAN                   SYS_C00341088               |     |       |     1   (0)| 00:00:01 |
|*  
|       FILTER                              |                             |       |       |            |          |
|   
|        FAST DUAL                          |                             |     |       |     2   (0)| 00:00:01 |
|* 
10 |    FILTER                                 |                             |       |       |            |          |
|  
11 |     VIEW                                  |                             |     |   285 |     2   (0)| 00:00:01 |
|  
12 |      TABLE ACCESS FULL                    SYS_TEMP_0FD9DD305_A0FFC59B |     |   255 |     2   (0)| 00:00:01 |
|* 
13 |     FILTER                                |                             |       |       |            |          |
|  
14 |      FAST DUAL                            |                             |     |       |     2   (0)| 00:00:01 |
|  
15 |      VIEW                                 |                             |     |   285 |     2   (0)| 00:00:01 |
|  
16 |       TABLE ACCESS FULL                   SYS_TEMP_0FD9DD305_A0FFC59B |     |   255 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   
filterEXISTS (SELECT 0 FROM "SYS"."DUAL" "DUAL" WHERE NVL("SV_SHOWMESSAGE"('Start'),0)>(-1)))
   
access("DAY"=TO_DATE(' 2022-04-30 00:00:00''syyyy-mm-dd hh24:mi:ss'))
   
filter(NVL("SV_SHOWMESSAGE"('Start'),0)>(-1))
  
10 filterEXISTS (SELECT 0 FROM "SYS"."DUAL" "DUAL" WHERE NVL("SV_SAVECURSOR1"(CURSOR (SELECT 
              
"REMAIN"."DAY","REMAIN"."PROGRAM","REMAIN"."AMOUNT","REMAIN"."PLAN","REMAIN"."NBI","REMAIN"."NBI_PLAN","REMAIN"."
              NOTE" 
FROM  (SELECT /*+ CACHE ("T1") */ "C0" "DAY","C1" "PROGRAM","C2" "AMOUNT","C3" "PLAN","C4" "NBI","C5" 
              "NBI_PLAN"
,"C6" "NOTE" FROM "SYS"."SYS_TEMP_0FD9DD305_A0FFC59B" "T1""REMAIN"),1),0)<>(-1)))
  
13 filter(NVL("SV_SAVECURSOR1"(CURSOR (SELECT "REMAIN"."DAY","REMAIN"."PROGRAM","REMAIN"."AMOUNT","REMAIN"."P
              LAN"
,"REMAIN"."NBI","REMAIN"."NBI_PLAN","REMAIN"."NOTE" FROM  (SELECT /*+ CACHE ("T1") */ "C0" "DAY","C1" 
              "PROGRAM"
,"C2" "AMOUNT","C3" "PLAN","C4" "NBI","C5" "NBI_PLAN","C6" "NOTE" FROM 
              
"SYS"."SYS_TEMP_0FD9DD305_A0FFC59B" "T1""REMAIN"),1),0)<>(-1)) 
-- Буфер памяти куда выводиться сообщения по этапам выполнения - второго сообщения "Show Start" не должно быть
так как fetch цикла cursora идет по сформированной жесткой таблице SYS_TEMP_0FD9DD305_A0FFC59B

SQL код:
Show Start
Show 
Function
Show Save
Show Open
Show Start  
!!!!
Show Commit            
Show Close
Show 
Return 
Если это будет БОЛЬШОЙ запроса - это потеря времени

По архитектуре и общей постановке задачи
Пользователь ввел дату в свое клиенское приложения
- если даты такой ранее не было - запустился БОЛЬШОЙ запрос - выполнился и сохранился в
пользовательской буферной таблице
- если такая дата есть запроса тянет данные из пользовательской буферной таблице

Здесь совмещены два этапа - этап обновления данных и этап визуализации, по сути это две
транзакции (курсор предстален в отдельной транзакции = pragma autonomous_transaction)
Но если опустить диррективы pragma и commit курсор все равно повторно выполняем запрос
Можно ли этого избежать ?
Как заставить курсор двигаться по уже матре-ой таблице SYS_TEMP_0FD9DD305_A0FFC59B

Спасибо !
20.05.2022 10:57
Harry
 
Надо включать расширенный режим - с форматом справился
Досмотрите плис насчет основгого вопроса
20.05.2022 17:02
OlegON
 
Заранее прошу извинить, объем вопроса уже начинает выпадать за рамки обычно рассматриваемого на форуме, пробежал, возможно, не совсем внимательно.
Первый вопрос, а зачем, собственно, делать вью с хинтом матвью, если можно сделать сразу матвью?
20.05.2022 19:51
Harry
 
По архитектуре и общей постановке задачи
Пользователь ввел дату в свое клиенское приложения
- если даты такой ранее не было - запустился БОЛЬШОЙ запрос - выполнился и сохранился в
пользовательской буферной таблице
- если такая дата есть запроса тянет данные из пользовательской буферной таблице

Пользователь вводить дату по сути в один и тот же запрос - но скорость выполнения зависит
если ли именно такой буферный кеш за конкретную дату в пользовательской таблице


Как реализовать этот процесс средствами мат. вью - буду признателен за пример
Обращаю Ваше внимание сделать это ОДНИМ select - без внешних PL/SQL блоков
(скажем блок обновление - их неоткуда запускать).
Два старта запроса нельзя - НЕ красивенько будет смотреться

Спасибо за понимание
Часовой пояс GMT +3, время: 16:32.

Форум на базе vBulletin®
Copyright © Jelsoft Enterprises Ltd.
В случае заимствования информации гипертекстовая индексируемая ссылка на Форум обязательна.