Subject Something like Materialized View
Author agjedrus
I wanted implement something like Materialized View.

The purpose is to cache the result from long lasting queries is some
persistent way. This persistent result could be later used as input of
sorting or even filtering, paging, or used for other session to serve
result without triggering timeconsuming select query.

I tried to use external file tables, because I need to store and
retrieve cached result very fast and only that way brings very
performant solution. The only problem that I found that I cannot use
table created at the runtime in stored procedure (EXECUTE STATEMENT
'CREATE TABLE T ' || intXXX || ' EXTERNAL FILE ''....'' ' for later
insert in the same stored procedure.
Other solution with "normal" table with reportId is not performant:
deleting report takes about 3 s. whereas droping external file is just
glipse. The same inserting into external table takes about 1s whereas
inserting into indexed (i need indexes for has search) "normal" table
9-20s. External table just stores ID of selected items and row number
for paging, complete report is achived by joing those ID's with other
tables accordingly of the details requred by client.

It is possible to use external table created at runtime of stored
procedure and then immediately insert into it?
In oracle this is possible...