We have various cases where being able to create a table from an execute native statement would greatly speed up processing.
This topic was discussed in Faster loading from execute native.
Example :
We are trying to synchronize with incremental approach some table in PostgreSQL. To do such we are leveraging a datetime_modified
column. But we also need to know which records were deleted in the source container. In order to achieve this we need to execute server side :
execute native
''
|| ' SELECT all_ids AS missing_ids'
|| ' FROM generate_series((SELECT MIN(id) FROM my_table), (SELECT '
|| 'MAX(id) FROM my_table)) all_ids'
|| 'EXCEPT SELECT id FROM my_table'
|| 'ORDER BY missing_ids ASC'
datacontainer 'PotsGres'
This statement returns 410K records on our dataset. We see from debug view (using trace) that the missing_ids
are pulled quite quickly (15 seconds).
Then we need to manipulate this inside Invantive Query Tool. But inserting the values from a loop takes a very long time, more than 10 minutes:
create or replace table missing_ids@inmemorystorage
( id int
);
--
-- Takes 10 minutes.
--
begin
for r in (execute native
'SELECT all_ids AS missing_ids
FROM generate_series((SELECT MIN(id) FROM my_table), (SELECT MAX(id) FROM my_table)) all_ids
EXCEPT SELECT id FROM my_table
ORDER BY missing_ids ASC
'
datacontainer 'postgres')
loop
insert into missing_ids@inmemorystorage (id) values (r.missing_ids);
end loop;
end;
It would be very useful to:
create or replace table my_ids@inmemorystorage
as
execute native
''
|| ' SELECT all_ids AS missing_ids'
|| ' FROM generate_series((SELECT MIN(id) FROM my_table), (SELECT '
|| 'MAX(id) FROM my_table)) all_ids'
|| 'EXCEPT SELECT id FROM my_table'
|| 'ORDER BY missing_ids ASC'
datacontainer 'PotsGres'
Approaches described in the faster-loading-from-execute-native like creating a view in postgres would work but give poor flexibility when update of the statement are needed.