Oracle Data Warehouse Tuning for 10g

C.3

GENERATE.SQL 437

Appendix C

create sequence industry_seq start with 1 increment by 1

nomaxvalue nocycle;

create sequence location_seq start with 1 increment by 1

nomaxvalue nocycle;

create sequence product_seq start with 1 increment by 1

nomaxvalue nocycle;

create sequence time_seq start with 1 increment by 1

nomaxvalue nocycle;

spool off;

C.3.4 DATADIMS.SQL

spool log/dataDimsDW.log;

Sample data on

ly (see http://www.oracledbaexpert.com/oracle/

OracleDataWarehouseTuning/index.html).

insert into industry values(1,'Aerospace and Defense');

insert into industry values(2,'Automotive');

insert into industry values(3,'Chemicals');

insert into industry values(4,'Communications');

insert into industry values(5,'Consumer Goods');

. . .

Sample data only (see http://www.oracledbaexpert.com/oracle/

OracleDataWarehouseTuning/index.html).

. . .

insert into location values(130,'North America','United

States','','Guam');

insert into location values(131,'North America','United

States','','Midway');

insert into location values(132,'North

America','Greenland','','Godthab');

insert into location values(133,'North

America','Greenland','','Thule');

insert into location values(134,'North

America','Canada','','Burlington');

insert into location values(135,'Europe','Northern

Ireland','','Belfast');

438

C.3

GENERATE.SQL

insert into location values(136,'Europe','Slovak

Republic','','Bratislava');

insert into location

values(137,'Europe','Yugoslavia','','Belgrade');

insert into location

values(138,'Europe','Scotland','','Edinburgh');

insert into location values(139,'Europe','United

Kingdom','','London');

insert into location values(140,'Europe','United

Kingdom','','Ascension Island');

insert into location values(141,'Europe','United

Kingdom','','Diego Garcia');

. . .

Sample data only (see http://www.oracledbaexpert.com/oracle/

OracleDataWarehouseTuning/index.html).

insert into product values(1,'PDAs','Sony CLIE S360

Handheld',205.86);

insert into product values(2,'Printers','Epson Stylus Photo

820 InkJet Printer',115.08);

insert into product values(4,'Printers','Epson Stylus C80

Color Inkjet Printer',171.78);

insert into product values(6,'Printers','Epson Stylus C40UX

InkJet Printer',61.98);

insert into product values(7,'Printers','Epson Stylus C60

Inkjet Printer',103.56);

. . .

Sample data only (see http://www.oracledbaexpert.com/oracle/Oracle

DataWarehouseTuning/index.html).

insert into time values(1,1,'Jan','January',1,1995);

insert into time values(2,1,'Jan','January',1,1996);

insert into time values(3,1,'Jan','January',1,1997);

insert into time values(4,1,'Jan','January',1,1998);

insert into time values(5,1,'Jan','January',1,1999);

insert into time values(6,1,'Jan','January',1,2000);

insert into time values(7,1,'Jan','January',1,2001);

insert into time values(8,1,'Jan','January',1,2002);

insert into time values(9,1,'Jan','January',1,2003);

C.3

GENERATE.SQL 439

Appendix C

insert into time values(10,1,'Jan','January',1,2004);

insert into time values(11,1,'Jan','January',1,2005);

insert into time values(12,1,'Jan','January',1,2006);

insert into time values(13,2,'Feb','February',1,1995);

. . .

--

--This script created the contents of the original TIME table

--

declare

i integer;

d date default '01-JAN-2000';

noofyrs integer default 7;

begin

for i in 0..(24*365*noofyrs) loop

d:=d+1/24;

insert into time

(

time_id

,hh24#

,dayofweek#

,dayofmonth#

,dyabbrev

,dayname

,weekofmonth#

,weekofyear#

,month#

,monabbrev

,monthname

,quarter#

,year#

)

values

(

time_seq.nextval

,TO_NUMBER(TO_CHAR(d,'HH24'))

,TO_NUMBER(TO_CHAR(d,'D'))

,TO_NUMBER(TO_CHAR(d,'DD'))

,INITCAP(TO_CHAR(d,'DY'))

,INITCAP(TO_CHAR(d,'DAY'))