rem =========================================================================
rem 
rem                     fk2pk.sql
rem 
rem     Copyright (C) Oriole Software, 2001
rem 
rem     Downloaded from http://www.oriole.com
rem 
rem     This script for Oracle database administration is free software; you
rem     can redistribute it and/or modify it under the terms of the GNU General
rem     Public License as published by the Free Software Foundation; either
rem     version 2 of the License, or any later version.
rem 
rem     This script is distributed in the hope that it will be useful,
rem     but WITHOUT ANY WARRANTY; without even the implied warranty of
rem     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
rem     GNU General Public License for more details.
rem 
rem     You should have received a copy of the GNU General Public License
rem     along with this program; if not, write to the Free Software
rem     Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
rem 
rem =========================================================================
--
--  When trying to estimate future sizes of tables, one usually have
--  a fair idea of what can be expected for a handful of tables -
--  but only a handful of them. Assuming that one already has some
--  'significant' data in the database, this script tries to provide
--  useful additional information by computing two ratios (relative
--  number of rows and relative number of used blocks) between a table
--  and the tables which reference it through foreign keys. The idea is to
--  feed everything into your favorite spreadsheet and use the coefficients
--  provided by running this script to get something which can at least
--  pretend to be better than guesstimate when we have reliable sizing
--  information available for the main tables only. Needless to say,
--  we must insist on the fact that existing data must be significant and
--  that Oracle statistics must be up-to-date. It must be run by a DBA.
--
--  The script assumes the existence of function constraint_cols() which
--  must be created as follows :
-- 
-- create or replace function constraint_cols(p_con# in number)
-- return varchar2
-- is
--    v_col_list      varchar2(1000) := ''; -- size should be enough. Can be increased.
--    cursor c is     select c.name
--                    from sys.ccol$ cc,
--                         sys.col$ c
--                    where cc.con# = p_con#
--                      and cc.obj# = c.obj#
--                      and cc.col# = c.col#
--                    order by cc.pos#;
-- begin
--    for rec in c
--    loop
--      if (length(v_col_list) > 0)
--      then
--        v_col_list := v_col_list || ',';
--      end if;
--      v_col_list := v_col_list || rec.name;
--    end loop;
--  return v_col_list;
-- end;
-- /
--
set linesize 132
column PRIMARY_KEY format A50
column foreign_key like PRIMARY_KEY
break on PRIMARY_KEY
--
--  Uncomment if you want to create a text file to feed in a spreadsheet
--
-- set pause on
-- set feedback off
-- set recsep off
-- set underline off
-- set echo off
-- set pagesize 999
-- clear breaks
-- rem Following character is a tab
-- set colsep '	'
-- spool ratios.txt
set NULL 'N/A'
select pu.name || '.' || po.name || '(' || constraint_cols(pc.con#)
               || ')' PRIMARY_KEY,
       fu.name || '.' || fo.name || '(' || constraint_cols(fc.con#)
               || ')' FOREIGN_KEY,
       decode(pt.rowcnt, 0, 0, round(ft.rowcnt / pt.rowcnt)) row_ratio,
       decode(pt.blkcnt, 0, 0, round(ft.blkcnt / pt.blkcnt)) size_ratio
from sys.cdef$ fc,
     sys.cdef$ pc,
     sys.obj$ fo,
     sys.obj$ po,
     sys.user$ fu,
     sys.user$ pu,
     sys.tab$ ft,
     sys.tab$ pt
where pc.type# in (2, 3)
  and fc.type# = 4
  and fc.robj# = pc.obj#
  and fc.rcon# = pc.con#
  and fo.obj# = fc.obj#
  and ft.obj# = fo.obj#
  and fu.user# = fo.owner#
  and po.obj# = pc.obj#
  and pt.obj# = po.obj#
  and pu.user# = po.owner#
order by 1, 2
/
-- spool off
set NULL ''
clear breaks