Noetix: Manually Rebuilding Cached KFF Data

Sometimes it's necessary to rebuild the cached KFF data that Noetix uses to speed up it's queries. These are typically the tables under the NOETIX_SYS schema that start with KFF_ followed by a code representing the data to be refreshed (i.e. GL_ACCT for General Ledger Account Code Combinations).

This refresh can take quite a time and it's useful (if you are in a highly controlled environment) to be able to hand over to people some statistics regarding the rebuild. Hence the script below.

The script looks at the packages that have been built under your Noetix schema and then into the tables the packages update to determine the current number of rows then it performs the initial upload (a full refresh) and then re-checks the number of records in the table - hopefully this will have changed!

Here is the script;

declare
  -- Local variables here
  v_Item varchar2(255);
begin
  -- Test statements here
  for v_Data in (SELECT ao.object_name,
                        SUBSTR(ao.object_name,
                               7,
                               LENGTH(ao.object_name) - 10) item_name
                   FROM all_objects ao
                  WHERE ao.owner = 'NOETIX_SYS'
                    AND ao.object_name LIKE 'N_KFF_%_PKG'
                    AND ao.object_type = 'PACKAGE') loop
    dbms_output.put_line(RPAD('===== ', 60, '= ') ||
                         TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
    dbms_output.put_line('Processing ' || v_Data.item_name);
    dbms_output.put_line('  - Package name      : N_KFF_' ||
                         v_Data.item_name || '_PKG');
    dbms_output.put_line('  - Data Table name   : N_KFF_' ||
                         v_Data.item_name);
    EXECUTE IMMEDIATE 'SELECT TO_CHAR(COUNT(*)) FROM N_KFF_' ||
                      v_Data.item_name
      INTO v_Item;
    dbms_output.put_line('  - Cache Table Count : ' || v_Item || ' (BEFORE)');
    dbms_output.put_line(RPAD('== Initial Upload == ', 20, '= ') ||
                         TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
    EXECUTE IMMEDIATE 'BEGIN N_KFF_' || v_Data.item_name ||
                      '_PKG.Init_Upld(); END;';
    dbms_output.put_line(RPAD('== Initial Upload == ', 20, '= ') ||
                         TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
    dbms_output.put_line('  - Cache Table Count : ' || v_Item || ' (AFTER)');
    dbms_output.put_line(RPAD('===== ', 60, '= ') ||
                         TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
  end loop;
end;

And here is a sample of the output generated on our system;


===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:46:31
Processing ACCALIAS
  - Package name      : N_KFF_ACCALIAS_PKG
  - Data Table name   : N_KFF_ACCALIAS
  - Cache Table Count : 1 (BEFORE)
== Initial Upload ==16-DEC-2011 15:46:31
Truncating Table N_KFF_GL_Acct
Initial Upload Started ...
Populating Table N_KFF_AccAlias with data
Initial Upload insert complete ...
Initial Upload completed ...
Time Taken for gather stats for data cache table in secs : 9
Initial Upload completed ...
 
== Initial Upload ==16-DEC-2011 15:46:31
  - Cache Table Count : 1 (AFTER)
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:46:31
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:46:31
Processing CTLG_GRP
  - Package name      : N_KFF_CTLG_GRP_PKG
  - Data Table name   : N_KFF_CTLG_GRP
  - Cache Table Count : 0 (BEFORE)
== Initial Upload ==16-DEC-2011 15:46:31
Truncating Table N_KFF_GL_Acct
Initial Upload Started ...
Populating Table N_KFF_Ctlg_Grp with data
Initial Upload insert complete ...
Initial Upload completed ...
Time Taken for gather stats for data cache table in secs : 23
Initial Upload completed ...
 
== Initial Upload ==16-DEC-2011 15:46:31
  - Cache Table Count : 0 (AFTER)
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:46:31
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:46:31
Processing GL_ACCT
  - Package name      : N_KFF_GL_ACCT_PKG
  - Data Table name   : N_KFF_GL_ACCT
  - Cache Table Count : 639234 (BEFORE)
== Initial Upload ==16-DEC-2011 15:46:31
Truncating Table N_KFF_GL_Acct
Initial Upload Started ...
Populating Table N_KFF_GL_Acct with data
Initial Upload insert complete ...
Initial Upload completed ...
Time Taken for gather stats for data cache table in secs : 655
Initial Upload completed ...
 
== Initial Upload ==16-DEC-2011 15:47:37
  - Cache Table Count : 639234 (AFTER)
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:47:37
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:47:37
Processing ITEM_LOC
  - Package name      : N_KFF_ITEM_LOC_PKG
  - Data Table name   : N_KFF_ITEM_LOC
  - Cache Table Count : 30845 (BEFORE)
== Initial Upload ==16-DEC-2011 15:47:37
Truncating Table N_KFF_GL_Acct
Initial Upload Started ...
Populating Table N_KFF_Item_Loc with data
Initial Upload insert complete ...
Initial Upload completed ...
Time Taken for gather stats for data cache table in secs : 37
Initial Upload completed ...
 
== Initial Upload ==16-DEC-2011 15:47:38
  - Cache Table Count : 30845 (AFTER)
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:47:38
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:47:38
Processing MTL_CAT
  - Package name      : N_KFF_MTL_CAT_PKG
  - Data Table name   : N_KFF_MTL_CAT
  - Cache Table Count : 1740 (BEFORE)
== Initial Upload ==16-DEC-2011 15:47:38
Truncating Table N_KFF_GL_Acct
Initial Upload Started ...
Populating Table N_KFF_Mtl_Cat with data
Initial Upload insert complete ...
Initial Upload completed ...
Time Taken for gather stats for data cache table in secs : 46
Initial Upload completed ...
 
== Initial Upload ==16-DEC-2011 15:47:40
  - Cache Table Count : 1740 (AFTER)
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:47:40
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:47:40
Processing SYS_ITEM
  - Package name      : N_KFF_SYS_ITEM_PKG
  - Data Table name   : N_KFF_SYS_ITEM
  - Cache Table Count : 690403 (BEFORE)
== Initial Upload ==16-DEC-2011 15:47:40
Truncating Table N_KFF_GL_Acct
Initial Upload Started ...
Populating Table N_KFF_Sys_Item with data
Initial Upload insert complete ...
Initial Upload completed ...
Time Taken for gather stats for data cache table in secs : 189
Initial Upload completed ...
 
== Initial Upload ==16-DEC-2011 15:48:35
  - Cache Table Count : 690403 (AFTER)
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:48:35