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 ]