Версия 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 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
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
Спасибо !