CODEWITHSUNDEEP
databaseoracle-databaseplsqloracle11gdatabase-programming
Mehmet
Mehmet

Reputation: 2278

getting data from memory instead of table

I have a parameter table with 10 rows. Called parameter_table. In my PL/SQL procedure, I do loop in 2 million records. And each time querying this parameter table too.

I want to load this parameter table in to the memory and decrease the I/O process.

What is the best way to do this?

 FOR cur_opt
  IN (SELECT customer_ID,
             NVL (customer_type, 'C') cus_type
        FROM invoice_codes 
       WHERE ms.invoice_type='RT') 
LOOP
  ....
  ...

  Select data From parameter_table Where cus_type = cur_opt.cus_type AND cr_date < sysdate ;  -- Where clause is much complex than this..

....
...
END LOOP;

Upvotes: 2

Views: 154

Answers (3)

Mohsen Heydari
Mohsen Heydari

Reputation: 7284

I Think you may change the query ,joining to parameter_table, so there will be no need to hit the select statement inside the loop. (like what @Chris Saxon solution)

But as a way to use cashed data, You could fill a dictionary like, array and then refer it when necessary
Something like this may help:

you have to call Fill_parameters_cash before starting the main process and call get_parameter to fetch the data, the input parameter to call get_parameter is the dictionary key

TYPE ga_parameter_t IS TABLE OF parameter_table%ROWTYPE INDEX BY BINARY_INTEGER;
ga_parameter ga_parameter_t;

procedure Fill_parameters_cash is
  begin 
    ga_parameter.DELETE;
    SELECT * BULK COLLECT
    INTO   ga_parameter
    FROM   parameter_table;    
end Fill_parameters_cash;

FUNCTION get_parameter(cus_type invoice_codes.cus_type%TYPE,
                                 is_fdound    OUT BOOLEAN)
    RETURN parameter_table%ROWTYPE IS
    result_value parameter_table%ROWTYPE;

    pos NUMBER;
  BEGIN
    result_value := NULL;
    is_fdound := FALSE;
    IF cus_type IS NULL THEN
      RETURN NULL;
    END IF;  

    pos := ga_parameter.FIRST;
    WHILE pos IS NOT NULL
    LOOP
      EXIT WHEN ga_parameter(pos).cus_type  = cus_type;
      pos := ga_parameter.NEXT(pos);
    END LOOP; 
   IF pos IS NOT NULL THEN
      is_fdound    := TRUE;
      result_value := ga_parameter(pos);
    END IF;  
    RETURN result_value;
  END get_parameter;

Upvotes: 1

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

I'd guess looping through a million records is already causing issues. Not quite sure how this parameter table lookup is really worsening it.

Anyways, if this is really the only approach you can take, then you could do an inner or outer join in the cursor declaration.

----
 FOR cur_opt
  IN (SELECT customer_ID,
             NVL (customer_type, 'C') cus_type
        FROM invoice_codes codes,
             parameter_table par 
       WHERE ms.invoice_type='RT'
         and codes.cus_type = par.cus_type -- (or an outer join) maybe?
      ) loop
..........

Upvotes: 0

Chris Saxon
Chris Saxon

Reputation: 9805

You can just join it to your main query:

select customer_id, data
from   parameter_table t, invoice_codes c
where  t.cus_type = nvl(c.customer_type, 'C')
and    t.cr_date < sysdate

However, if you've got 2 million records in invoice_codes, then joining to the parameter table is the least of your concerns - looping through this will take some time (and is probably the real cause of your I/O problems).

Upvotes: 3

Related Questions