Saving a result set for later fetching

Question

I have several queries that work for quite some time (20-30 minutes). If many requests are started at the same time, the connection pool is quickly deleted.

Is it possible to complete a long query into an operator (procedure) that will save the result of the general query in the temp table, terminating the connection and fetchin (polling) the results later on request?

EDIT: queries and data structures are optimized, and tips on how to "check your indexes and execution plan" do not work for me. I am looking for a way to store a [possibly] byte presentation of a generic result set for a later return.

Answer 1

The most general approach in Oracle that I can think of is to create a stored procedure that converts the result set to XML and saves it as a CLOB XMLType in a table with the results of your lengthy queries.

You can find more about generating XML data from common result sets here .

SQL> select dbms_xmlgen.getxml('select employee_id, first_name,
  2  last_name, phone_number from employees where rownum < 6') xml
  3  from dual

Answer 2

First of all, 20-30 minutes is a very long time for the query - are you sure that you do not have any indexes for the query? Check your execution plan - you can get a huge performance boost from a well-placed index.

In MySQL you could do

INSERT INTO `cached_result_table` (
    SELECT your_query_here
)

(, cached_result_table , SELECT, ).

( ) - cached_result_table.

, , 20-30 , . , . :

init:
insert select your_big_query

work:
if your_big_query cached table is empty or nearing expiration:
  refresh in the background:
     check flag to see if there another "refresh" process running
     if yes
       end // don't run two your_big_queries at the same time
     else 
       set flag
       re-run your_big_query, save to cached table
       clear flag
serve data to clients always from cached table

Answer 3

Oracle "CREATE TABLE sometempname AS SELECT...". , .

Answer 4

, .

50 . , 40 , 10 .

, , , 40 ( ) 50. , , 40 () 50 , - ?

( DBMS_SCHEDULER DBMS_JOB). - , . , . PDF CSV Excel.

If you want 40 to work simultaneously with the settings of the 50 'pool, then you might be better off not creating a separate connection pool for long queries.

You can look in the resource manager to end calls that take too much time or too many resources. Thus, the quickie pool cannot get caught up in long requests.