Home » SQL & PL/SQL » SQL & PL/SQL » How to store data from mapping table into variables(arrays) and using variables to get mapping info (Oracle 10g)
How to store data from mapping table into variables(arrays) and using variables to get mapping info [message #376560] Wed, 17 December 2008 19:07 Go to next message
prashas_d
Messages: 66
Registered: February 2007
Member
Hi All,

I have a requirement that I have to read a huge table having over 1 million records and do some processing on each record.

While processing each record, I have to look another mapping table let say MAPPINGDATA that has 4 columns and has around 2 to 3 hundred records. I have to query this table using 3 columns and the get the value in corresponding 4th column.

Well, I feel that for each of 1 million record, quering another table MAPPINGDATA will be very costly. So I am thinking of storing this table data 'MAPPINGDATA' into some memory like arrays and use this memory for quering with 3 values and get the value in 4th column.

Basically what I have to implement using memory is something like in sql as
select column_4 from MAPPINGDATA 
where  column_1 = <> 
and    column_2 = <>
and    column_3 = <>;

Can someone please suggest me is there any PLSQL concept that I can apply here.

Regards,
prashas_d.
Re: How to store data from mapping table into variables(arrays) and using variables to get mapping i [message #376592 is a reply to message #376560] Wed, 17 December 2008 23:54 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hi,

In this case you can use two cursors.

Outer cursor will take one record from MAPPINGDATA.
and according to that record you fetch data from first table using Parameterised cursor.

Hope this will help.

regards,
Delna
Re: How to store data from mapping table into variables(arrays) and using variables to get mapping i [message #376599 is a reply to message #376560] Thu, 18 December 2008 00:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
prashas_d wrote on Thu, 18 December 2008 02:07
Well, I feel that for each of 1 million record, quering another table MAPPINGDATA will be very costly. So I am thinking of storing this table data 'MAPPINGDATA' into some memory like arrays and use this memory for quering with 3 values and get the value in 4th column.


Bad idea, based on nothing but your (probably wrong) gut feeling.
Let the database do what it's made for. At least test it instead of going by your "feeling"
Re: How to store data from mapping table into variables(arrays) and using variables to get mapping i [message #376605 is a reply to message #376560] Thu, 18 December 2008 00:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So I am thinking of storing this table data 'MAPPINGDATA' into some memory like arrays and use this memory for quering with 3 values and get the value in 4th column.

Well, it is already there ready for you, it is... buffer cache. Possibly use keep buffer, only if it is useful for the overall workload and not just your process.
Just put an index on your 3 columns and let Oracle do the stuff for you.

Regards
Michel
Re: How to store data from mapping table into variables(arrays) and using variables to get mapping i [message #376616 is a reply to message #376560] Thu, 18 December 2008 00:56 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Just an idea: join the MAPPINGDATA table to the processed table during "read a huge table". Use outer join if you want to also process rows without mapping.

To "read a huge table having over 1 million records and do some processing on each record" will surely affect performance worse; if it is possible to rewrite to single SQL statement, I would go for it.
Re: How to store data from mapping table into variables(arrays) and using variables to get mapping i [message #376643 is a reply to message #376560] Thu, 18 December 2008 02:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I agree with the other posters that a SQL based solution (joining the two queries together) is almost certainly the best solution.

If however you decide to go with a lookup table, here's an example of how to use one.
YOu can have a pl/sql table with a varchar2 key. Simply construct a unique string to represent the three columns that you want to use to identify a row (in this case, I simply express each number as an 8 chr 0 padded string and concatenate them) and use that as the index value for a row in the lookup table:
create table test_047  (col_1  number, col_2 number, col_3 varchar2(100));

insert into test_047 select floor(level/10),mod(level,10),'Row '||level from dual connect by level <= 100;

declare
  type ty_tab is table of varchar2(100) index by varchar2(100);
  
  t_tab  ty_tab;
  v_key  varchar2(100);
  function make_key (p_1 in number
                    ,p_2 in number) return varchar2 is
  begin
    return to_char(p_1,'00000000')||to_char(p_2,'00000000');
  end;
begin
  for rec in (select * from test_047) loop
    v_key := make_key(rec.col_1,rec.col_2);
    
    t_Tab(v_key) := rec.col_3;
  end loop;

  dbms_output.put_line('Row 4,5 Value:'||t_tab(make_key(4,5)));
  dbms_output.put_line('Row 7,8 Value:'||t_tab(make_key(7,8)));
  
end;
/
Re: How to store data from mapping table into variables(arrays) and using variables to get mapping i [message #376849 is a reply to message #376643] Thu, 18 December 2008 21:03 Go to previous message
prashas_d
Messages: 66
Registered: February 2007
Member
Hi JRowBottom,

This is really amazing !!

Earlier, I used to fire a select query on that MAPPINGDATA table for each record. The time taken with this approach is around 10 mins for 100,000 records in that master table.

But now with your lookup-table approach, the time has reduced to just 1 min & 22 sec for the same 100,000 records. This is exactly what I was looking for.

Thanks a lot !! This is not the first time that you have helped me. I owe you a lot Smile

@flyboy, I agree with your idea. But in my case I have to do some other business processing before calling that mappingdata table. So outer join doesn't help in my case here.

@Micheal, I am yet to try with the approach that you have suggested. I will let you know after trying it.

Thanks to everyone for your responses !!

Regards,
prashas_d.
Previous Topic: adding space between rows
Next Topic: "view" data of last week,month,year
Goto Forum:
  


Current Time: Sat Dec 10 16:54:24 CST 2016

Total time taken to generate the page: 0.19607 seconds