2

I'm using Oracle's PL/SQL and would like to create an in memory table from which I can do selects.

Let's say I have a table with only 20 records with two columns: special_id(int), out_date(date)

I want to collect those 20 records and stick them in memory so I don't have to do the same select 10,000 times and I want to be able to access those 20 records later.

I then have a loop running a different query and I'd like to do the equivalent of the following inside that loop:

select out_date 
from in_memory_table 
where in_memory_table.special_id = cursor.special_id (where cursor is from my current loop).

NOTE: It makes no sense to loop through the in_mempry_table under ANY circumstance. I just need to be able to access the data in that table.

I'm not including actual code here because I'd have to do a heck of a lot of work recoding so as to not give away company info.

Wicked Jester
  • 53
  • 2
  • 4
  • 4
    What sort of table do you have? If you have a nested table that is defined in SQL, you could `select out_date from table(local_collection_variable) where ...`. Though it generally doesn't make a whole lot of sense to grab a bunch of data from SQL, load it into the PL/SQL VM, and then push it all back to the SQL VM. I would guess that you'd be better off joining whatever table you use to populate the collection to the tables in your cursor definition. – Justin Cave Aug 24 '16 at 20:02
  • 1
    A regular table will automatically be kept "in memory" (=buffer cache) if there is enough memory and the table is accessed often enough. You can even tell Oracle to pin the table's data to the buffer cache. –  Aug 24 '16 at 21:17
  • I can't use a regular table and I can't just access it via a join. There's a lot going on here that makes this situation difficult. I guess I'll just have to use a real scripting language instead of this abomination called PL/SQL. – Wicked Jester Aug 24 '16 at 23:17
  • 1
    @WickedJester Why can't you use a regular table or a join? Are you worried about performance or simplifying the code? If it's performance, it only takes about half a second to select all rows from a 20-row table 10,000 times. – Jon Heller Aug 25 '16 at 03:25
  • 2
    Are you maybe looking for a collection then? https://docs.oracle.com/database/121/LNPLS/composites.htm#LNPLS00501 –  Aug 25 '16 at 11:33
  • 1
    I can assure you that PL/SQL is neither an abomination nor a scripting language. – William Robertson Aug 25 '16 at 12:42

1 Answers1

1

As said above, it might not have much sense to do so, but you could use some kind of hashmap to do the job for you. This uses associative arrays.

1) populate the hashmap at the beginning of your program

for x in (select out_date, special_id from input_table) loop
   my_hashmap(x.special_id) := x.out_date;
end loop;

2) instead of the select, you get the out_date when you need it with a 

curr_out_date := my_hashmap(cursor.special_id);

3) enjoy, hope it helps make plsql friendlier to you

Community
  • 1
  • 1
J. Chomel
  • 8,193
  • 15
  • 41
  • 69