--2-Jun-2006 
-- Miscellaneous queries
--
-- 1. trying to distinguish between int and float types by TableExtractor
--    Use the LBN table as an example because LBN is integer and DURATION is float
--
-- 2. Look at the tables and data I have defined and 
--    the counts loaded by TableExtractor
--    Play with the LBN clause to get a good amount of data.
-- 
-- 3. truncate or delete the data, then reload (TableExtractor_lum.py)
--
-- 4. look how much data has been loaded
--
-- 5. Look at the most frequently configured triggers in LBN_TRIGGERS
--
-- 6. Look how many LBNs are in each data taking period
--
-- ================================================================================
-- ================================================================================
-- 1. TableExtractorDb_lum uses the user_tab_columns table

 desc user_tab_columns
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 COLUMN_NAME                               NOT NULL VARCHAR2(30)
 DATA_TYPE                                          VARCHAR2(106)
 DATA_TYPE_MOD                                      VARCHAR2(3)
 DATA_TYPE_OWNER                                    VARCHAR2(30)
 DATA_LENGTH                               NOT NULL NUMBER
 DATA_PRECISION                                     NUMBER
 DATA_SCALE                                         NUMBER
 NULLABLE                                           VARCHAR2(1)
 COLUMN_ID                                          NUMBER
 DEFAULT_LENGTH                                     NUMBER
 DATA_DEFAULT                                       LONG
 NUM_DISTINCT                                       NUMBER
 LOW_VALUE                                          RAW(32)
 HIGH_VALUE                                         RAW(32)
 DENSITY                                            NUMBER
 NUM_NULLS                                          NUMBER
 NUM_BUCKETS                                        NUMBER
 LAST_ANALYZED                                      DATE
 SAMPLE_SIZE                                        NUMBER
 CHARACTER_SET_NAME                                 VARCHAR2(44)
 CHAR_COL_DECL_LENGTH                               NUMBER
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 AVG_COL_LEN                                        NUMBER
 CHAR_LENGTH                                        NUMBER
 CHAR_USED                                          VARCHAR2(1)
 V80_FMT_IMAGE                                      VARCHAR2(3)
 DATA_UPGRADED                                      VARCHAR2(3)
 HISTOGRAM                                          VARCHAR2(15)

spool 060602_MiscQuery.out;
set echo on;
select unique TABLE_NAME         from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  COLUMN_NAME        from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  DATA_TYPE          from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  DATA_TYPE_MOD      from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  DATA_TYPE_OWNER    from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  DATA_LENGTH        from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  DATA_PRECISION     from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  DATA_SCALE         from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  NULLABLE           from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  COLUMN_ID          from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  DEFAULT_LENGTH     from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  DATA_DEFAULT       from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  NUM_DISTINCT       from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  LOW_VALUE          from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  HIGH_VALUE         from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  DENSITY            from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  NUM_NULLS          from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  NUM_BUCKETS        from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  LAST_ANALYZED      from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  SAMPLE_SIZE        from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  CHARACTER_SET_NAME from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  CHAR_COL_DECL_LENGTH  from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  GLOBAL_STATS          from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  USER_STATS            from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  AVG_COL_LEN           from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  CHAR_LENGTH           from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  CHAR_USED             from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  V80_FMT_IMAGE         from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  DATA_UPGRADED         from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');
select unique  HISTOGRAM             from user_tab_columns where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');

-- Results from looking at table user_tab_columns,
-- 	comparing integers and floats so I can distinguish 
-- 	between them in TableExtractor
-- 
-- both integers and floats have 
-- 	DATA_TYPE = 'NUMBER',
-- 	DATA_LENGTH = 22
-- 	NULLABLE,
-- 	NUM_NULLS = 0
-- 	NUM_BUCKETS = 1
-- 	SAMPLE_SIZE = 5574
-- 	GLOBAL_STATS = YES
-- 	USER_STATS = NO
-- 	CHAR_LENGTH = 0
-- 
-- empty columns = DATA_TYPE_MOD, DATA_TYPE_OWNER, 
--                 DEFAULT_LENGTH, DATA_DEFAULT (LONG!), 
-- 		   CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH, CHAR_USED
-- 
-- different for integers and floats
-- 	DATA_PRECISION
-- 	DATA_SCALE
-- 	NUM_DISTINCT  (70 and 2492278)
-- 	LOW_VALUE  (C4020E133A    and 3E6466  )
-- 	HIGH_VALUE (C4054A2302 and C20A35)
-- 	DENSITY (4.0124E-07 and .014285714)
-- 	AVG_COL_LEN (6 and 3)

select column_name,data_precision,data_scale from user_tab_columns 
	where table_name='LBNS' and COLUMN_NAME in ('LBN','DURATION');

-- COLUMN_NAME                    DATA_PRECISION DATA_SCALE
-- ------------------------------ -------------- ----------
-- LBN                                        38          0
-- DURATION
-- 
-- DATA_PRECISION is up to 38 decimal digits including decimal before+after dec 
-- DATA_SCALE is the number of places after the decimal (0 for integer)

-- CONCLUSION: use DATA_SCALE (places after decimal to distinguish int and float)

-- ================================================================================
-- ================================================================================
-- 2. Look at the tables and data I have defined and 
--    the counts loaded by TableExtractor
--    Play with the LBN clause to get a good amount of data.
-- ================================================================================
-- ================================================================================

sqlplus gallas@d0ofdev1


 select table_name from user_tables;

 select count(*) from ticks;
 select count(*) from exposure_groups;
 select count(*) from transition_records;

 select count(*) from EXPOSED_LUMS;
 select count(*) from LBNS;
 select count(*) from DELIVERED_LUMS;
 select count(*) from LBN_TRIGGERS;

-- ================================================================================
-- ================================================================================
-----------------------------------------------------------------------------------
-- for my tests on d0ofdev1 in gallas account, choose the following lbns,ranges
-----------------------------------------------------------------------------------
-- LBN 3829969 has a floating point duration of 59.326611
-- 1139444 to 1139717: lbn range of first run of period A
-- 3535951 to 3737346: first lbn of last run of period A to last lbn  of first run of period B
-- 3756354 to 3759371: first lbn of last run of period	B to last lbn  of first run of period C
-- 3921293 to 3921439: first lbn of last run of period C to last lbn  of first run of period D
-- 4317745 to 4318134: first lbn of last run of period D to last lbn  of first run of period E
-- 4536116 to 4536500: first lbn of last run of period E to OVER 4536192
--
-- This above set contains 156315 LBNs -- too many -- see how many in each range
--
select count(*) from lbns where (LBN>=1139444 AND LBN<=1139717);
       274
select count(*) from lbns where (LBN>=3535951 AND LBN<=3737346);
    152105
select count(*) from lbns where (LBN>=3756354 AND LBN<=3759371);
      3018
select count(*) from lbns where (LBN>=3921293 AND LBN<=3921439);
       144
select count(*) from lbns where (LBN>=4317745 AND LBN<=4318134);
       388
select count(*) from lbns where (LBN>=4536116 AND LBN<=4536500);
       385

-----------------------------------------------------------------------------------
-- try splitting the 2nd and 3rd regions into 2 smaller, still covering all periods
--

-- LBN 3829969 has a floating point duration of 59.326611
-- 1139444 to 1139717: lbn range of first run of period A
--
-- 3535900 to 3536400: first lbn of last run of period A to last lbn  of first run of period B
-- 3737000 to 3737346: first lbn of last run of period A to last lbn  of first run of period B
--
-- 3756354 to 3756854: first lbn of last run of period	B to last lbn  of first run of period C
-- 3759000 to 3759371: first lbn of last run of period	B to last lbn  of first run of period C
--
-- 3921293 to 3921439: first lbn of last run of period C to last lbn  of first run of period D
--
-- 4317745 to 4318134: first lbn of last run of period D to last lbn  of first run of period E
--
-- 4536116 to 4536500: first lbn of last run of period E to OVER 4536192
--
select count(*) from lbns  
WHERE LBN=3829969
 OR (LBN>=1139444 AND LBN<=1139717)
 OR (LBN>=3535900 AND LBN<=3536400)
 OR (LBN>=3737000 AND LBN<=3737346)
 OR (LBN>=3756354 AND LBN<=3756854)
 OR (LBN>=3759000 AND LBN<=3759371)
 OR (LBN>=3921293 AND LBN<=3921793) 
 OR (LBN>=3921000 AND LBN<=3921439)
 OR (LBN>=4317745 AND LBN<=4318134) 
 OR (LBN>=4536116 AND LBN<=4536500);
      3560

-- still too many entries:
-rw-rw-r--   1 gallas   e740     4002912 Jun  5 14:44 DELIVERED_LUMS.list
131424
-rw-rw-r--   1 gallas   e740     23259955 Jun  5 14:51 EXPOSED_LUMS.list
713577
-rw-rw-r--   1 gallas   e740      805793 Jun  5 14:43 LBNS.list
3560
-rw-rw-r--   1 gallas   e740     79371371 Jun  5 15:07 LBN_TRIGGERS.list
713039
-----------------------------------------------------------------------------------

select count(*) from lbns  
WHERE LBN=3829969
 OR (LBN>=1139400 AND LBN<=1139500)
 OR (LBN>=3535900 AND LBN<=3536000)
 OR (LBN>=3737300 AND LBN<=3737400)
 OR (LBN>=3756300 AND LBN<=3756400)
 OR (LBN>=3759000 AND LBN<=3759100)
 OR (LBN>=3921200 AND LBN<=3921300) 
 OR (LBN>=3921000 AND LBN<=3921100)
 OR (LBN>=4317700 AND LBN<=4317800) 
 OR (LBN>=4536400 AND LBN<=4536500);

160	TICKS
8	EXPOSURE_GROUPS
910	LBNS
206998	EXPOSED_LUMS
33633	DELIVERED_LUMS
48178	LBN_TRIGGERS

-rw-rw-r--   1 gallas   e740     1059583 Jun  5 15:19 DELIVERED_LUMS.list
-rw-rw-r--   1 gallas   e740     6815405 Jun  5 15:21 EXPOSED_LUMS.list
-rw-rw-r--   1 gallas   e740          70 Jun  2 17:06 EXPOSURE_GROUPS.list
-rw-rw-r--   1 gallas   e740      206165 Jun  5 15:19 LBNS.list
-rw-rw-r--   1 gallas   e740     19904489 Jun  5 15:25 LBN_TRIGGERS.list
-rw-rw-r--   1 gallas   e740         576 Jun  2 17:06 TICKS.list
-----------------------------------------------------------------------------------
-- run with percentage prescales 213821	(4467848-4467974)
-- lbn with float duration 3829969
-- lbn with trigger with a ^ 3756333 (can't use this delimeter)
-- and try 10 lbns around the start and end of each period

select count(*) from lbns  
WHERE LBN=3829969
 OR   LBN=3756333
 OR (LBN>=4467840 AND LBN<=4467850)
 OR (LBN>=1139440 AND LBN<=1139450)
 OR (LBN>=3536160 AND LBN<=3536170)
 OR (LBN>=3737320 AND LBN<=3737330)
 OR (LBN>=3756470 AND LBN<=3756480)
 OR (LBN>=3759350 AND LBN<=3759360)
 OR (LBN>=3921400 AND LBN<=3921410)
 OR (LBN>=3921400 AND LBN<=3921410)
 OR (LBN>=4317920 AND LBN<=4317930)
 OR (LBN>=4317945 AND LBN<=4317955)
 OR (LBN>=4536190 AND LBN<=4536200);

160	TICKS
8	EXPOSURE_GROUPS
108	LBNS
23125	EXPOSED_LUMS
3959	DELIVERED_LUMS
19767	LBN_TRIGGERS
-- I think this is a reasonable amount of data to work with.

-- ================================================================================
-- ================================================================================
-- 3. truncate or delete the data, then reload (TableExtractor_lum.py)
-- ================================================================================
-- ================================================================================
exit;
-- not normally deleted
-- delete from TICKS;
-- delete from  EXPOSURE_GROUPS;
-- delete from  TRANSITION_RECORDS;
-- 
delete from  EXPOSED_LUMS;
delete from  DELIVERED_LUMS;
delete from  LBN_TRIGGERS;
delete from  LBNS;

truncate TABLE EXPOSED_LUMS;
truncate TABLE DELIVERED_LUMS;
truncate TABLE LBN_TRIGGERS;
truncate TABLE LBNS;
-- ================================================================================
--
-- 4. look how much data has been loaded
-- ================================================================================
 select count(*) from DELIVERED_LUMS;
91788087

 select count(*) from DELIVERED_LUMS where DEL_LUM_NIM_NEW IS NULL;
  40049536

 select count(*) from EXPOSED_LUMS;
 350847806

 select count(*) from EXPOSED_LUMS where EXP_LUM_NIM_NEW IS NULL;
 180630240

 select count(*) from LBN_TRIGGERS;

 select count(*) from EXPOSED_LUMS;
 select count(*) from LBNS;
 select count(*) from DELIVERED_LUMS;
 select count(*) from LBN_TRIGGERS;

-- ================================================================================
--
-- 5. Look at the most frequently configured triggers in LBN_TRIGGERS
--
-- ================================================================================
set heading off;
column L3_BIT_NAME format a20;
select l3_bit_name,count(*) from lbn_triggers
   group by l3_bit_name
   order by count(*);


-------- Original Message --------
Subject: 	D0 trivia: 50 most frequently run triggers in Run 2A
Date: 	Wed, 26 Jul 2006 11:15:55 -0500
From: 	Elizabeth Gallas <gallas@fnal.gov>
To: 	D0 Luminosity Group <d0luminosity@fnal.gov>

There were 5249 distinct trigger names used in D0
as counted by those found in the Luminosity database
(starting with data taken in April 2002).

The following 50 triggers were the most frequently
configured by LBN (note: this does not mean they saw the
most luminosity, they were just configured during
the most number of LBNs).

Most frequent physics trigger:  JT_45TT !!!
This trigger name was used in many test trigger lists and
every global trigger list from version 5 to version 14.

I would guess that the trigger that saw the most luminosity
over Run 2A was the second most frequent physics trigger
in this list: JT_125TT (also used in global versions 5 to 14).

Trigger Name         Number of LBNs

E4_SHT15_TK13            503822
E4_SH30                  503822
E6_2L15_SH15             503822
E4_2L15_SH15             503822
E2_SHT15_TK13            503835
E3_2L15_SH15             503835
E1_SH30                  503835
E2_2L20                  503835
E2_SH30                  503835
E1_2L15_SH15             503835

E2_2L15_SH15             503835
E1_SHT15_TK13            503835
E1_2L20                  503835
E3_2L20                  503835
E3_SH30                  503835
EM15                     503835
E5_T7SHT8_2TK5           503853
E5_SHT15_TK13            503853
E5_2L15_SH15             503853
ZBIAS_L3MP               590247

ZBIAS_L3P0               592164
mu2ptxatxx               620456
2MT1_2TRK_GAPN           640638
2MT1_2TRK_GAPS           640714
2MT1_2TRK_GAPSN          654407
JT_45TT_GAPSN            703813
JT_45TT_GAPS             703889
JT_45TT_GAPN             704008
EM12                     704022
EM9                      704022

EM5                      704077
JT_15TT_GapSN            746567
JT_15TT_GapS             747086
JT_15TT_GapN             747500
JT_15TT                  759206
zero_bias_GapS_NCU       784513
zero_bias_GapSN_NCU      784513
zero_bias_GapN_NCU       784964
JT_8TT                   789224
Cal_unsuppressed         790595

zero_bias_NCU            802002
L1CTT_DOWNLOAD           839305
L1MU_DOWNLOAD            846124
JT_25TT_NG               940997
JT_95TT                 1002957
JT_65TT                 1002991
JT_125TT                1005429
JT_45TT                 1008946
ZBIAS_L2P0_NOLUM        1043220
zero_bias               1347772

================================================================================
--
-- 6. Look how many LBNs are in each data taking period
--
-- ================================================================================

SET ECHO ON;

-- 'Run 2A:A'
select count(*) from LBNS where LBN >= 1129853 AND LBN <= 3736259;
   1650376

-- 'Run 2A:B'
select count(*) from LBNS where LBN >= 3736260 AND LBN <= 3757957 ;
     21698

-- 'Run 2A:C'
select count(*) from LBNS where LBN >= 3757958 AND LBN <= 3921401 ;
    142894

-- 'Run 2A:D'
select count(*) from LBNS where LBN >= 3921405 AND LBN <= 4317937 ;
    356251

-- 'Run 2A:E'
select count(*) from LBNS where LBN >= 4317939 AND LBN <= 4715213 ;
    310665

-- 'Run 2B'
select count(*) from LBNS where LBN >= 4715214 ;
     18307

---------------------------------------
-- GetRunBits development -- get bit numbers, names for a run or current runs...

SET LINESIZE 256;
SET PAGESIZE 600;
COLUMN PRESCALE FORMAT A10;
COLUMN RUN_NUMBER FORMAT 999999;
COLUMN EG_NUM     FORMAT A4;
COLUMN BIT_NUM    FORMAT A4;
COLUMN BIT_NAME   FORMAT A40;
DESC  RUNS.RUN_TRIGS;

SELECT RT3.RUN_NUMBER,RT3.BIT_NUM
  FROM RUNS.RUN_TRIGS RT3
  WHERE RT3.RUN_NUMBER=223751 AND RT3.TRG_TYPE='L3bit'
  ORDER BY RT3.RUN_NUMBER,TO_NUMBER(RT3.BIT_NUM);

558 rows selected.

SELECT RT3.RUN_NUMBER,RT1.EG_NUM,RT1.PRESCALE,
    RT3.BIT_NUM,RT3.BIT_NAME,
        RT2.BIT_NUM,RT2.BIT_NAME,
        RT1.BIT_NUM,RT1.BIT_NAME
FROM RUNS.RUN_TRIGS RT1, RUNS.RUN_TRIGS RT2, RUNS.RUN_TRIGS RT3
WHERE RT1.RUN_NUMBER=223751 AND RT1.TRG_TYPE='L1bit'
  AND RT3.RUN_NUMBER=223751 AND RT3.DEPEND_BIT=RT2.BIT_NUM AND RT3.TRG_TYPE='L3bit'
  AND   RT2.RUN_NUMBER=223751 AND RT2.DEPEND_BIT=RT1.BIT_NUM AND RT2.TRG_TYPE='L2bit'
ORDER BY RT3.RUN_NUMBER,TO_NUMBER(RT3.BIT_NUM);


-- from d0oflumd 
DESC  RUN_TRIGS;
SELECT RT3.RUN_NUMBER,RT1.EG_NUM,RT1.PRESCALE,
    RT3.BIT_NUM,RT3.BIT_NAME,
        RT2.BIT_NUM,RT2.BIT_NAME,
        RT1.BIT_NUM,RT1.BIT_NAME
FROM RUN_TRIGS RT1, RUN_TRIGS RT2, RUN_TRIGS RT3
WHERE RT1.RUN_NUMBER=223751 AND RT1.TRG_TYPE='L1bit'
  AND RT3.RUN_NUMBER=223751 AND RT3.DEPEND_BIT=RT2.BIT_NUM AND RT3.TRG_TYPE='L3bit'
  AND   RT2.RUN_NUMBER=223751 AND RT2.DEPEND_BIT=RT1.BIT_NUM AND RT2.TRG_TYPE='L2bit'
ORDER BY RT3.RUN_NUMBER,TO_NUMBER(RT3.BIT_NUM);

================================================================================
--
-- 7. Try to help Luiz find row counts
--
-- ================================================================================

select a.*,
       b.*,
       c.* 
from lbn a,
     lbn_triggers b,
     lbn_streams c 
where a.LBN = b.LBN 
and b.ls_id = c.ls_id


SELECT last_name, salary,
   COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING
      AND 150 FOLLOWING) AS mov_count FROM employees;

-- count from LT for a single LBN
select count(*) from lbn_triggers where LBN=3829781;

-- count from LT for a range of LBNs
select count(*),LBN from lbn_triggers 
	where LBN>=3829781 and lbn<=3829881 
	group by LBN;

-- count of PFC rows for a single LBN
select count(*) from prd_file_chks pfc, lbn_streams ls
	where pfc.ls_id=ls.ls_id and ls.lbn=3829781;

-- count of PFC rows for a range of LBNs
select count(*),ls.ls_id from prd_file_chks pfc, lbn_streams ls
	where pfc.ls_id=ls.ls_id
	  and ls.lbn>=3829781 and ls.lbn<=3829881
	group by ls.ls_id;

select count(*),ls.ls_id,ls.lbn from prd_file_chks pfc, lbn_streams ls
	where pfc.ls_id=ls.ls_id
	  and ls.lbn>=3829781 and ls.lbn<=3829881
	group by ls.ls_id,ls.lbn;
