 |
 |
Archives of the TeradataForum
Message Posted: Fri, 18 Dec 2004 @ 07:18:11 GMT
| 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: [email protected] or [email protected].
--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
| |
