Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Agile forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Heavy weight Insert query

 
Murad Iqbal
Ranch Hand
Posts: 90
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?

Regards,
Murad.
[ September 07, 2006: Message edited by: Murad Iqbal ]
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Mark
 
Murad Iqbaal
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


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

Cheers,
Murad.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic