Clever Geek Handbook

External tables Oracle. Optimizing selected queries

I need to do many selections from an external Oracle table.

I have 10 cursors that are very similar (ext_temp is an external table)

CURSOR F_CURSOR (day IN varchar,code Number,orig Number)
    IS
    select NVL(sum(table_4.f),0) 
     from ext_temp table_4
    where
      --couple of conditions here, irrelevant for the question at hand.
      AND TO_CHAR(table_4.day,'YYYYMMDD') = day
      AND table_4.CODE = code
      AND table_4.ORIG = orig;

And the outer table has about 22659 registers.

My script main loop looks like this

   for each register in some_query: --22659 registers
       open F_cursor(register.day,register.code,register.orig);
       --open 9 more cursors

       fetch F_cursor into some_var;  
       --fetch 9 more cursors, with the same structure

Requests go far. And I know from here that I cannot have any indexes or DMLs.

So, is there a way to make it run faster? I can rewrite my plsql script but I don't think I have any time left.

Update: An important detail is missing.

I am not the owner or administrator of the database. This guy doesn't want to get additional information (about 3GB of data) in his database, and external tables are all we could get from him. It doesn't allow creating temporary tables. I do not pretend to ask about its causes, but external tables are not a solution for this. So we are stuck with them.

+2


source to share


4 answers


Make them Oracle

tables.

External tables are replaced SQL*LOADER

rather than working with them on a daily basis.

Just run the import script whenever you change the base file that loads the contents of the external table into the table Oracle

.

Here's what the namesake thinks of it (stolen from here ):

you are using external tables instead sqlldr

.

with external tables you can

  • concatenate flat file with existing table in one expression.
  • Sort the flat file on the path to the table you want to compress.
  • perform parallel loading of direct path - without splitting the input file, writing umpteen scripts, etc.
  • run sqlldr

    essentially from a stored procedure or trigger (insert is not sqlldr

    )
  • make inserts with multiple tables
  • pass data through plsql pipelined function for cleanup / transformation

etc. they are instead of sqlldr

- to get the data in the database without using it sqlldr

in the first place.

Usually you didn't ask them every day in the operating system, you use them to load data.



Update:

You will never get decent performance with a table 3GB

as Oracle

it will have to do fullscan 3GB

for every query and it will be a top-notch full-text disk reading engine, not the cheap cached imitation you can see in the plan but barely notice in the actual runtime ...

Try convincing the guy to create a temporary table for you that you can use to manipulate the data, and just load the data from the external table whenever your session starts.

This is not the best solution because the temporary tablespace needs to keep a separate copy of the table for each session, but it is much better in performance.

+4


source


This is very difficult if you need to work around restrictions that don't make sense, but that you cannot change ...

You should be better off reading through an external table once and then creating the data you need in an indexed data structure in your code (basically an array with one element for each register you're looking for).

So your cursor will look like this:

CURSOR F_CURSOR (day IN varchar, orig IN Number)
    IS
    select NVL(sum(table_4.f),0) value, table_4.CODE register
     from ext_temp table_4
    where
      --couple of conditions here, irrelevant for the question at hand.
      AND TO_CHAR(table_4.day,'YYYYMMDD') = day
      -- AND table_4.CODE = code -- don't use this condition!
      AND table_4.ORIG = orig;


And your loop-register will turn into a loop-cursor:

open F_cursor(register.day,register.orig);
LOOP
    fetch F_cursor into some_var;
    EXIT WHEN F_cursor%NOT_FOUND
    result (some_var.register) := some_var.value;
END LOOP;

As a result, instead of looping through the external table for each register, you only need one loop for all registers.

This can be extended to the ten cursors you mentioned.

+3


source


You can write your external table data to the temporary table indexed (if you like) and then do multiple queries on it.

create your_temp_table as select * from ext_temp;
create index your_desired_index on your_temp_table(indexed_field);

Then execute all your queries directly using your_temp_table.

0


source


While fully agreeing with Quassnoi that external tables are not a proper solution here, and the DCookie analogy that you are tied and thrown overboard and asked to float, perhaps there is at least a way to structure your program so that the external table is read just one time. My belief in your description is that all 10 cursors are read from the external table, which means that you are forcing Oracle to scan the external table 10 times.

Assuming this conclusion is correct, the simplest answer will most likely cause the outer table to move with a cursor similar to what IronGoofy suggested. Depending on what some_query

the below code snippet does ,

for each register in some_query

and assuming that the fact that the query returns the same number of rows that are in the outer table is not a match, the simplest option would be to do something like

FOR register in (select * from ext_temp)
LOOP
  -- Figure out if the row should have been part of cursor 1
  IF( <<set of conditions>> ) 
  THEN
    <<do something>>
  -- Figure out if the row should have been part of cursor 2
  ELSIF( ... )
  ...
END LOOP;

or

FOR register in (select * 
                   from ext_temp a, 
                        (<<some query>>) b 
                  where a.column_name = b.column_name )
LOOP
  -- Figure out if the row should have been part of cursor 1
  IF( <<set of conditions>> ) 
  THEN
    <<do something>>
  -- Figure out if the row should have been part of cursor 2
  ELSIF( ... )
  ...
END LOOP;

It should be more efficient to go a step further and draw logic out of cursors (and IF statements) and into the motion pointer. Using the simpler code snippet above (you could, of course, join some_query

these examples

FOR register in (select a.*,
                        NVL(sum( (case when condition1 and condition2
                                       then table_4.f
                                       else 0
                                       end) ),
                             0) f_cursor_sum
                  from ext_temp table_4)
LOOP
  <<do something>>
END LOOP;

If even after that, you still find yourself doing some line processing, you can go a step further and BULK COLLECT from a motion pointer to a locally declared collection and work with that collection. You almost certainly don't want to fetch 3GB of data into your local collection (although crushing the PGA might cause the DBA to conclude that the temp tables aren't all that bad, that's not what I would recommend) by choosing multiple a hundred lines at a time using the LIMIT clause should make things more efficient.

0


source






More articles:


All Articles