Enterprise Software Solutions Oracle

Oracle

Modeling Notes:

	Avoid tables in excess of 1 billion rows
	If large tables are needed, use partitioning across a dimensional attribute such an month.
	For best performance, fact tables should always have primary keys and other indexes for most combinations of dimensions.

DDL

DDL to build time dimension tables with indexes:

	CREATE TABLE Time_Month( Time_Month_ID INT, Time_Month_Desc VARCHAR2(12), PRIMARY KEY(Time_Month_ID));
	CREATE TABLE Time_Date( Time_Date DATE, Time_Month_ID INT, PRIMARY KEY(Time_Date));

Create Bitmap Index:

CREATE BITMAP INDEX Call_Day_Base_Fact_XIE1 ON MySchema.Fact_Sales_Calls
(Period_ID ASC)
PCTFREE 5
INITRANS 2
MAXTRANS 255
TABLESPACE mdidx;

Create uniqu index:

CREATE UNIQUE INDEX Fact_Sales_Call_Base_XPK ON MySchema.Fact_Sales_Call_Base
(Terr_ID ASC, Cust_ID ASC, Period_ID ASC, Cust_ID_Seq ASC)
PCTFREE 5
INITRANS 2
MAXTRANS 255
TABLESPACE mdidx;

Create Foreign Key Constraint:

ALTER TABLE MySchema.Fact_Sales_Call_Account
ADD ( CONSTRAINT R_275
FOREIGN KEY (Acct_ID, Acct_ID_Seq)
REFERENCES MySchema.LU_ACCOUNT ) ;

[ Home ] [ Up ] [ Oracle PL/SQL ]

Oracle

DDL

Send mail to Webmaster@ESScorporation.com with questions or comments about this web site. Copyright © 2004 Enterprise Software Solutions, Inc. Last modified: July 29, 2004

Send mail to Webmaster@ESScorporation.com with questions or comments about this web site.
Copyright © 2004 Enterprise Software Solutions, Inc.
Last modified: July 29, 2004