DWH load & partitioned tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: DWH load & partitioned tables

  1. #1
    Join Date
    Feb 2001
    Posts
    75

    Cool

    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?

  2. #2
    Join Date
    Feb 2001
    Posts
    75

    DWH & partitioning

    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width