--------------------------------------------------------
--  File created - Wednesday-September-30-2015   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table DI_EPA_REGION
--------------------------------------------------------

  CREATE TABLE "STORETW"."DI_EPA_REGION" 
   (	"PK_ISN" NUMBER(12,0), 
	"EPA_REGION_CODE" VARCHAR2(2 CHAR), 
	"EPA_REGION_NAME" VARCHAR2(256 CHAR)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 COMPRESS FOR OLTP NOLOGGING
  STORAGE(INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "STORETDATA_STORMODP" 
   CACHE ;

   COMMENT ON COLUMN "STORETW"."DI_EPA_REGION"."PK_ISN" IS 'Primary Key. Generated using sequence object SEQ_EPA_REGION.';
   COMMENT ON COLUMN "STORETW"."DI_EPA_REGION"."EPA_REGION_CODE" IS 'The code of the EPA region.';
   COMMENT ON COLUMN "STORETW"."DI_EPA_REGION"."EPA_REGION_NAME" IS 'The name of the EPA region.';
   COMMENT ON TABLE "STORETW"."DI_EPA_REGION"  IS 'EPA Region dimension table';
REM INSERTING into STORETW.DI_EPA_REGION
SET DEFINE OFF;
Insert into STORETW.DI_EPA_REGION (PK_ISN,EPA_REGION_CODE,EPA_REGION_NAME) values (1,'1','Region 1 (CT, ME, MA, NH, RI, VT)');
Insert into STORETW.DI_EPA_REGION (PK_ISN,EPA_REGION_CODE,EPA_REGION_NAME) values (2,'2','Region 2 (NJ, NY, PR, VI)');
Insert into STORETW.DI_EPA_REGION (PK_ISN,EPA_REGION_CODE,EPA_REGION_NAME) values (3,'3','Region 3 (DE, DC, MD, PA, VA, WA)');
Insert into STORETW.DI_EPA_REGION (PK_ISN,EPA_REGION_CODE,EPA_REGION_NAME) values (4,'4','Region 4 (AL, FL, GA, KY, MS, NC, SC, TN)');
Insert into STORETW.DI_EPA_REGION (PK_ISN,EPA_REGION_CODE,EPA_REGION_NAME) values (5,'5','Region 5 (IL, IN, MI, MN, OH, WI)');
Insert into STORETW.DI_EPA_REGION (PK_ISN,EPA_REGION_CODE,EPA_REGION_NAME) values (6,'6','Region 6 (LA, AR, OK, NM, TX)');
Insert into STORETW.DI_EPA_REGION (PK_ISN,EPA_REGION_CODE,EPA_REGION_NAME) values (7,'7','Region 7 (IA, KS, MO, NE)');
Insert into STORETW.DI_EPA_REGION (PK_ISN,EPA_REGION_CODE,EPA_REGION_NAME) values (8,'8','Region 8 (CO, MT, ND, SD, UT, WY)');
Insert into STORETW.DI_EPA_REGION (PK_ISN,EPA_REGION_CODE,EPA_REGION_NAME) values (9,'9','Region 9 (AS, AZ, CA, GU, HI, MP, NV)');
Insert into STORETW.DI_EPA_REGION (PK_ISN,EPA_REGION_CODE,EPA_REGION_NAME) values (10,'10','Region 10 (AK, ID, OR, WA)');
--------------------------------------------------------
--  DDL for Index IER_ERCODE
--------------------------------------------------------

  CREATE INDEX "STORETW"."IER_ERCODE" ON "STORETW"."DI_EPA_REGION" ("EPA_REGION_CODE") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "STORETINDX_STORMODP" ;
--------------------------------------------------------
--  DDL for Index PK_EPA_REGION
--------------------------------------------------------

  CREATE UNIQUE INDEX "STORETW"."PK_EPA_REGION" ON "STORETW"."DI_EPA_REGION" ("PK_ISN") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "STORETDATA_STORMODP" ;
--------------------------------------------------------
--  Constraints for Table DI_EPA_REGION
--------------------------------------------------------

  ALTER TABLE "STORETW"."DI_EPA_REGION" ADD CONSTRAINT "PK_EPA_REGION" PRIMARY KEY ("PK_ISN")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "STORETDATA_STORMODP"  ENABLE;
  ALTER TABLE "STORETW"."DI_EPA_REGION" MODIFY ("EPA_REGION_NAME" NOT NULL ENABLE);
  ALTER TABLE "STORETW"."DI_EPA_REGION" MODIFY ("EPA_REGION_CODE" NOT NULL ENABLE);
