Man, you gotta be kidding. Without the tables and indexes and data on hand, nobody's going to be able to truly optimize something this complex. The only reason I'm looking at this at all is that I happen to be between fires and love doing this kinda stuff.

Basically, all I can do is give you some pointers.

First of all, a statement this large really needs some better formatting. Always put the new table fields on the right or the left (I prefer the right) to make it easier to read.

Okay, first real hint: ALWAYS JOIN EVERYTHING THAT'S JOIN-ABLE BETWEEN EVERY TABLE. I cannot stress this enough.

For example, you have:

AND b1.pty_id = a1.pty_id
as well as
AND p1.pty_id = b1.pty_id,

You also NEED to add:
AND p1.pty_id = a1.pty_id

Otherwise, the optimizer will never know that going from table a1 directly to table p1 is an option. The first rule of optimization is to always give the optimizer every possible chance to get it right. So cross-join every single field that is cross-joinable. This means joining to constant values as well (although Oracle can generally figure that one out on its own, IT CAN'T HURT!). Having too many joins will almost never hurt, but NOT having those legal joins often will hurt.

Moving on, if any of those joins are done simply to retrieve a name from a code table, or to only translate a single field, do them as sub-selects in the SELECT clause. While it is generally a better idea to pile everything into the WHERE clause, the rules change when outer-joins (as well as some other criteria like GROUP BYs, etc.) are involved. The optimizer is put at a disadvantage. If you know that all you are doing in the STATE table is translating the State_Cd to a State_Name, then do that in the SELECT clause so it will definitely be done last. I don't know if any of these fields fall under that heading, but branch sure looks like it does. I would pull that out and put it in the SELECT clause.

Since you can't do the 'join everything' trick where outer joins are concerned, you *really* need to know what your driving table is going to be. In this case, it is obviously a1. Therefore, wherever possible, join the outer-joines tables to *that* table. You have several cases where you are outer-joining to b1 where you could have used a1. Change these! Then, like I said above, pull b1 out to the SELECT clause. It appears to only be used to get some tax id.

Databases are set-oriented beasts. If there is *any* way of combining this into a single INSERT...SELECT statement by, say, using DECODES... DO IT. Unfortunately, you lose your ability to commit between rows.

The
SELECT COUNT (*)
INTO chk_pk
FROM acct_static_details asd
WHERE c1rec.clnt_id = asd.clnt_id
AND c1rec.acct_id = asd.acct_id
AND c1rec.pty_id = asd.pty_id;
inside the loop should have
AND ROWNUM = 1
appended to it. You don't care about the count, just the existence of a record.

I don't know if there is any way to re-write that last part of the WHERE clause with the DECODES and ORs and NULL tests, but do it if you can - those will kill you.

Finally, if none of this works, go back to the business rules at hand. This just strikes me as too cumbersome to be the best approach. Without knowing more I can't help much on this, but it just *seems* wrong. There almost *has* to be an easier way.

Hope this helps

- Chris