80 likes | 179 Views
General recommendations. Focus on representing the modularity of the detector when creating the tables Implement constrains on table columns Use Primary keys PK on columns which contain the version number Use Foreign keys FK on the columns which reference the primary keys.
E N D
General recommendations • Focus on representing the modularity of the detector when creating the tables • Implement constrains on table columns • Use Primary keys PK on columns which contain the version number • Use Foreign keys FK on the columns which reference the primary keys. • Since no deletes and no updates will be run- • Use IOTs (Index Organized Tables) (CREATE TABLE… …ORGANIZATION INDEX…) to assure for future performance
Once the schema is ready… • When populating the tables (e.g. from configuration files) you might use the /*+APPEND/ hint (example: insert /*+append / into mytable values (1, 255);) • You can let the version column increment itself by creating a sequence and a trigger • create or replace sequence myseq increment by 1 start with 1 nomaxvalue nocycle; • create or replace trigger mytrigger before insert on mytable for each row begin select myseq.nextval into :new.version from dual;end;/)
Side C Side A y x z SPD
Half-stave Sector Half-stave SPD
Hierarchical organization of DCS FERO configuration data A view created from the individual detector versions Actual configuration modes (Fixed number of rows, only updates) ALIVersion TypeOfRun PK Table Name SPDMode SPDVersion PK SPDVersion PK Column Name TypeOfRun PK SPDversion FK SDDVersion PK SDDVersion PK SPD Data SSDVersion PK SSDVersion PK SDD Data Oracle Datatype number(4,0) number(7,0) … SSD Data ppRunID xxxxx … … CosmicsRunID xxxxx … PbPbRunID xxxxx TRDVersion PK TRDVersion PK … xxxxx TRD Data … xxxxx … xxxxx … xxxxx SPDRemark SPDversion FK Remark number(7,0) varchar2(1000) xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx
Hierarchical organization of DCS SPD FERO configuration data version 2 1 2 20 120 SIDAVersion SIDVersion PK SECA0Version SECA1Version SEC0Ver FK Halfstave 0 SECA2Version Halfstave 0 SEC1Ver FK Halfstave 1 … HASA90Version Halfstave 0 Halfstave 1 SEC2Ver FK Halfstave 2 SPDVersion … Halfstave 0 Halfstave 1 HASVersion PK Halfstave 2 … … SPDVersion PK SECA9Version Halfstave 0 Halfstave 1 Halfstave 2 … … … MCMVer FK SIDAVer FK SECVersion PK Halfstave 1 Halfstave 2 … … Halfstave 5 SEC9Ver FK DACVer FK SIDCVer FK HAS0Ver FK Halfstave 2 … … Halfstave 5 MASVer FK SIDCVersion HAS1Ver FK … … Halfstave 5 SIDVersion PK … … Halfstave 5 SEC0Ver FK … Halfstave 5 SEC1Ver FK HAS5Ver FK SEC2Ver FK … … SEC9Ver FK
120 1 HASA90Version MCMVersion HASVersion PK MCMVersion Number(9) PK MCMVer FK ACO Blob DACVersion DACVersion Number(9) PK DACVer FK APIVect Char(6) DACVect CHAR(440) DPIVect Char(8) MBRVer FK GOLVect Char(4) Hierarchical organization of DCS SPD FERO configuration data version 2 continued 1 1 MBRVersion MBRVersion Number(9) PK Version 1: 508 Tables 507 Primary Keys 3862 Foreign Keys Version 2: 147 Tables 146 Primary Keys 502 Foreign Keys MBR Varchar(1640) Redesign