课程第11/12次（2018-07-09星期一，2018-07-13星期五）

botang

SYSTEM分区：

1. CREATE TABLE hr.systab (c1 integer, c2 integer)
   
2. PARTITION BY SYSTEM
   
3. (
   
4.   PARTITION p1 TABLESPACE users,
   
5.   PARTITION p2 TABLESPACE example,
   
6.   PARTITION p3 TABLESPACE users,
   
7.   PARTITION p4 TABLESPACE example
   
8. );
   
9. 
   
10. 
    
11.     select    *  from dba_part_tables  pt
    
12.     where pt.owner='HR' and pt.table_name='SYSTAB';
    
13. 
    
14.     select  * from dba_tab_partitions  tp
    
15.     where tp.table_owner='HR' and tp.table_name='SYSTAB';
    
16.    
    
17.     INSERT INTO hr.systab VALUES (4,5);会报错

复制代码

1.     INSERT INTO hr.systab PARTITION (p1) VALUES (4,5);
   
2.     commit;
   
3.    
   
4.     select  * from hr.systab  partition ( p1);
   
5.    

复制代码

----------------虚拟列表：

1. 
   
2. 
   
3. create table hr.t5006_a  ( a  number ,
   
4.                                        b   as  (a*2 )  );
   
5. 
   
6. select * from dba_tab_columns  tc
   
7. where tc.OWNER='HR' and tc.TABLE_NAME='T5006_A';
   
8. 
   
9. insert into hr.T5006_A (a)  values  (1    );
   
10. commit;
    
11. 
    
12. begin
    
13.     for i in 2..300
    
14.     loop
    
15.        insert into hr.T5006_A (a)  values  (i    );
    
16.      end loop;
    
17.      commit;
    
18.   end;
    
19. 
    
20. begin
    
21.     dbms_stats.gather_table_stats(ownname => 'HR',
    
22.     tabname => 'T5006_A',
    
23.     method_opt => 'for columns b size 254 for all columns size auto');
    
24.   end;
    
25.   
    
26.   select  * from dba_tab_col_statistics tcs
    
27.    where tcs.owner='HR'  and tcs.table_name='T5006_A';
    
28.    
    
29.    select  * from hr.t5006_a;
    
30.    
    
31.     update hr.t5006_a set b=4 where a=1;会报错
    
32. 
    
33. 
    

复制代码

1. create table hr.t5006_b  ( a  varchar2(10) ,
   
2.                                        b   as  (a||'X' )  )
   
3.            partition by list (b)
   
4.          (
   
5.           partition p1  values ('AX','BX'),
   
6.           partition p2 values ('CX')
   
7.          );                           
   
8.       
   
9. 
   
10. insert into hr.t5006_b  (a ) values ('A');
    
11. 
    
12. commit;
    
13. 
    
14. select  * from hr.t5006_b  partition (p1);

复制代码

语法上可以不写virtual：

1. create table hr.t5006_c  ( a  number ,
   
2.                                        b   as  (a*2 )  virtual );
   

复制代码

--------------------------- 引用分区，要特别注意外键非空：

1. create table hr.t5006_parent (  a number  primary key  , b varchar2(20) )
   
2. partition by range ( a )
   
3. (
   
4. partition p1 values less than(100),
   
5. partition p2 values less than (maxvalue)
   
6. );
   
7. 
   
8. create table hr.t5006_son ( a number      constraint fk_t5006  references hr.t5006_parent  ,
   
9.                                            c date )
   
10. partition by  reference  (fk_t5006); 会报错:
    

复制代码

1. create table hr.t5006_son ( a number  not null    constraint fk_t5006  references hr.t5006_parent  ,
   
2.                                            c date )
   
3. partition by  reference  (fk_t5006);        

复制代码

------------------------引用分区的自动维护：

1. create table hr.t5006_parent (  a number  primary key  , b varchar2(20) )
   
2. partition by range ( a )
   
3. (
   
4. partition p1 values less than(100),
   
5. partition p2 values less than (maxvalue)
   
6. );
   
7. 
   
8. create table hr.t5006_son ( a number  not null    constraint fk_t5006  references hr.t5006_parent  ,
   
9.                                            c date )
   
10. partition by  reference  (fk_t5006);                                          
    
11. ----------------------------------------
    
12. select table_name, partition_name, high_value
    
13. from dba_tab_partitions
    
14. where table_name in ('T5006_PARENT','T5006_SON')
    
15. order by partition_position, table_name;
    
16. ----------------------------------------
    
17. 
    
18. 
    
19. insert into hr.t5006_parent values ( 99,'A99' ) ;
    
20. commit;
    
21. 
    
22. insert into hr.t5006_son  values (99,sysdate );
    
23. commit;
    
24. 
    
25. select  * from hr.t5006_son partition (p2) ;
    
26. 
    
27. -----------------------------------------
    
28. 
    
29. alter table hr.t5006_parent  split partition p1 at ( 50)  into
    
30. ( partition p11 , partition p12);
    
31. ------------------------------------------
    
32. select table_name, partition_name, high_value
    
33. from dba_tab_partitions
    
34. where table_name in ('T5006_PARENT','T5006_SON')
    
35. order by partition_position, table_name;
    
36. -----------------------------------------------------------------------
    
37. alter table hr.t5006_parent merge partitions p11,  p12
    
38. into partition p1;
    
39. -------------------------------------------------
    
40. select table_name, partition_name, high_value
    
41. from dba_tab_partitions
    
42. where table_name in ('T5006_PARENT','T5006_SON')
    
43. order by partition_position, table_name;
    
44. 
    

复制代码

---------------------分区键并不是主外键（书上的例子）:

1. 
   
2. 
   
3. create table hr.t5006_p ( a number primary key , b date )
   
4. partition by range ( b )
   
5. (
   
6. partition p1 values less than(  to_date ('2000-01-01','YYYY-MM-DD' )),
   
7. partition p2 values less than (  to_date ('2010-01-01','YYYY-MM-DD') )
   
8. );
   
9. 
   
10. 
    
11. 
    
12. create table hr.t5006_s ( a number not null constraint fk_t5006_s references hr.t5006_p ,
    
13. b date )
    
14. partition by reference (fk_t5006_s);
    
15. 
    
16. 
    
17. insert into hr.t5006_p values ( 1,  to_date ('2009-01-01','YYYY-MM-DD') );
    
18. 
    
19. commit;
    
20. 
    
21. insert into hr.t5006_s values ( 1,   to_date ('1999-01-01','YYYY-MM-DD') );
    
22. 
    
23. commit;
    
24. 
    
25. select table_name, partition_name, high_value
    
26. from dba_tab_partitions
    
27. where table_name in ('T5006_P','T5006_S')
    
28. order by partition_position, table_name;
    
29. 
    
30. 
    
31. select  * from hr.t5006_p partition (p2) ;
    
32. 
    
33. select  * from hr.t5006_s partition (p2);
    
34. 
    
35. select  * from hr.t5006_s;
    
36. select  * from hr.t5006_s partition (p2);

复制代码

以上这种情况，son表的分区范围(date)与 parent表是不一样的，son表的分区分布：
主键（1）在p2，子键(1)就在子表自己的p2上。
--------------------------------------复合分区
有办法写temple的语法：

1. CREATE TABLE hr.sales (   prod_id NUMBER(6) NOT NULL,
   
2.                                              cust_id NUMBER NOT NULL,
   
3.                                              time_id DATE NOT NULL,
   
4.                                              channel_id char(1) NOT NULL,
   
5.                                              promo_id NUMBER (6) NOT NULL,
   
6.                                              quantity_sold NUMBER(3) NOT NULL,
   
7.                                              amount_sold NUMBER(10,2) NOT NULL )
   
8. PARTITION BY RANGE (time_id)
   
9. SUBPARTITION BY RANGE (cust_id)
   
10. SUBPARTITION TEMPLATE
    
11.    (  SUBPARTITION sp1 VALUES LESS THAN (50000),   
    
12.       SUBPARTITION sp2 VALUES LESS THAN (100000),
    
13.       SUBPARTITION sp3 VALUES LESS THAN (150000),
    
14.       SUBPARTITION sp4 VALUES LESS THAN (MAXVALUE) )
    
15. (
    
16.   PARTITION  p1 VALUES LESS THAN (TO_DATE('1-4-2007','DD-MM-YYYY')),
    
17.   PARTITION  p2 VALUES LESS THAN (TO_DATE('1-7-2007','DD-MM-YYYY')),
    
18.   PARTITION  p3 VALUES LESS THAN (TO_DATE('1-8-2007','DD-MM-YYYY')),
    
19.   PARTITION  p4 VALUES LESS THAN (TO_DATE('1-1-2008','DD-MM-YYYY'))
    
20. );
    
21. 
    
22. 
    
23. select  * from dba_tab_partitions  tp
    
24.   where tp.table_owner='HR' and tp.table_name='SALES';
    
25. 
    
26. select  * from dba_tab_subpartitions tp
    
27.   where tp.table_owner='HR' and tp.table_name='SALES';
    

复制代码

没办法写template的语法：

1. create table shipments
   
2. ( order_id number not null
   
3. , order_date date not null
   
4. , ship_date date not null
   
5. , customer_id number not null
   
6. , sales_amount number not null
   
7. )
   
8. partition by range (order_date)
   
9. subpartition by range (ship_date)
   
10. ( partition p_2006_jul values
    
11.   less than (to_date('01-AUG-2006','dd-MON-yyyy'))
    
12.   ( subpartition p_2006_jul_early values
    
13.     less than (to_date('15-AUG-2006','dd-MON-yyyy'))
    
14.   , subpartition p_2006_jul_agreed values
    
15.     less than (to_date('01-SEP-2006','dd-MON-yyyy'))
    
16.   , subpartition p_2006_jul_late values less than (maxvalue)
    
17.   )
    
18. , partition p_2006_aug values
    
19.   less than (to_date('01-SEP-2006','dd-MON-yyyy'))
    
20.   ( subpartition p_2006_aug_early values
    
21.     less than (to_date('15-SEP-2006','dd-MON-yyyy'))
    
22.   , subpartition p_2006_aug_agreed values
    
23.     less than (to_date('01-OCT-2006','dd-MON-yyyy'))
    
24.   , subpartition p_2006_aug_late values less than (maxvalue)
    
25.   )
    
26. , partition p_2006_sep values
    
27.   less than (to_date('01-OCT-2006','dd-MON-yyyy'))
    
28.   ( subpartition p_2006_sep_early values
    
29.     less than (to_date('15-OCT-2006','dd-MON-yyyy'))
    
30.   , subpartition p_2006_sep_agreed values
    
31.     less than (to_date('01-NOV-2006','dd-MON-yyyy'))
    
32.   , subpartition p_2006_sep_late values less than (maxvalue)
    
33.   )
    
34. , partition p_2006_oct values
    
35.   less than (to_date('01-NOV-2006','dd-MON-yyyy'))
    
36.   ( subpartition p_2006_oct_early values
    
37.     less than (to_date('15-NOV-2006','dd-MON-yyyy'))
    
38.   , subpartition p_2006_oct_agreed values
    
39.     less than (to_date('01-DEC-2006','dd-MON-yyyy'))
    
40.   , subpartition p_2006_oct_late values less than (maxvalue)
    
41.   )
    
42. , partition p_2006_nov values
    
43.   less than (to_date('01-DEC-2006','dd-MON-yyyy'))
    
44.   ( subpartition p_2006_nov_early values
    
45.   less than (to_date('15-DEC-2006','dd-MON-yyyy'))
    
46.   , subpartition p_2006_nov_agreed values
    
47.   less than (to_date('01-JAN-2007','dd-MON-yyyy'))
    
48.   , subpartition p_2006_nov_late values less than (maxvalue)
    
49.   )
    
50. , partition p_2006_dec values
    
51.   less than (to_date('01-JAN-2007','dd-MON-yyyy'))
    
52.   ( subpartition p_2006_dec_early values
    
53.     less than (to_date('15-JAN-2007','dd-MON-yyyy'))
    
54.   , subpartition p_2006_dec_agreed values
    
55.     less than (to_date('01-FEB-2007','dd-MON-yyyy'))
    
56.   , subpartition p_2006_dec_late values less than (maxvalue)
    
57.   )
    
58. )
    
59. 
    

复制代码

制造SAA新模板：

1. variable     l_task_id number;
   
2. variable     l_name varchar2(30);
   
3. 
   
4. 
   
5. Rem make a new template to have a minimum of 100 rows only for partitioning
   
6. 
   
7. begin
   
8. :l_name := 'SQLACCESS_PARTITION_SMALL';
   
9. end;
   
10. /
    
11. 
    
12. begin
    
13. dbms_advisor.update_task_attributes(:l_name,null,null,'FALSE');
    
14. dbms_advisor.delete_task(:l_name);
    
15. end;
    
16. /
    
17. 
    
18. begin
    
19. dbms_advisor.create_task(dbms_advisor.sqlaccess_advisor,:l_task_id,:l_name,'For demo purposes only');
    
20. dbms_advisor.set_task_parameter(:l_name,'_PA_MIN_TABLE_SIZE_PART',100);
    
21. dbms_advisor.set_task_parameter(:l_name,'EXECUTION_TYPE','FULL');
    
22. dbms_advisor.set_task_parameter(:l_name,'ANALYSIS_SCOPE','ALL');
    
23. dbms_advisor.set_task_parameter(:l_name,'MODE','COMPREHENSIVE');
    
24. 
    
25. dbms_advisor.update_task_attributes(:l_name,null,null,'TRUE','TRUE');
    
26. end;
    
27. /
    

复制代码

	Tasks and Templates
	View Select Name Description Last Modified Type SQLACCESS_EMTASK Default Enterprise Manager task template Jul 10, 2018 6:06:22 AM CST Default Template SQLACCESS_GENERAL General purpose database template Jul 10, 2018 6:06:21 AM CST Template SQLACCESS_OLTP OLTP database template Jul 10, 2018 6:06:21 AM CST Template SQLACCESS_PARTITION_SMALL For demo purposes only Jul 10, 2018 6:28:29 AM CST Template SQLACCESS_WAREHOUSE Data Warehouse database template Jul 10, 2018 6:06:22 AM CST Template

以下是SAA的典型的建议（数据仓库模板）：
Rem  SQL Access Advisor: Version 11.2.0.1.0 - Production
Rem  
Rem  Username:        SYS
Rem  Task:            SQLACCESS1553744
Rem  Execution date:   
Rem  

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(30),
    "CUST_TOTAL" VARCHAR2(14),
    "CUST_TOTAL_ID" NUMBER,
    "CUST_SRC_ID" NUMBER,
    "CUST_EFF_FROM" DATE,
    "CUST_EFF_TO" DATE,
    "CUST_VALID" VARCHAR2(1)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  TABLESPACE "EXAMPLE"
PARTITION BY RANGE ("CUST_ID") INTERVAL( 3000) ( PARTITION VALUES LESS THAN (3000)
       );

Rem
Rem Copying comments to new partitioned table
Rem
COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_ID" IS 'primary key';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_FIRST_NAME" IS 'first name of the customer';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_LAST_NAME" IS 'last name of the customer';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_GENDER" IS 'gender; low cardinality attribute';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_YEAR_OF_BIRTH" IS 'customer year of birth';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_MARITAL_STATUS" IS 'customer marital status; low cardinality attribute';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_STREET_ADDRESS" IS 'customer street address';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_POSTAL_CODE" IS 'po
stal code of the customer';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_CITY" IS 'city where the customer lives';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_STATE_PROVINCE" IS 'customer geography: state or province';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."COUNTRY_ID" IS 'foreign key to the countries table (snowflake)';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_MAIN_PHONE_NUMBER" IS 'customer main phone number';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_INCOME_LEVEL" IS 'customer income level';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_CREDIT_LIMIT" IS 'customer credit limit';

COMMENT ON COLUMN "SH"."CUSTOMERS1"."CUST_EMAIL" IS 'customer email id';

COMMENT ON TABLE "SH"."CUSTOMERS1"  IS 'dimension table';

Rem
Rem Copying constraints to new partitioned table
Rem
  ALTER TABLE "SH"."CUSTOMERS1" ADD CONSTRAINT "CUSTOMERS_PK1" PRIMARY KEY ("CUST_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE"  ENABLE NOVALIDATE;

  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" NO
T NULL ENABLE);

  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_TOTAL" NOT NULL ENABLE);

  ALTER TABLE "SH"."CUSTOMERS1" MODIFY ("CUST_TOTAL_ID" NOT NULL ENABLE);

Rem
Rem Copying referential constraints to new partitioned table
Rem
  ALTER TABLE "SH"."CUSTOMERS1" ADD CONSTRAINT "CUSTOMERS_COUNTRY_FK1" FOREIGN KEY ("COUNTRY_ID")
      REFERENCES "SH"."COUNTRIES" ("COUNTRY_ID") 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 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE" ;

  CREATE BITMAP INDEX "SH"."CUSTOMERS_GENDER_BIX1" ON "SH"."CUSTOMERS1" ("CUST_GENDER")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE" LOCAL;

  CREATE BITMAP INDEX "SH"."CUSTOMERS_MARITAL_BIX1" ON "SH"."CUSTOMERS1" ("CUST_MARITAL_STATUS")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE" LOCAL;

  CREATE BITMAP INDEX "SH"."CUSTOMERS_YOB_BIX1" ON "SH"."CUSTOMERS1" ("CUST_YEAR_OF_BIRTH")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE" LOCAL;

Rem
Rem Copying object grants to new partitioned table
Rem
GRANT SELECT ON "SH"."CUSTOMERS1" TO "BI";

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 Revalidating dimensions for use with new partitioned table
Rem
ALTER DIMENSION "SH"."CUSTOMERS_DIM" COMPILE;


CREATE MATERIALIZED VIEW LOG ON
    "SH"."PRODUCTS"
    WITH ROWID, SEQUENCE("PROD_ID","SUPPLIER_ID","PROD
_LIST_PRICE")
    INCLUDING NEW VALUES;

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

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

CREATE MATERIALIZED VIEW "SYS"."MV$$_009C0000"
    REFRESH FAST WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT SH.CUSTOMERS.CUST_CREDIT_LIMIT C1, SH.CUSTOMERS.CUST_YEAR_OF_BIRTH C2,
       SH.CUSTOMERS.CUST_GENDER C3, COUNT(*) M1 FROM SH.CUSTOMERS, SH.SALES WHERE
       SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND (SH.CUSTOMERS.CUST_CREDIT_LIMIT
       <= 14962) GROUP BY SH.CUSTOMERS.CUST_CREDIT_LIMIT, SH.CUSTOMERS.CUST_YEAR_OF_BIRTH,
       SH.CUSTOMERS.CUST_GENDER;

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

CREATE MATERIALIZED VIEW "SYS"."MV$$_009C0001"
PARTITION BY RANGE ("C3") INTERVAL( NUMTOYMINTERVAL( 1, 'MONTH')) ( PARTITION
       VALUES LESS THAN (TO_DATE(' 1998-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'
       , 'NLS_CALENDAR=GREGORIAN')) )
    REFRESH FAST WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT SH.PRODUCTS.PROD_LIST_PRICE C1, SH.PRODUCTS.SUPPLIER_ID C2, SH.SALES.TIME_ID
       C3, COUNT(*) M1 FROM SH.PRODUCTS, SH.SALES WHERE SH.SALES.PROD_ID = SH.PRODUCTS.PROD_ID
       GROUP BY SH.PRODUCTS.PROD_LIST_PRICE, SH.PRODUCTS.SUPPLIER_ID, SH.SALES.TIME_ID;

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

CREATE MATERIALIZED VIEW "SYS"."MV$$_009C0002"
PARTITION BY RANGE ("C1") INTERVAL( 40) ( PARTITION VALUES LESS THAN (40) )
    REFRESH FAST WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT SH.PRODUCTS.PROD_LIST_PRICE C1, SH.PRODUCTS.SUPPLIER_ID C2, SH.SALES.CUST_ID
       C3, COUNT(*) M1 FROM SH.PRODUCTS, SH.SALES WHERE SH.SALES.PROD_ID = SH.PRODUCTS.PROD_ID
       GROUP BY SH.PRODUCTS.PROD
_LIST_PRICE, SH.PRODUCTS.SUPPLIER_ID, SH.SALES.CUST_ID;

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

CREATE MATERIALIZED VIEW "SYS"."MV$$_009C0003"
    REFRESH FAST WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT SH.PRODUCTS.PROD_LIST_PRICE C1, SH.PRODUCTS.SUPPLIER_ID C2, COUNT(*) M1
       FROM SH.PRODUCTS, SH.SALES WHERE SH.SALES.PROD_ID = SH.PRODUCTS.PROD_ID
       GROUP BY SH.PRODUCTS.PROD_LIST_PRICE, SH.PRODUCTS.SUPPLIER_ID;

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

CREATE INDEX "SYS"."MV$$_009C0002_IDX$$_009C0000"
    ON "SYS"."MV$$_009C0001"
    ("C3")
    COMPUTE STATISTICS;

CREATE INDEX "SH"."PRODUCTS_IDX$$_009C0001"
    ON "SH"."PRODUCTS"
    ("PROD_LIST_PRICE","SUPPLIER_ID","PROD_NAME")
    COMPUTE STATISTICS;

CREATE INDEX "SYS"."MV$$_009C0004_IDX$$_009C0002"
    ON "SYS"."MV$$_009C0002"
    ("C1")
    COMPUTE STATISTICS
    LOCAL;

DROP MATERIALIZED VIEW "SH"."CAL_MONTH_SALES_MV";

DROP MATERIALIZED VIEW "SH"."FWEEK_PSCAT_SALES_MV";

DROP INDEX "SH"."SALES_PROD_BIX";

DROP INDEX "SH"."SALES_CUST_BIX";

DROP INDEX "SH"."SALES_TIME_BIX";

DROP INDEX "SH"."SALES_CHANNEL_BIX";

DROP INDEX "SH"."SALES_PROMO_BIX";

DROP INDEX "SH"."PRODUCTS_PROD_SUBCAT_IX";

DROP INDEX "SH"."PRODUCTS_PROD_CAT_IX";

DROP INDEX "SH"."PRODUCTS_PROD_STATUS_BIX";

DROP INDEX "SH"."CUSTOMERS_GENDER_BIX";

DROP INDEX "SH"."CUSTOMERS_MARITAL_BIX";

DROP INDEX "SH"."CUSTOMERS_YOB_BIX";

DROP INDEX "SH"."FW_PSC_S_MV_SUBCAT_BIX";

DROP INDEX "SH"."FW_PSC_S_MV_CHAN_BIX";

DROP INDEX "SH"."FW_PSC_S_MV_PROMO_BIX";

DROP INDEX "SH"."FW_PSC_S_MV_WD_BIX";

相同的SQL Tunning Set用OLTP模板建议就会完全不一样。