Star schema in Oracle

Star schema in Oracle

SET SERVEROUT ON;

spool create_sample_star_schema.log;

/* Drop the Sample Tables */
DROP TABLE batting_fact;
DROP TABLE game_dim;
DROP SEQUENCE game_dim_seq;
DROP DIMENSION game_dim;
DROP TABLE team_dim;
DROP SEQUENCE team_dim_seq;
DROP DIMENSION team_dim;
DROP TABLE player_dim;
DROP SEQUENCE player_dim_seq;
DROP DIMENSION player_dim;
purge recyclebin;

/* Create the Sample Dimension Tables and Dimensions */

CREATE TABLE game_dim
(game_id NUMBER(30) NOT NULL,
game_no NUMBER(3) NOT NULL,
game_day DATE NOT NULL ,
game_month VARCHAR2(15) NOT NULL,
game_season NUMBER(4) NOT NULL);

CREATE SEQUENCE game_dim_seq;

CREATE DIMENSION game_dim
LEVEL game IS game_dim.game_no
LEVEL game_day IS game_dim.game_day
LEVEL game_month IS game_dim.game_month
LEVEL game_season IS game_dim.game_season
HIERARCHY game_rollup (
game CHILD OF
game_day CHILD OF
game_month CHILD OF
game_season);

DECLARE
lv_start_date DATE := TO_DATE ('01-APR-2007', 'DD-MON-YYYY');
lv_tmp_date DATE;
BEGIN
FOR j IN 1 .. 3
LOOP
lv_tmp_date := lv_start_date;

FOR i IN 1 .. 365
LOOP
INSERT INTO game_dim
VALUES (game_dim_seq.NEXTVAL, i, TRUNC (lv_tmp_date),
TRIM (TO_CHAR (lv_tmp_date, 'Month')),
TO_CHAR (lv_tmp_date, 'YYYY'));

COMMIT;
lv_tmp_date := TRUNC (lv_tmp_date) + 1;
END LOOP;

lv_start_date := TRUNC (lv_start_date) + 365;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END;
/

CREATE UNIQUE INDEX game_dim_pk ON game_dim(game_id);
ALTER TABLE game_dim ADD CONSTRAINT game_dim_pk PRIMARY KEY (game_id) RELY ENABLE NOVALIDATE;
CREATE BITMAP INDEX game_dim_game_no_bidx ON game_dim(game_no);
CREATE BITMAP INDEX game_dim_game_day_bidx ON game_dim(game_day);
CREATE BITMAP INDEX game_dim_game_month_bidx ON game_dim(game_month);
CREATE BITMAP INDEX game_dim_game_season_bidx ON game_dim(game_season);

CREATE TABLE team_dim
(team_id NUMBER(30) NOT NULL,
team_name VARCHAR2(20) NOT NULL,
team_division VARCHAR2(20) NOT NULL ,
team_league VARCHAR2(20) NOT NULL);

CREATE SEQUENCE team_dim_seq;

CREATE DIMENSION team_dim
LEVEL team IS team_dim.team_name
LEVEL team_division IS team_dim.team_division
LEVEL team_league IS team_dim.team_league
HIERARCHY team_rollup (
team CHILD OF
team_division CHILD OF
team_league );

INSERT INTO team_dim
VALUES (team_dim_seq.NEXTVAL, 'CHICAGO CUBS', 'MIDWEST', 'NATIONAL');
INSERT INTO team_dim
VALUES (team_dim_seq.NEXTVAL, 'HOUSTON ASTROS', 'MIDWEST', 'NATIONAL');
INSERT INTO team_dim
VALUES (team_dim_seq.NEXTVAL, 'ST. LOUIS CARDINALS', 'MIDWEST',
'NATIONAL');
INSERT INTO team_dim
VALUES (team_dim_seq.NEXTVAL, 'BOSTON RED SOX', 'EAST', 'AMERICAN');
INSERT INTO team_dim
VALUES (team_dim_seq.NEXTVAL, 'NEW YORK', 'EAST', 'AMERICAN');
COMMIT ;

CREATE UNIQUE INDEX team_dim_pk ON team_dim(team_id);
CREATE UNIQUE INDEX team_dim_uk ON team_dim(team_name,team_division,team_league);
ALTER TABLE team_dim ADD CONSTRAINT team_dim_pk PRIMARY KEY (team_id) RELY ENABLE NOVALIDATE;
ALTER TABLE team_dim ADD CONSTRAINT team_dim_uk UNIQUE (team_name,team_division,team_league) USING INDEX;
CREATE BITMAP INDEX team_dim_team_name_bidx ON team_dim(team_name);
CREATE BITMAP INDEX team_dim_team_division_bidx ON team_dim(team_division);
CREATE BITMAP INDEX team_dim_team_league_bidx ON team_dim(team_league);

CREATE TABLE player_dim
(player_id NUMBER(30) NOT NULL,
player_fname VARCHAR2(30) NOT NULL,
player_lname VARCHAR2 (30) NOT NULL ,
player_name VARCHAR2(65) NOT NULL,
player_bday DATE NOT NULL,
player_start_date DATE NOT NULL,
player_retire_date DATE);

CREATE SEQUENCE player_dim_seq;

CREATE DIMENSION player_dim
LEVEL player IS player_dim.player_id
ATTRIBUTE player DETERMINES
(player_dim.player_fname,
player_dim.player_lname,
player_dim.player_name,
player_dim.player_bday,
player_dim.player_start_date,
player_dim.player_retire_date);

INSERT INTO player_dim
VALUES (player_dim_seq.NEXTVAL, 'Ryne', 'Sandberg', 'Ryne Sandberg',
TO_DATE ('23-JAN-1945', 'DD-MON-YYYY'),
TO_DATE ('01-APR-1982', 'DD-MON-YYYY'),
TO_DATE ('23-AUG-2001', 'DD-MON-YYYY'));
INSERT INTO player_dim
VALUES (player_dim_seq.NEXTVAL, 'James', 'Colestock', 'James Colestock',
TO_DATE ('01-APR-1976', 'DD-MON-YYYY'),
TO_DATE ('01-APR-1989', 'DD-MON-YYYY'),
TO_DATE ('30-AUG-2006', 'DD-MON-YYYY'));
INSERT INTO player_dim
VALUES (player_dim_seq.NEXTVAL, 'Jim', 'Colestock', 'Jim Colestock',
TO_DATE ('01-APR-1976', 'DD-MON-YYYY'),
TO_DATE ('01-APR-1989', 'DD-MON-YYYY'),
TO_DATE ('30-AUG-2006', 'DD-MON-YYYY'));
INSERT INTO player_dim
VALUES (player_dim_seq.NEXTVAL, 'Jaime', 'Colestock', 'Jaime Colestock',
TO_DATE ('01-APR-1976', 'DD-MON-YYYY'),
TO_DATE ('01-APR-1989', 'DD-MON-YYYY'),
TO_DATE ('30-AUG-2006', 'DD-MON-YYYY'));
INSERT INTO player_dim
VALUES (player_dim_seq.NEXTVAL, 'Rob', 'Colestock', 'Rob Colestock',
TO_DATE ('01-APR-1976', 'DD-MON-YYYY'),
TO_DATE ('01-APR-1989', 'DD-MON-YYYY'),
TO_DATE ('30-AUG-2006', 'DD-MON-YYYY'));
INSERT INTO player_dim
VALUES (player_dim_seq.NEXTVAL, 'Larry', 'Colestock', 'Larry Colestock',
TO_DATE ('01-APR-1976', 'DD-MON-YYYY'),
TO_DATE ('01-APR-1989', 'DD-MON-YYYY'),
TO_DATE ('30-AUG-2006', 'DD-MON-YYYY'));
INSERT INTO player_dim
VALUES (player_dim_seq.NEXTVAL, 'Bob', 'Colestock', 'Bob Colestock',
TO_DATE ('01-APR-1976', 'DD-MON-YYYY'),
TO_DATE ('01-APR-1989', 'DD-MON-YYYY'),
TO_DATE ('30-AUG-2006', 'DD-MON-YYYY'));
INSERT INTO player_dim
VALUES (player_dim_seq.NEXTVAL, 'Bob', 'Dobalina', 'Bob Dobalina',
TO_DATE ('01-APR-1976', 'DD-MON-YYYY'),
TO_DATE ('01-APR-1989', 'DD-MON-YYYY'),
TO_DATE ('30-AUG-2006', 'DD-MON-YYYY'));
COMMIT ;

CREATE UNIQUE INDEX player_dim_pk ON player_dim(player_id);
CREATE UNIQUE INDEX player_dim_uk ON player_dim(player_name,player_bday, player_start_date, player_retire_date);
ALTER TABLE player_dim ADD CONSTRAINT player_dim_pk PRIMARY KEY (player_id) RELY ENABLE NOVALIDATE;
ALTER TABLE player_dim ADD CONSTRAINT player_dim_uk UNIQUE (player_name,player_bday, player_start_date, player_retire_date) USING INDEX;
CREATE BITMAP INDEX player_dim_player_fname_bidx ON player_dim(player_fname);
CREATE BITMAP INDEX player_dim_player_lname_bidx ON player_dim(player_lname);
CREATE BITMAP INDEX player_dim_player_name_bidx ON player_dim(player_name);
CREATE BITMAP INDEX player_dim_player_bday_bidx ON player_dim(player_bday);
CREATE BITMAP INDEX player_dim_player_sdate_bidx ON player_dim(player_start_date);
CREATE BITMAP INDEX player_dim_player_rdate_bidx ON player_dim(player_retire_date);

/* Create the Sample Fact Table */

CREATE TABLE batting_fact
(
game_id NUMBER(30) NOT NULL,
player_id NUMBER(30) NOT NULL,
team_id NUMBER(30) NOT NULL,
at_bats NUMBER(2) DEFAULT 0 NOT NULL,
walks NUMBER(2) DEFAULT 0 NOT NULL,
strike_outs NUMBER(2) DEFAULT 0 NOT NULL,
"1B" NUMBER(2) DEFAULT 0 NOT NULL,
"2B" NUMBER(2) DEFAULT 0 NOT NULL,
"3B" NUMBER(2) DEFAULT 0 NOT NULL,
hrs NUMBER(2) DEFAULT 0 NOT NULL
);

INSERT INTO batting_fact
SELECT gd.game_id, pd.player_id, td.team_id, 5, 1, 1, 1, 1, 1, 0
FROM game_dim gd, team_dim td, player_dim pd
WHERE td.team_name = 'CHICAGO CUBS' AND pd.player_name = 'Ryne Sandberg';
COMMIT ;

INSERT INTO batting_fact
SELECT gd.game_id, pd.player_id, td.team_id, 4, 0, 0, 1, 1, 1, 1
FROM game_dim gd, team_dim td, player_dim pd
WHERE td.team_name = 'CHICAGO CUBS' AND pd.player_name = 'James Colestock';
COMMIT ;

INSERT INTO batting_fact
SELECT gd.game_id, pd.player_id, td.team_id, 4, 0, 0, 1, 1, 1, 1
FROM game_dim gd, team_dim td, player_dim pd
WHERE td.team_name = 'CHICAGO CUBS' AND pd.player_name = 'Jim Colestock';
COMMIT ;

INSERT INTO batting_fact
SELECT gd.game_id, pd.player_id, td.team_id, 3, 0, 0, 0, 1, 1, 1
FROM game_dim gd, team_dim td, player_dim pd
WHERE td.team_name = 'BOSTON RED SOX' AND pd.player_name = 'Jaime Colestock';
COMMIT ;
INSERT INTO batting_fact
SELECT gd.game_id, pd.player_id, td.team_id, 2, 0, 0, 0, 0, 1, 1
FROM game_dim gd, team_dim td, player_dim pd
WHERE td.team_name = 'BOSTON RED SOX' AND pd.player_name = 'Rob Colestock';
COMMIT ;
INSERT INTO batting_fact
SELECT gd.game_id, pd.player_id, td.team_id, 2, 0, 0, 0, 0, 1, 1
FROM game_dim gd, team_dim td, player_dim pd
WHERE td.team_name = 'CHICAGO CUBS' AND pd.player_name = 'Larry Colestock';
COMMIT ;
INSERT INTO batting_fact
SELECT gd.game_id, pd.player_id, td.team_id, 3, 0, 0, 0, 1, 1, 1
FROM game_dim gd, team_dim td, player_dim pd
WHERE td.team_name = 'HOUSTON ASTROS' AND pd.player_name = 'Bob Colestock';
COMMIT ;
INSERT INTO batting_fact
SELECT gd.game_id, pd.player_id, td.team_id, 5, 0, 0, 0, 1, 1, 3
FROM game_dim gd, team_dim td, player_dim pd
WHERE td.team_name = 'HOUSTON ASTROS' AND pd.player_name = 'Bob Dobalina';
COMMIT ;

CREATE UNIQUE INDEX batting_fact_pk ON batting_fact(game_id,player_id);
ALTER TABLE batting_fact ADD CONSTRAINT batting_fact_pk PRIMARY KEY (game_id,player_id) RELY ENABLE NOVALIDATE;
CREATE BITMAP INDEX batting_fact_gmid_fk_bidx ON batting_fact(game_id);
CREATE BITMAP INDEX batting_fact_plyrid_fk_bidx ON batting_fact(player_id);
CREATE BITMAP INDEX batting_fact_teamid_fk_bidx ON batting_fact(team_id);
CREATE BITMAP INDEX batting_fact_at_bats_bidx ON batting_fact(at_bats);
CREATE BITMAP INDEX batting_fact_walks_bidx ON batting_fact(walks);
CREATE BITMAP INDEX batting_fact_so_bidx ON batting_fact(strike_outs);
CREATE BITMAP INDEX batting_fact_1b_bidx ON batting_fact("1B");
CREATE BITMAP INDEX batting_fact_2b_bidx ON batting_fact("2B");
CREATE BITMAP INDEX batting_fact_3b_bidx ON batting_fact("3B");
CREATE BITMAP INDEX batting_fact_hrs_bidx ON batting_fact(hrs);

ALTER TABLE batting_fact ADD (
CONSTRAINT batting_fact_gmid_fk
FOREIGN KEY (game_id)
REFERENCES game_dim (game_id) RELY DISABLE NOVALIDATE);

ALTER TABLE batting_fact ADD (
CONSTRAINT batting_fact_plyrid_fk
FOREIGN KEY (player_id)
REFERENCES player_dim (player_id) RELY DISABLE NOVALIDATE);

ALTER TABLE batting_fact ADD (
CONSTRAINT batting_fact_teamid_fk
FOREIGN KEY (team_id)
REFERENCES team_dim (team_id) RELY DISABLE NOVALIDATE);

/* Analyze the Tables */
EXEC dbms_stats.gather_schema_stats(ownname=>'SH',options=>'GATHER EMPTY',estimate_percent=>.001, degree=>2, cascade=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 100');

/* Query the sizes of the tables */

select segment_name, round(sum(bytes)/1024/1024) "MB" from user_segments
where segment_name like ('BATTING%')
or segment_name like ('GAME%')
or segment_name like ('PLAYER%')
or segment_name like ('TEAM%')
group by segment_name
order by 2 desc;

spool off;