This section is for Cursor based procedures only. It is not relevant for Set based or other types.
When generating the cursor-based procedures to update the staging table, WhereScape RED includes an Update/Insert logic. This logic first attempts to update an existing staging record and then inserts a new record, if the record does not exist. 
This build option should be altered once an understanding of the data you are dealing with is acquired.
For the initial prototype and testing phases of a data warehouse analysis area we need to find out if our business keys for the fact table are genuinely unique. The update/insert logic in the staging procedure will only perform an 'Update' if our business key is not unique.
In this case, we need to decide if this is a failure in the business key, or some form of additive record situation we need to handle and code accordingly.

Removing the Update Component

Once we have resolved any data issues, we would normally remove the update component to speed up the processing of the stage data. There are two ways of doing this. First, we can clear the Include Update Statement check-box when defining the table business key. This will result in a procedure with only an insert statement. The other option is to do it manually as follows:
There are normally three steps to removing the update component. These being:

  1. The update makes use of an index that can also be deleted in most situations.
  2. The actual Update statement can be removed from the code such that the insert is always executed.
  3. The procedure also includes an analyze of the table after 1000 rows have been inserted. This code is present to ensure that the index is used in the update. We can now remove this code. It would normally look like the following (Oracle version):
    --=======================================
-- If 1000 or more rows inserted then perform an analyze
--=======================================
IF v_insert_count >= 1000 And v_analyze_flag = 0 THEN
    v_analyze_flag := 1;
    v_Sql := 'analyze table stage estimate statistics sample 5 percent for all indexed columns';
    execute immediate v_Sql;
END IF;
  • No labels