Re: Alternate for Varrays in Oracle
I have just recently implemented something similar, with good results.
The problem: I needed to do hundreds, sometimes thousands, of SQL statements from a remote location (80 millisecond roundtrip between client and server (Master in a replication environment)).
Solution: Use a temp table for the variable amount of data, call a stored procedure. 2 roundtrips needed (INSERT and CALL). The actual implementation used 5:
The program sits there all day, gathering variable amounts of data, then calling the SP. When the program starts, it does two CREATE TABLEs. (The structure of the data necessitated 2, not 1, tables.)
The main loop:
* Gather some data
* TRUNCATE TABLE for the two tables.
* Build two multi-row INSERT statements (one per table), and execute them
* CALL the SP with essentially no arguments other than the two tables (implicitly).
This led to two speedups.
* Drastic decrease in roundtrips
* Doing things in "batch". That is, doing a single SQL statement to move (copy, update, whatever) the data from the tmp table(s) to the 'real' tables is a lot faster than doing them one row at a time. However,...
One challenge was normalization. A lot of values were inserted into the tmp tables as strings, then turned into ids ("normalization"). This task is straightforward, but slow, when done one item at a time. When done en masse, I found this to be the best:
1. UPDATE tmp JOIN norm ON ... SET tmp.id = norm.id;
2. INSERT INTO norm (...) SELECT ... FROM tmp WHERE tmp.id IS NULL;
3. UPDATE tmp JOIN norm ON ... SET tmp.id = norm.id WHERE tmp.id IS NULL; (again)
#2 creates any new entries in the normalization table.
#3 copies the ids into the tmp table -- for subsequent operations.
#1 is 'necessary' because... In InnoDB, #2 will allocate lots of ids, even if it later decides they are not needed. This "burning" of ids is avoided by doing #1.
Logically TRUNCATE should be at the end of the loop. But by moving it to the beginning, the tables are kept around for debugging.
Another detail... The Stored Procedure needs to know the names of the tmp tables.
Plan A: Pass the name in, and do lots of PREPARE, etc, calls.
Plan B: Construct the SP and the CREATE TABLEs (I used Perl) with a new name every time the program is run. (In theory (hehe) the program is started once and runs forever.)
I picked Plan B.