>>> You are perfoming two ROW_NUMBER analytical operations, and discarding>>> the results. The ROW_NUMBER operation is sorting quite a few rows and>>> this sort is likely hitting the temporary tablespace quite hard. Your>>> query includes TAB3.RANK = 1 and TAB2.RANK = 1, but not TAB3.RANK =>>> TAB2.RANK. The explain plan would indicate if you are performing a>>> full table scan on TABLE1's 46,697,622 rows.

>>> Do you need the outer join? If possible, remove that and performance>>> may improve considerably. I could be wrong, but I believe that the>>> CASE statements may not be functioning as expected if an outer join>>> would be required to retrieve all rows, for instance: TAB2.CNAME NOT IN>>> ('PN','AR') - this may need to be rewritten as NVL(TAB2.CNAME,'PN')>>> NOT IN ('PN','AR') to obtain the expected results.

And, in addition, do your future explain plans using DBMS_XPLAN ... not
whatever script you are using. DBMS_XPLAN will give the amount of TEMP
space used and other valuable information. Look at
$ORACLE_HOME/rdbms/admin/utlxplp.sql.

Note also the "note" about plan_table being the old version. You should
run catplan.sql (if available) and use the newest version.