Re: Passing one array per field into procedure.
Date: Wed, 23 Feb 2011 05:05:33 -0800 (PST)
Message-ID: <3a3446e5-a5a9-4f94-a6ea-da5271be94ec_at_y36g2000pra.googlegroups.com>
I've been involved with something similar a number of years ago and it worked very well.
That was on 10gR1.
I believe when you say "table of" you're referring to plsql associative arrays and for "arrays" you mean nested table types (i.e. create type).
At the time, we benchmarked different approaches and, in our cirumcstances, using Oracle collections outperformed standard java statement batching.
I'm not sure I'd go down the route of one array per field, although it's certainly viable.
My preference would normally be to create an Oracle object type (create type objecttypename as object (attr1, attr2...) and then collections of those object types (create type tabletypename as table of objecttypename). Certainly 30+ attributes to an object type would not give me any worries, depending on what those attributes were (e.g. CLOBS and BLOBS might give me second thoughts, especially if multiple large LOBS I'd probably rule out collections altogether).
These can then be used directly in INSERT ... SELECT statements, UPDATE statements, MERGE statements, etc using the TABLE(CAST...AS ...) structures - this might tie in easily with your data validation, foreign key lookups etc. This fits directly into the "Do it with a single SQL statement" mantra which normally delivers the best performance.
What you need to remember when dealing with collections in SELECT .. TABLE (CAST...) statements is how the default cardinalities are handled. It's related to the block size of the database but there are ways of providing better information about the size of collections.
Finally, the main downside to using Oracle collections of object types, is that the Java can be a bit clumsy. I've no idea how Oracle Fusion would make any of this easier or harder. I'm not a java programmer but I did a demo of some performance comparisons of statement batching versus oracle collections here: http://orastory.wordpress.com/2007/05/01/upscaling-your-jdbc-app/ You'll note my crappy java but it does show the INSERT... SELECT... TABLE statement to give you an idea of what I'm talking about. If it had been anything other than a demo, then I would have had that in a stored proc etc.
Hope this is relevant and helps.
Cheers,
Dominic
Received on Wed Feb 23 2011 - 07:05:33 CST