getting data from memory instead of table

Question

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;

I'd be really surprised if Oracle doesn't keep the entire parameter table into buffer cache anyway. Your trying to fix a problem that doesn't exist I think (at least not due to a 10 row parameter table). — tbone, Mar 05 '13 at 21:54

score 3 · Answer 1 · answered Mar 04 '13 at 20:28

3

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).

answered Mar 04 '13 at 20:28

Chris Saxon

9,105
1
26
42

1

upvoted because that's obviously the simplest thing to do, I didn't pay attention enough. But please, use ANSI joins. – Sebas Mar 04 '13 at 20:35
Sorry, I can not join because of the program flow. – Mehmet Mar 04 '13 at 20:38
1

If you have additional restrictions on what you can do, then please update your question with all the relevant details. I'm not sure how making this a join would affect the program flow however. – Chris Saxon Mar 04 '13 at 20:40
Because there is a dependency between variables in where clause above the select query.Question is edited – Mehmet Mar 04 '13 at 20:52
Disk I/O to parameter table is not source of problems. 10 rows fit into a single page, which can be easily kept in buffer cache. 2 millions of consecutive executions for a single query makes much bigger overhead. Joining is the solution. This will also fix possible phantom reads. – ibre5041 Mar 05 '13 at 10:11
@Jack - it's still not clear exactly why this join isn't possible in your main query. – Chris Saxon Mar 05 '13 at 12:30

Mohsen Heydari · Answer 2 · 2013-03-06T06:03:53.430

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;

score 0 · Answer 3 · answered Mar 04 '13 at 20:29

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
..........

getting data from memory instead of table

3 Answers3