Transcend and Constraint Maintenance
In this posting, I explained the Transcend product as a new offering here at Rittman Mead, and I went on to demonstrate it's use with index maintenance. Now, I'd like to demonstrate another staple of data warehouse load routines: constraint maintenance.
I constructed a complete replica of the SH schema called SH_NEW for use in this example. Assuming that this schema contains an actual data warehouse that we want to load data into, let's suppose that we want to disable some constraints prior to loading one of the fact tables. We aren't exactly sure which constraints we want to disable, and we're a little lazy and don't want to query the data dictionary. Transcend gives us what's called Debug Mode, and it allows us to see what the framework WOULD DO with a particular command, without actually doing it. This works for all aspects of the framework, and is initiated with a single command:
SQL> BEGIN 2 3 trans_adm.start_debug; 4 5 END; 6 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.45 SQL>
All Transcend packages and underlying code objects are written to understand that, while a session is in Debug Mode, no DDL statements should be executed inside that session. There is still plenty of recursive SQL that goes on inside that session: queries against Oracle dictionary objects and Transcend tables, auditing and logging, etc. However, the session is in "do no harm" mode, and everything the framework does in this mode is benign. We could go back to the default Runtime Mode by issuing the TRANS_ADM.STOP_DEBUG procedure.
Now I'll use Debug Mode to see all the constraints that would get disabled with a particular command, so that I can evaluate whether I want to disable all of these, or perhaps just a subset:
SQL> BEGIN 2 trans_etl.disable_constraints( 3 p_table => 'sales', 4 p_owner => 'sh_new' 6 ); 7 END; 8 / SQL: alter table SH_NEW.SALES disable constraint SALES_CHANNEL_FK Constraint SALES_CHANNEL_FK disabled on SH_NEW.SALES SQL: alter table SH_NEW.SALES disable constraint SALES_CUSTOMER_FK Constraint SALES_CUSTOMER_FK disabled on SH_NEW.SALES SQL: alter table SH_NEW.SALES disable constraint SALES_PRODUCT_FK Constraint SALES_PRODUCT_FK disabled on SH_NEW.SALES SQL: alter table SH_NEW.SALES disable constraint SALES_PROMO_FK Constraint SALES_PROMO_FK disabled on SH_NEW.SALES SQL: alter table SH_NEW.SALES disable constraint SALES_TIME_FK Constraint SALES_TIME_FK disabled on SH_NEW.SALES SQL: alter table SH_NEW.SALES disable constraint SYS_C0014014 Constraint SYS_C0014014 disabled on SH_NEW.SALES SQL: alter table SH_NEW.SALES disable constraint SYS_C0014009 Constraint SYS_C0014009 disabled on SH_NEW.SALES SQL: alter table SH_NEW.SALES disable constraint SYS_C0014010 Constraint SYS_C0014010 disabled on SH_NEW.SALES SQL: alter table SH_NEW.SALES disable constraint SYS_C0014011 Constraint SYS_C0014011 disabled on SH_NEW.SALES SQL: alter table SH_NEW.SALES disable constraint SYS_C0014012 Constraint SYS_C0014012 disabled on SH_NEW.SALES SQL: alter table SH_NEW.SALES disable constraint SYS_C0014013 Constraint SYS_C0014013 disabled on SH_NEW.SALES SQL: alter table SH_NEW.SALES disable constraint SYS_C0014008 Constraint SYS_C0014008 disabled on SH_NEW.SALES 12 constraint disablement processes for SH_NEW.SALES executed PL/SQL procedure successfully completed. Elapsed: 00:00:00.13 SQL> select distinct status from all_constraints where owner='SH_NEW'; STATUS ------------ ENABLED 1 row selected. Elapsed: 00:00:00.11 SQL>
We are able to evaluate the DDL that the command generates without executing it. In the statement above, we can see multiple check constraints, as well as multiple foreign key constraints. To give us three types of constraints to work with, I'll go ahead and build a primary key on the SALES table, which is usually made up of all the combinations of foreign keys to the dimension tables:
SQL> alter table sh_new.sales add constraint sales_pk 2 primary key (prod_id, cust_id, time_id, channel_id, promo_id); Table altered. Elapsed: 00:00:07.70 SQL>
Now, I can use the parameter P_CONSTRAINT_TYPE to determine which types of constraints I want to disable. This parameter actually accepts a regular expression, so it will match with any of the constraint types from the ALL_CONSTRAINTS table, such as P, R, C, etc., but if I wanted to specify only two types of constraints, I can do that as well:
SQL> BEGIN 2 trans_etl.disable_constraints( 3 p_table => 'sales', 4 p_owner => 'sh_new', 5 p_constraint_type => 'p|c' 6 ); 7 END; 8 / Constraint SALES_PK disabled on SH_NEW.SALES Constraint SYS_C0014008 disabled on SH_NEW.SALES Constraint SYS_C0014009 disabled on SH_NEW.SALES Constraint SYS_C0014014 disabled on SH_NEW.SALES Constraint SYS_C0014011 disabled on SH_NEW.SALES Constraint SYS_C0014012 disabled on SH_NEW.SALES Constraint SYS_C0014013 disabled on SH_NEW.SALES Constraint SYS_C0014010 disabled on SH_NEW.SALES 8 constraint disablement processes for SH_NEW.SALES executed PL/SQL procedure successfully completed. Elapsed: 00:00:00.75 SQL> select constraint_type, status, count(*) from all_constraints 2 where owner='SH_NEW' and table_name='SALES' group by constraint_type, status; C | STATUS | COUNT(*) - | ------------ | ---------- R | ENABLED | 5 C | DISABLED | 7 P | DISABLED | 1 3 rows selected. Elapsed: 00:00:00.20 SQL>
Enabling is just as easy... using different constraint types, or using constraint names as regular expressions. As I demonstrated in the previous post, I can also use the Oracle Scheduler to queue processes up so they execute concurrently. However, this can be dangerous with constraints, so be sure the combination of constraints to be built won't block one another.
SQL> BEGIN 2 trans_etl.enable_constraints( 3 p_table => 'sales', 4 p_owner => 'sh_new', 5 p_constraint_regexp => 'sys', 6 p_concurrent => 'yes' 7 ); 8 END; 9 / Oracle scheduler job CONSTRAINT_MAINT101 created Oracle scheduler job CONSTRAINT_MAINT101 enabled Constraint SYS_C0014008 enabled on SH_NEW.SALES Oracle scheduler job CONSTRAINT_MAINT102 created Oracle scheduler job CONSTRAINT_MAINT102 enabled Constraint SYS_C0014009 enabled on SH_NEW.SALES Oracle scheduler job CONSTRAINT_MAINT103 created Oracle scheduler job CONSTRAINT_MAINT103 enabled Constraint SYS_C0014010 enabled on SH_NEW.SALES Oracle scheduler job CONSTRAINT_MAINT104 created Oracle scheduler job CONSTRAINT_MAINT104 enabled Constraint SYS_C0014014 enabled on SH_NEW.SALES Oracle scheduler job CONSTRAINT_MAINT105 created Oracle scheduler job CONSTRAINT_MAINT105 enabled Constraint SYS_C0014012 enabled on SH_NEW.SALES Oracle scheduler job CONSTRAINT_MAINT106 created Oracle scheduler job CONSTRAINT_MAINT106 enabled Constraint SYS_C0014013 enabled on SH_NEW.SALES Oracle scheduler job CONSTRAINT_MAINT107 created Oracle scheduler job CONSTRAINT_MAINT107 enabled Constraint SYS_C0014011 enabled on SH_NEW.SALES 7 constraint enablement processes for SH_NEW.SALES submitted to the Oracle scheduler PL/SQL procedure successfully completed. Elapsed: 00:00:06.16 SQL>
Another option with constraints is validating them. This is useful with the primary key of a fact table, because the underlying index of that primary key is all but useless: it's not valuable in any typical star schema queries, and the space it consumes could be considerable. Keeping the constraint disabled is another option, but in some cases, validating that a constraint could be disabled goes a long way with data quality initiatives. So I'll start by turning up the logging level so we can see the actual DDL that is generated. I'll validate the primary key on the SALES table, and then attempt to insert some data into it:
SQL> BEGIN 2 3 trans_adm.set_module_conf( 4 p_logging_level=> 3 5 ); 6 7 END; 8 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 SQL> SQL> BEGIN 2 trans_etl.validate_constraints( 3 p_table => 'sales', 4 p_owner => 'sh_new', 5 p_constraint_type => 'p', 6 p_concurrent => 'no' 7 ); 8 END; 9 / SQL: alter table SH_NEW.SALES modify constraint SALES_PK validate Constraint SALES_PK validated on SH_NEW.SALES 1 constraint process for SH_NEW.SALES executed PL/SQL procedure successfully completed. Elapsed: 00:00:08.71 SQL> insert into sh_new.sales values (1,1,trunc(sysdate),1,1,10,10); insert into sh_new.sales values (1,1,trunc(sysdate),1,1,10,10) * ERROR at line 1: ORA-25128: No insert/update/delete on table with constraint (SH_NEW.SALES_PK) disabled and validated Elapsed: 00:00:00.02 SQL>
To be able to insert rows into a table with a constraint that is disabled and validated, we would have to either disable it (most likely) or enable it first.
Finally, for our last example, I'll demonstrate the parameter P_BASIS. Though it's probably poorly named, what it actually means is "which object is the BASIS of the constraint maintenance: the table that holds the constraint, or the table that is referenced by the constraint?" So far, the default of 'table' has been passed in all the examples, but we could pass either a value of 'reference' or 'all'. This means that we can pass the name of a dimension table to the DISABLE_CONSTRAINTS procedure when we actually want to disable constraints on a fact table. We may not know for sure which fact tables have foreign keys pointing to a particular dimension table, only that we want to disable any constraints that reference it. P_BASIS allows us to do that.
SQL> BEGIN 2 trans_etl.disable_constraints( 3 p_table => 'products', 4 p_owner => 'sh_new', 5 p_basis => 'reference' 6 ); 7 END; 8 / Constraint SALES_PRODUCT_FK disabled on SH_NEW.SALES Constraint COSTS_PRODUCT_FK disabled on SH_NEW.COSTS 2 constraint disablement processes related to SH_NEW.PRODUCTS executed PL/SQL procedure successfully completed. Elapsed: 00:00:00.08 SQL> SQL> BEGIN 2 trans_etl.enable_constraints( 3 p_table => 'products', 4 p_owner => 'sh_new', 5 p_basis => 'reference', 6 p_concurrent => 'yes' 7 ); 8 END; 9 / Oracle scheduler job CONSTRAINT_MAINT131 created Oracle scheduler job CONSTRAINT_MAINT131 enabled Constraint SALES_PRODUCT_FK enabled on SH_NEW.SALES Oracle scheduler job CONSTRAINT_MAINT132 created Oracle scheduler job CONSTRAINT_MAINT132 enabled Constraint COSTS_PRODUCT_FK enabled on SH_NEW.COSTS 2 constraint enablement processes related to SH_NEW.PRODUCTS submitted to the Oracle scheduler PL/SQL procedure successfully completed. Elapsed: 00:00:05.71 SQL>
More to come soon, including object cloning (of which we've seen a little bit already), stats maintenance, and eventually, complex scenarios such as partition exchanges, table replacements, and configured Mappings and Dimensions which are easily called from ETL tools such as OWB.