Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 18 Dec 2004 @ 07:18:11 GMT


     
  <Prev Next>  
<<First
<Prev
Next>
Last>>
 


Subj:   Useful Data Dictionary Queries - Generate DDL to Add & Drop Soft RI Constraints
 
From:   Terry Stover

I wrote these queries generate the DDL to add and drop all of the potentially useful soft RI constraints for testing on V2R5. Soft RI is used by the R5 optimizer for join elimination, I haven't done any benchmarking so I don't know how effective it is in practice. The soft RI constraints can be added and dropped without creating new versions of the tables. This version query is limited to 3 column PI's, but the structure is easily extended. This query returns the DDL for all possible soft RI constraints, not just reasonable or even rational ones. The foreign key and primary key tables, and referenced columns are included in the output to support analysis by the dba.

     /*  ############  CREATE SOFT RI DDL  ############

     This query generates the DDL to create or drop soft RI contraints using the
     data dictionary views.

        Returns a row for every table having the same columns that form the
     unique primary index on another table.

        The matching is based strictly on column names, but does not depend
     on column order within the tables.

        Change the references to databasename = 'dw_data'  to match your
     database.

     WARNING!!:  Since the matching is done strictly on column names, more rows
     may be included than are appropriate. The results should be reviewed in
     detail before implementing.  I included the table and column names in
     separate columns to enable easy sorting and filtering.

     Add / Drop soft RI constraint DDL uses syntax
     ALTER TABLE fiscal_month ADD FOREIGN KEY (fisc_yr_key) REFERENCES WITH NO
     CHECK OPTION fiscal_year(fisc_yr_key);
     ALTER TABLE fiscal_month DROP FOREIGN KEY (fisc_yr_key) REFERENCES
     fiscal_year(fisc_yr_key);

     Author:  Terry Stover, Levi Strauss & Co.  2004-06-14.
              Comments & feedback are welcome: tstover@levi.com or terry.stover@comcast.net.


     --OTHER COMMENTS
     It supports a maximum of 3 column PI's.  I have a 5 column PI version since
     that's pretty much the most we have other than a few oddball tables.

     There's enough of a pattern that it could easily be extended to
     longer PI's.

     I haven't tried tweaking this for performance.  The explains show locking
     for access on all the tables, so it shouldn't interfere with regular
     operations.

        It seems to choke a bit when I run it for all PI's up to 5 columns
     in one pass.

        5 column version runs much faster when I break the inner query into
     2 batches: PI lengths 1-3, then 4-5.

     I kept losing formatting (indenting) when I move the file across systems, so
     I gave up.  It's just one flat sql statement.

     ############################################################## */

     select databasename, fk_table, pk_table,  nbr_cols, col1, col2, col3, col4, col5,
     'ALTER TABLE ' || trim(databasename) || '.'|| trim (FK_table) || ' ADD
     FOREIGN KEY (' || col_list || ')  REFERENCES WITH NO CHECK OPTION  '
     || trim(databasename) || '.'|| trim(PK_table) || '(' || col_list || ');' as
     Create_SoftRI_DDL,
     'ALTER TABLE ' || trim(databasename) || '.'||trim (FK_table) || ' DROP
     FOREIGN KEY (' || col_list || ')  REFERENCES '
     || trim(PK_table) || '(' || col_list || ');' as Drop_SoftRI_DDL
     from
     (
     -- SINGLE COLUMN PIs
     select pi.databasename, pi.tablename PK_table, c1.tablename FK_table, pi.nbr_cols
     , pi.col1, pi.col2, pi.col3, pi.col4, pi.col5,
     cast(col1 as varchar(200)) as col_list  --first instance must be wide enough
     to support longest possible column list
     from (
     select  di.databasename, di.tablename, di.indexname, max(di.columnposition) nbr_cols,
     max (case when di.columnposition = 1 then di.columnname else '' end)  col1,
     max (case when di.columnposition = 2 then di.columnname else '' end)  col2,
     max (case when di.columnposition = 3 then di.columnname else '' end)  col3,
     max (case when di.columnposition = 4 then di.columnname else '' end)  col4,
     max (case when di.columnposition = 5 then di.columnname else '' end)  col5
      from dbc.indices di, dbc.tables dt where di.databasename = dt.databasename
     and di.tablename = dt.tablename and dt.tablekind = 'T'
      and di.indextype = 'P' and di.databasename = 'dw_data'  and di.uniqueflag = 'Y'
     group by 1,2,3
     having max(di.columnposition) =1) PI
     inner join dbc.columns c1 on pi.databasename = c1.databasename and
     pi.tablename <> c1.tablename and pi.col1 = c1.columnname
     inner join dbc.tables t1 on c1.databasename = t1.databasename and
     c1.tablename = t1.tablename and t1.tablekind = 'T'
     where pi.nbr_cols = 1


     UNION ALL -- 2 COLUMN PIs

     select pi.databasename, pi.tablename PK_table, c1.tablename FK_table, pi.nbr_cols,
     pi.col1, pi.col2, pi.col3, pi.col4, pi.col5, col1||', '|| col2 as col_list
     from (
     select  di.databasename, di.tablename, di.indexname, max(di.columnposition) nbr_cols,
     max (case when di.columnposition = 1 then di.columnname else '' end)  col1,
     max (case when di.columnposition = 2 then di.columnname else '' end)  col2,
     max (case when di.columnposition = 3 then di.columnname else '' end)  col3,
     max (case when di.columnposition = 4 then di.columnname else '' end)  col4,
     max (case when di.columnposition = 5 then di.columnname else '' end)  col5
     from dbc.indices di, dbc.tables dt
     where di.databasename = dt.databasename and di.tablename = dt.tablename and dt.tablekind = 'T'
     and di.indextype = 'P' and di.databasename = 'dw_data'  and di.uniqueflag = 'Y'
     group by 1,2,3
     having max(di.columnposition) =2) PI
     inner join dbc.columns c1 on pi.databasename = c1.databasename and
     pi.tablename <> c1.tablename and pi.col1 = c1.columnname
     inner join dbc.columns c2 on pi.databasename = c2.databasename and
     pi.tablename <> c2.tablename and pi.col2 = c2.columnname
     inner join dbc.tables t1 on c1.databasename = t1.databasename and
     c1.tablename = t1.tablename and t1.tablekind = 'T'
     where c1.tablename = c2.tablename and pi.nbr_cols = 2

     UNION ALL --3 COLUMN PIs

     select pi.databasename, pi.tablename PK_table, c1.tablename FK_table, pi.nbr_cols,
     pi.col1, pi.col2, pi.col3, pi.col4, pi.col5,
      col1||', '|| col2 || ', ' || col3 as col_list
     from (
     select  di.databasename, di.tablename, di.indexname, max(di.columnposition) nbr_cols,
     max (case when di.columnposition = 1 then di.columnname else '' end)  col1,
     max (case when di.columnposition = 2 then di.columnname else '' end)  col2,
     max (case when di.columnposition = 3 then di.columnname else '' end)  col3,
     max (case when di.columnposition = 4 then di.columnname else '' end)  col4,
     max (case when di.columnposition = 5 then di.columnname else '' end)  col5
     from dbc.indices di, dbc.tables dt where di.databasename = dt.databasename
     and di.tablename = dt.tablename and dt.tablekind = 'T'
     and di.indextype = 'P' and di.databasename = 'dw_data'  and di.uniqueflag = 'Y'
     group by 1,2,3
     having max(di.columnposition) =3) PI
     inner join dbc.columns c1 on pi.databasename = c1.databasename and
     pi.tablename <> c1.tablename and pi.col1 = c1.columnname
     inner join dbc.columns c2 on pi.databasename = c2.databasename and
     pi.tablename <> c2.tablename and pi.col2 = c2.columnname
     inner join dbc.columns c3 on pi.databasename = c3.databasename and
     pi.tablename <> c3.tablename and pi.col3 = c3.columnname
     inner join dbc.tables t1 on c1.databasename = t1.databasename and
     c1.tablename = t1.tablename and t1.tablekind = 'T'
     where c1.tablename = c2.tablename and c2.tablename = c3.tablename and
     pi.nbr_cols = 3) xtab
     order by 1,2,3
     


     
  <Prev Next>  
<<First
<Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016