Nn 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?
