set pagesize 1600;
set linesize 256;
column owner format a15;
column table_name format a20;
column column_name format a18;
column comments format a100;

select OBJECT_NAME,OBJECT_TYPE,CREATED from all_objects 
	where OBJECT_TYPE='TABLE' and owner='LUMINOSITY';

OBJECT_NAME                    OBJECT_TYPE        CREATED
------------------------------ ------------------ ---------
DELIVERED_LUMS                 TABLE              02-AUG-04
EXPOSED_FRACTIONS              TABLE              02-AUG-04
EXPOSED_LUMS                   TABLE              02-AUG-04
EXPOSURE_GROUPS                TABLE              02-AUG-04
LBNS                           TABLE              02-AUG-04
LBN_FACTORS                    TABLE              02-AUG-04
LBN_QUALITIES                  TABLE              02-AUG-04
LBN_QUALITY_PARAMS             TABLE              02-AUG-04
LBN_STREAMS                    TABLE              02-AUG-04
LBN_TRIGGERS                   TABLE              02-AUG-04
LM_RATES                       TABLE              02-AUG-04
LM_TYPES                       TABLE              02-AUG-04
PRD_FILE_CHKS                  TABLE              02-AUG-04
QUAL_FLAGS                     TABLE              02-AUG-04
QUAL_GROUPS                    TABLE              02-AUG-04
QUAL_USERS                     TABLE              02-AUG-04
RANGED_FACTORS                 TABLE              02-AUG-04
STATUS_EXPLAIN_LBNS            TABLE              02-AUG-04
STATUS_EXPLAIN_LFS             TABLE              02-AUG-04
STATUS_EXPLAIN_LSS             TABLE              02-AUG-04
STATUS_EXPLAIN_LTS             TABLE              02-AUG-04
STATUS_EXPLAIN_PFCS            TABLE              02-AUG-04
STATUS_EXPLAIN_RFS             TABLE              02-AUG-04
STATUS_EXPLAIN_TKS             TABLE              02-AUG-04
STATUS_EXPLAIN_TRS             TABLE              02-AUG-04
STATUS_TO_LBNS                 TABLE              02-AUG-04
STATUS_TO_LFS                  TABLE              02-AUG-04
STATUS_TO_LSS                  TABLE              02-AUG-04
STATUS_TO_LTS                  TABLE              02-AUG-04
STATUS_TO_PFCS                 TABLE              02-AUG-04
STATUS_TO_RFS                  TABLE              02-AUG-04
STATUS_TO_TKS                  TABLE              02-AUG-04
STATUS_TO_TRS                  TABLE              02-AUG-04
TICKS                          TABLE              02-AUG-04
TRANSITION_RECORDS             TABLE              02-AUG-04

36 rows selected.

-----------------------------------------------------------------------------------
-- Here's the tables initially filled by sqlldr files 
--   (fixed number of records - will probably never increase)
-- Initial size is 3 years (use the values below)
-- Max size is after 8 years (multiply values below by 3)):

select count(*) from TICKS                  ;
       160
select count(*) from EXPOSURE_GROUPS        ;
         8
select count(*) from LM_TYPES               ;
         8
select count(*) from QUAL_FLAGS             ;
         5
select count(*) from QUAL_GROUPS            ;
         8
select count(*) from QUAL_USERS             ;
         7

-----------------------------------------------------------------------------------
-- select tables in the order in Heidi's spreadsheet (this is count of 6 weeks of data)
-- Initial size is 3 years of data so multiply by (52 weeks / 1 year)(3 years/6 weeks)
-- Max size is 8 years of data so multiply by (52 weesk / 1 year)(8 years/6 weeks)

select count(*) from LBNS                   ;
    237824
select count(*) from DELIVERED_LUMS         ;
   2783214
select count(*) from EXPOSED_FRACTIONS      ;
  19757417
select count(*) from EXPOSED_LUMS           ;
  16766251
select count(*) from LBN_TRIGGERS           ;
   9220813
select count(*) from LM_RATES               ;
   8349520
select count(*) from LBN_STREAMS            ;
     53921

-----------------------------------------------------------------------------------
select count(*) from TRANSITION_RECORDS     ;
         0
-- TRANSITION_RECORDS estimate about 1000 per year x 8 years = 80000 max

-----------------------------------------------------------------------------------
-- current size of quality related tables (this is count of 3 years of data = 'initial size')
-- Multiple by 3 and then multiply by 8/3:

select count(*) from LBN_QUALITIES          ;
    227901
select count(*) from LBN_QUALITY_PARAMS     ;
     72979
-----------------------------------------------------------------------------------
-- Status Explain tables:
-- Initial size is about 40 for each table
-- Max Size is about 100 for each table

SQL> select count(*) from STATUS_EXPLAIN_LBNS    ;
        29
SQL> select count(*) from STATUS_EXPLAIN_LFS     ;
         0
SQL> select count(*) from STATUS_EXPLAIN_LSS     ;
         4
SQL> select count(*) from STATUS_EXPLAIN_LTS     ;
        37
SQL> select count(*) from STATUS_EXPLAIN_PFCS    ;
         0
SQL> select count(*) from STATUS_EXPLAIN_RFS     ;
         0
SQL> select count(*) from STATUS_EXPLAIN_TKS     ;
        28
SQL> select count(*) from STATUS_EXPLAIN_TRS     ;
         0

-----------------------------------------------------------------------------------
-- The following tables have not been populated yet, so we have to guess.
-- Assume 10% of all tables with a STATUS_TO_* table have a record in this table


select count(*) from STATUS_TO_LBNS         ;
	Initial (3 years) = 237824 x 0.10 (52/6) x 3
	Max     (8 years) = 237824 x 0.10 (52/6) x 8
select count(*) from STATUS_TO_LFS          ;
select count(*) from STATUS_TO_LSS          ;
select count(*) from STATUS_TO_LTS          ;
select count(*) from STATUS_TO_PFCS         ;
select count(*) from STATUS_TO_RFS          ;
select count(*) from STATUS_TO_TKS          ;
select count(*) from STATUS_TO_TRS          ;
