Rem  SQL Access Advisor: Version 12.1.0.1.0 - Production
Rem  
Rem  Username:        SYS
Rem  Task:            SH_SAA
Rem  Execution date:   
Rem  

Rem  
Rem  Repartitioning table "SH"."SALES"
Rem  

SET SERVEROUTPUT ON
SET ECHO ON

Rem 
Rem Creating new partitioned table
Rem 
  CREATE TABLE "SH"."SALES1" 
   (	"PROD_ID" NUMBER, 
	"CUST_ID" NUMBER, 
	"TIME_ID" DATE, 
	"CHANNEL_ID" NUMBER, 
	"PROMO_ID" NUMBER, 
	"QUANTITY_SOLD" NUMBER(10,2), 
	"SELLER" NUMBER(6,0), 
	"FULFILLMENT_CENTER" NUMBER(6,0), 
	"COURIER_ORG" NUMBER(6,0), 
	"TAX_COUNTRY" VARCHAR2(3), 
	"TAX_REGION" VARCHAR2(3), 
	"AMOUNT_SOLD" NUMBER(10,2)
   ) 
PARTITION BY HASH ("TIME_ID") PARTITIONS 32;

Rem 
Rem Copying constraints to new partitioned table
Rem 
  ALTER TABLE "SH"."SALES1" MODIFY ("PROD_ID" NOT NULL ENABLE);
  ALTER TABLE "SH"."SALES1" MODIFY ("CUST_ID" NOT NULL ENABLE);
  ALTER TABLE "SH"."SALES1" MODIFY ("TIME_ID" NOT NULL ENABLE);
  ALTER TABLE "SH"."SALES1" MODIFY ("CHANNEL_ID" NOT NULL ENABLE);
  ALTER TABLE "SH"."SALES1" MODIFY ("PROMO_ID" NOT NULL ENABLE);
  ALTER TABLE "SH"."SALES1" MODIFY ("QUANTITY_SOLD" NOT NULL ENABLE);
  ALTER TABLE "SH"."SALES1" MODIFY ("SELLER" NOT NULL ENABLE);
  ALTER TABLE "SH"."SALES1" MODIFY ("FULFILLMENT_CENTER" NOT NULL ENABLE);
  ALTER TABLE "SH"."SALES1" MODIFY ("COURIER_ORG" NOT NULL ENABLE);
  ALTER TABLE "SH"."SALES1" MODIFY ("TAX_COUNTRY" NOT NULL ENABLE);
  ALTER TABLE "SH"."SALES1" MODIFY ("AMOUNT_SOLD" NOT NULL ENABLE);
Rem 
Rem Populating new partitioned table with data from original table
Rem 
INSERT /*+ APPEND */ INTO "SH"."SALES1"
    SELECT * FROM "SH"."SALES";
COMMIT;

begin
  dbms_stats.gather_table_stats('"SH"', '"SALES1"', NULL, dbms_stats.auto_sample_size);
end;
/

Rem 
Rem Renaming tables to give new partitioned table the original table name
Rem 
ALTER TABLE "SH"."SALES" RENAME TO "SALES11";
ALTER TABLE "SH"."SALES1" RENAME TO "SALES";


Rem  
Rem  Repartitioning table "SH"."CUSTOMERS"
Rem  

SET SERVEROUTPUT ON
SET ECHO ON

Rem 
Rem Creating new partitioned table
Rem 
  CREATE TABLE "SH"."CUSTOMERS1" 
   (	"CUST_ID" NUMBER, 
	"CUST_FIRST_NAME" VARCHAR2(20), 
	"CUST_LAST_NAME" VARCHAR2(40), 
	"CUST_GENDER" CHAR(1), 
	"CUST_YEAR_OF_BIRTH" NUMBER(4,0), 
	"CUST_MARITAL_STATUS" VARCHAR2(20), 
	"CUST_STREET_ADDRESS" VARCHAR2(40), 
	"CUST_POSTAL_CODE" VARCHAR2(10), 
	"CUST_CITY" VARCHAR2(30), 
	"CUST_CITY_ID" NUMBER, 
	"CUST_STATE_PROVINCE" VARCHAR2(40), 
	"CUST_STATE_PROVINCE_ID" NUMBER, 
	"COUNTRY_ID" NUMBER, 
	"CUST_MAIN_PHONE_NUMBER" VARCHAR2(25), 
	"CUST_INCOME_LEVEL" VARCHAR2(30), 
	"CUST_CREDIT_LIMIT" NUMBER, 
	"CUST_EMAIL" VARCHAR2(50), 
	"CUST_TOTAL" VARCHAR2(14), 
	"CUST_TOTAL_ID" NUMBER, 
	"CUST_SRC_ID" NUMBER, 
	"CUST_EFF_FROM" DATE, 
	"CUST_EFF_TO" DATE, 
	"CUST_VALID" VARCHAR2(1)
   ) 
PARTITION BY RANGE ("CUST_ID") INTERVAL( 5080) ( PARTITION VALUES LESS THAN (5080)
       );

Rem 
Rem Copying constraints to new partitioned table
Rem 
  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_ID" NOT NULL ENABLE);
  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_FIRST_NAME" NOT NULL ENABLE);
  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_LAST_NAME" NOT NULL ENABLE);
  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_GENDER" NOT NULL ENABLE);
  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_YEAR_OF_BIRTH" NOT NULL ENABLE);
  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_STREET_ADDRESS" NOT NULL ENABLE);
  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_POSTAL_CODE" NOT NULL ENABLE);
  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_CITY" NOT NULL ENABLE);
  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_CITY_ID" NOT NULL ENABLE);
  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_STATE_PROVINCE" NOT NULL ENABLE);
  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_STATE_PROVINCE_ID" NOT NULL ENABLE);
  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("COUNTRY_ID" NOT NULL ENABLE);
  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_MAIN_PHONE_NUMBER" NOT NULL ENABLE);
  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_TOTAL" NOT NULL ENABLE);
  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_TOTAL_ID" NOT NULL ENABLE);
  ALTER TABLE "SH"."CUSTOMERS1" ADD CONSTRAINT "CUSTOMERS_PK1" PRIMARY KEY ("CUST_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  TABLESPACE "SH"  ENABLE NOVALIDATE;
Rem 
Rem Copying indexes to new partitioned table
Rem 
  CREATE UNIQUE INDEX "SH"."CUSTOMERS_PK1" ON "SH"."CUSTOMERS1" ("CUST_ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  TABLESPACE "SH" 
  PARALLEL 4 ;
Rem 
Rem Populating new partitioned table with data from original table
Rem 
INSERT /*+ APPEND */ INTO "SH"."CUSTOMERS1"
    SELECT * FROM "SH"."CUSTOMERS";
COMMIT;

begin
  dbms_stats.gather_table_stats('"SH"', '"CUSTOMERS1"', NULL, dbms_stats.auto_sample_size);
end;
/

Rem 
Rem Renaming tables to give new partitioned table the original table name
Rem 
ALTER TABLE "SH"."CUSTOMERS" RENAME TO "CUSTOMERS11";
ALTER TABLE "SH"."CUSTOMERS1" RENAME TO "CUSTOMERS";


Rem  
Rem  Repartitioning table "SH"."TIMES"
Rem  

SET SERVEROUTPUT ON
SET ECHO ON

Rem 
Rem Creating new partitioned table
Rem 
  CREATE TABLE "SH"."TIMES1" 
   (	"TIME_ID" DATE, 
	"DAY_NAME" VARCHAR2(9), 
	"DAY_NUMBER_IN_MONTH" VARCHAR2(2), 
	"DAY_NUMBER_IN_YEAR" VARCHAR2(3), 
	"CALENDAR_YEAR" VARCHAR2(4), 
	"CALENDAR_QUARTER_NUMBER" VARCHAR2(1), 
	"CALENDAR_MONTH_NUMBER" VARCHAR2(2), 
	"CALENDAR_WEEK_NUMBER" VARCHAR2(2), 
	"CALENDAR_MONTH_DESC" VARCHAR2(7), 
	"CALENDAR_QUARTER_DESC" VARCHAR2(6)
   ) 
PARTITION BY HASH ("TIME_ID") PARTITIONS 32;

Rem 
Rem Copying constraints to new partitioned table
Rem 
  ALTER TABLE "SH"."TIMES1" ADD CONSTRAINT "TIMES_PK1" PRIMARY KEY ("TIME_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  TABLESPACE "SH"  ENABLE NOVALIDATE;
Rem 
Rem Copying indexes to new partitioned table
Rem 
  CREATE UNIQUE INDEX "SH"."TIMES_PK1" ON "SH"."TIMES1" ("TIME_ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING 
  TABLESPACE "SH" 
  PARALLEL 4 ;
  CREATE INDEX "SH"."IDX$$_00A900011" ON "SH"."TIMES1" ("CALENDAR_QUARTER_DESC", "TIME_ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE "SH" ;
Rem 
Rem Populating new partitioned table with data from original table
Rem 
INSERT /*+ APPEND */ INTO "SH"."TIMES1"
    SELECT * FROM "SH"."TIMES";
COMMIT;

begin
  dbms_stats.gather_table_stats('"SH"', '"TIMES1"', NULL, dbms_stats.auto_sample_size);
end;
/

Rem 
Rem Renaming tables to give new partitioned table the original table name
Rem 
ALTER TABLE "SH"."TIMES" RENAME TO "TIMES11";
ALTER TABLE "SH"."TIMES1" RENAME TO "TIMES";


CREATE MATERIALIZED VIEW LOG ON
    "SH"."TIMES"
    WITH ROWID, SEQUENCE("TIME_ID","CALENDAR_QUARTER_DESC")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
    "SH"."SALES"
    WITH ROWID, SEQUENCE("CUST_ID","TIME_ID","AMOUNT_SOLD")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
    "SH"."CUSTOMERS"
    WITH ROWID, SEQUENCE("CUST_ID","CUST_FIRST_NAME","CUST_LAST_NAME")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW "SYS"."MV$$_00C00000"
    REFRESH FAST WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT SH.TIMES.CALENDAR_QUARTER_DESC C1, SH.CUSTOMERS.CUST_LAST_NAME C2, SH.CUSTOMERS.CUST_FIRST_NAME
       C3, SH.CUSTOMERS.CUST_ID C4, SUM("SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH"."SALES"."AMOUNT_SOLD")
       M2, COUNT(*) M3 FROM SH.TIMES, SH.SALES, SH.CUSTOMERS WHERE SH.SALES.TIME_ID
       = SH.TIMES.TIME_ID AND SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID AND (SH.TIMES.CALENDAR_QUARTER_DESC
       <= '2014-4') AND (SH.TIMES.CALENDAR_QUARTER_DESC >= '1995-3') GROUP BY
       SH.TIMES.CALENDAR_QUARTER_DESC, SH.CUSTOMERS.CUST_LAST_NAME, SH.CUSTOMERS.CUST_FIRST_NAME,
       SH.CUSTOMERS.CUST_ID;

begin
  dbms_stats.gather_table_stats('"SYS"','"MV$$_00C00000"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE BITMAP INDEX "SYS"."MV$$_00C00000_IDX$$_00C00000"
    ON "SYS"."MV$$_00C00000"
    ("C1")
    COMPUTE STATISTICS;