DWH load & partitioned tables

In DWH load, we use PL/SQL procedure to load data. The row is either skipped ( if all the columns are same), updated or inserted.

For the first, the statement for checking eqality is like. ( cur is cursor on staging table)

select count(*) from tb where keyfields = cur.keyfields and
((fld1 = cur.fld1 ) or (fld1 is null and cur.fld1 is null)) the last repeated for all non-primary fileds.

My problem is

1. When above is used for a partitioned table having over 140 columns, the query just hangs. It appears that the optimizer is unable to find execution plan for such a long statement. If I drop most columns, the query goes. Has anybody come accross such thing? The oracle is 8.1.6.2.

2. As a work around, I replaced the condition to as under

select count(*) from tb where keyfields = cur.keyfields and
(nvl(fld1,'0') = nvl(cur.fld1,'0') for each field & using sysdate for date fileds instead of '0'.

The qurey on table with 142 fields goes well and there is not degradation in performance due to use of NVL. In fact I found slight improvement. Is it OK.

3. Is there better method of skipping equal rows?

Hi,

I found a work around as under.

Remove all other fields from where condition & keep only key fields. Instead of count get all the fields into a record ( tab%rowtype) and leave equality checking to PL/SQL in a large IF THEN Condition.

The code sample is

tab_rec tab%rowtype;

Select * into tab_rec from tab where key_fields = cur.key_fields;

IF (tab_rec.f1 = cur.f1 or ( tab_rec.f1 is null and cur.f1 is null))
...

Then
return equal
else
return not equal
end if;
exception when no_data_found;
return not equal;
end;