wood burning stoves 2.0
The moose likes Oracle/OAS and the fly likes Heavy weight Insert query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Heavy weight Insert query" Watch "Heavy weight Insert query" New topic

Heavy weight Insert query

Murad Iqbal
Ranch Hand

Joined: Dec 09, 2003
Posts: 90
Hi all,

I am calling an Oracle stored procedure which runs an Insert statement on a 23 column table. The values supplied to the Insert are through a select statement that collects required data from different tables, does some string concatenation, uses TO_CHAR, Decode, NVL functions and a mixture of all to create values for individual columns. The Select statement also contains a Where clause which does 3 comparisons all of which should be true i.e. (condition AND condition AND condition). One of the conditions looks like this

TO_CHAR(purhc_dt,'DDMMYYYY')||accountnum||corp = trans_id.

The select may return several records, potentially 10-100, which are then inserted.

The result, as expected, the statement is very heavy on the Oracle db and it took ages to do anything before finally running out of temporary tablespace. Any comments/suggestions how to improve this?

[ September 07, 2006: Message edited by: Murad Iqbal ]
Mark Spritzler

Joined: Feb 05, 2001
Posts: 17276

For Oracle, it is very important to use explain plans on queries. This will tell you how Oracle will access the tables, when it will use an index and more important when it isn't and doing full table scans.

If you have where clauses that do complex stuff, then it is usually a good idea to create an index using that same complex stuff.

So for instance you might want to create an index on TO_CHAR(purhc_dt,'DDMMYYYY')||accountnum||corp

Also make sure that if you go to multiple tables that they are correctly joined and doesn't create a cartesian product, which sounds more like what is happening since you run out of temp space.


Perfect World Programming, LLC - iOS Apps
How to Ask Questions the Smart Way FAQ
Murad Iqbaal

Joined: Feb 02, 2012
Posts: 9

Thanks Mark. Though a little late, but that helped....

jQuery in Action, 3rd edition
subject: Heavy weight Insert query
It's not a secret anymore!