442
C.3
GENERATE.SQL
,CONSTRAINT PK_Purchase PRIMARY KEY (purchase_id) USING
INDEX TABLESPACE INDX
,CONSTRAINT FK_Purchase_Time FOREIGN KEY (time_id)
REFERENCES Time
,CONSTRAINT FK_Purchase_Product FOREIGN KEY (product_id)
REFERENCES Product
,CONSTRAINT FK_Purchase_Location FOREIGN KEY
(location_id) REFERENCES Location
,CONSTRAINT FK_Purchase_Industry FOREIGN KEY
(industry_id) REFERENCES Industry
) TABLESPACE DATA NOLOGGING;
CREATE INDEX XFX_Purchase_Time ON Purchase(time_id)
TABLESPACE INDX NOLOGGING;
CREATE INDEX XFX_Purchase_Product ON Purchase(product_id)
TABLESPACE INDX NOLOGGING;
CREATE INDEX XFX_Purchase_Location ON Purchase(location_id)
TABLESPACE INDX NOLOGGING;
CREATE INDEX XFX_Purchase_Industry ON Purchase(industry_id)
TABLESPACE INDX NOLOGGING;
spool off;
C.3.6 DATAFACTSDW.SQL
These tables have millions of rows and are provided on my web site as a
download in various forms.
spool log/dataFactsDW.log;
analyze table sale compute statistics;
analyze table purchase compute statistics;
spool off;
Sample data only (see http://www.oracledbaexpert.com/oracle/
OracleDataWarehouseTuning/index.html).
insert into sale values(1,6,57,39,12,530.87,'18-JAN-
00',530.87,'18-JAN-00',4185.15,'18-JAN-00','Sales Invoice
53186',-10,530.87,18-JAN-00');
insert into sale values(2,6,12,39,12,163.25,'18-JAN-
00',163.25,'18-JAN-00',4185.15,'18-JAN-00','Sales Invoice
53186',-2,163.25,18-JAN-00');

Get Oracle Data Warehouse Tuning for 10g now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.