This week's book giveaway is in the Design forum. We're giving away four copies of DevOps: A Software Architect's Perspective and have Len Bass, Ingo Weber & Liming Zhu on-line! See this thread for details.
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
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.