|
SYSTEM分区:
- CREATE TABLE hr.systab (c1 integer, c2 integer)
- PARTITION BY SYSTEM
- (
- PARTITION p1 TABLESPACE users,
- PARTITION p2 TABLESPACE example,
- PARTITION p3 TABLESPACE users,
- PARTITION p4 TABLESPACE example
- );
- select * from dba_part_tables pt
- where pt.owner='HR' and pt.table_name='SYSTAB';
- select * from dba_tab_partitions tp
- where tp.table_owner='HR' and tp.table_name='SYSTAB';
-
- INSERT INTO hr.systab VALUES (4,5);会报错
复制代码
- INSERT INTO hr.systab PARTITION (p1) VALUES (4,5);
- commit;
-
- select * from hr.systab partition ( p1);
-
复制代码
----------------虚拟列表:
- create table hr.t5006_a ( a number ,
- b as (a*2 ) );
-
- select * from dba_tab_columns tc
- where tc.OWNER='HR' and tc.TABLE_NAME='T5006_A';
-
- insert into hr.T5006_A (a) values (1 );
- commit;
-
- begin
- for i in 2..300
- loop
- insert into hr.T5006_A (a) values (i );
- end loop;
- commit;
- end;
-
- begin
- dbms_stats.gather_table_stats(ownname => 'HR',
- tabname => 'T5006_A',
- method_opt => 'for columns b size 254 for all columns size auto');
- end;
-
- select * from dba_tab_col_statistics tcs
- where tcs.owner='HR' and tcs.table_name='T5006_A';
-
- select * from hr.t5006_a;
-
- update hr.t5006_a set b=4 where a=1;会报错
-
-
-
复制代码
- create table hr.t5006_b ( a varchar2(10) ,
- b as (a||'X' ) )
- partition by list (b)
- (
- partition p1 values ('AX','BX'),
- partition p2 values ('CX')
- );
-
- insert into hr.t5006_b (a ) values ('A');
- commit;
- select * from hr.t5006_b partition (p1);
复制代码 语法上可以不写virtual:
- create table hr.t5006_c ( a number ,
- b as (a*2 ) virtual );
复制代码 --------------------------- 引用分区,要特别注意外键非空:
- create table hr.t5006_parent ( a number primary key , b varchar2(20) )
- partition by range ( a )
- (
- partition p1 values less than(100),
- partition p2 values less than (maxvalue)
- );
- create table hr.t5006_son ( a number constraint fk_t5006 references hr.t5006_parent ,
- c date )
- partition by reference (fk_t5006); 会报错:
复制代码
- create table hr.t5006_son ( a number not null constraint fk_t5006 references hr.t5006_parent ,
- c date )
- partition by reference (fk_t5006);
复制代码
------------------------引用分区的自动维护:
- create table hr.t5006_parent ( a number primary key , b varchar2(20) )
- partition by range ( a )
- (
- partition p1 values less than(100),
- partition p2 values less than (maxvalue)
- );
- create table hr.t5006_son ( a number not null constraint fk_t5006 references hr.t5006_parent ,
- c date )
- partition by reference (fk_t5006);
- ----------------------------------------
- select table_name, partition_name, high_value
- from dba_tab_partitions
- where table_name in ('T5006_PARENT','T5006_SON')
- order by partition_position, table_name;
- ----------------------------------------
- insert into hr.t5006_parent values ( 99,'A99' ) ;
- commit;
- insert into hr.t5006_son values (99,sysdate );
- commit;
- select * from hr.t5006_son partition (p2) ;
- -----------------------------------------
- alter table hr.t5006_parent split partition p1 at ( 50) into
- ( partition p11 , partition p12);
- ------------------------------------------
- select table_name, partition_name, high_value
- from dba_tab_partitions
- where table_name in ('T5006_PARENT','T5006_SON')
- order by partition_position, table_name;
- -----------------------------------------------------------------------
- alter table hr.t5006_parent merge partitions p11, p12
- into partition p1;
- -------------------------------------------------
- select table_name, partition_name, high_value
- from dba_tab_partitions
- where table_name in ('T5006_PARENT','T5006_SON')
- order by partition_position, table_name;
复制代码
---------------------分区键并不是主外键(书上的例子):
- create table hr.t5006_p ( a number primary key , b date )
- partition by range ( b )
- (
- partition p1 values less than( to_date ('2000-01-01','YYYY-MM-DD' )),
- partition p2 values less than ( to_date ('2010-01-01','YYYY-MM-DD') )
- );
- create table hr.t5006_s ( a number not null constraint fk_t5006_s references hr.t5006_p ,
- b date )
- partition by reference (fk_t5006_s);
- insert into hr.t5006_p values ( 1, to_date ('2009-01-01','YYYY-MM-DD') );
- commit;
- insert into hr.t5006_s values ( 1, to_date ('1999-01-01','YYYY-MM-DD') );
- commit;
- select table_name, partition_name, high_value
- from dba_tab_partitions
- where table_name in ('T5006_P','T5006_S')
- order by partition_position, table_name;
- select * from hr.t5006_p partition (p2) ;
- select * from hr.t5006_s partition (p2);
- select * from hr.t5006_s;
- select * from hr.t5006_s partition (p2);
复制代码
以上这种情况,son表的分区范围(date)与 parent表是不一样的,son表的分区分布:
主键(1)在p2,子键(1)就在子表自己的p2上。
--------------------------------------复合分区
有办法写temple的语法:
- CREATE TABLE hr.sales ( prod_id NUMBER(6) NOT NULL,
- cust_id NUMBER NOT NULL,
- time_id DATE NOT NULL,
- channel_id char(1) NOT NULL,
- promo_id NUMBER (6) NOT NULL,
- quantity_sold NUMBER(3) NOT NULL,
- amount_sold NUMBER(10,2) NOT NULL )
- PARTITION BY RANGE (time_id)
- SUBPARTITION BY RANGE (cust_id)
- SUBPARTITION TEMPLATE
- ( SUBPARTITION sp1 VALUES LESS THAN (50000),
- SUBPARTITION sp2 VALUES LESS THAN (100000),
- SUBPARTITION sp3 VALUES LESS THAN (150000),
- SUBPARTITION sp4 VALUES LESS THAN (MAXVALUE) )
- (
- PARTITION p1 VALUES LESS THAN (TO_DATE('1-4-2007','DD-MM-YYYY')),
- PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2007','DD-MM-YYYY')),
- PARTITION p3 VALUES LESS THAN (TO_DATE('1-8-2007','DD-MM-YYYY')),
- PARTITION p4 VALUES LESS THAN (TO_DATE('1-1-2008','DD-MM-YYYY'))
- );
- select * from dba_tab_partitions tp
- where tp.table_owner='HR' and tp.table_name='SALES';
- select * from dba_tab_subpartitions tp
- where tp.table_owner='HR' and tp.table_name='SALES';
复制代码
没办法写template的语法:
- create table shipments
- ( order_id number not null
- , order_date date not null
- , ship_date date not null
- , customer_id number not null
- , sales_amount number not null
- )
- partition by range (order_date)
- subpartition by range (ship_date)
- ( partition p_2006_jul values
- less than (to_date('01-AUG-2006','dd-MON-yyyy'))
- ( subpartition p_2006_jul_early values
- less than (to_date('15-AUG-2006','dd-MON-yyyy'))
- , subpartition p_2006_jul_agreed values
- less than (to_date('01-SEP-2006','dd-MON-yyyy'))
- , subpartition p_2006_jul_late values less than (maxvalue)
- )
- , partition p_2006_aug values
- less than (to_date('01-SEP-2006','dd-MON-yyyy'))
- ( subpartition p_2006_aug_early values
- less than (to_date('15-SEP-2006','dd-MON-yyyy'))
- , subpartition p_2006_aug_agreed values
- less than (to_date('01-OCT-2006','dd-MON-yyyy'))
- , subpartition p_2006_aug_late values less than (maxvalue)
- )
- , partition p_2006_sep values
- less than (to_date('01-OCT-2006','dd-MON-yyyy'))
- ( subpartition p_2006_sep_early values
- less than (to_date('15-OCT-2006','dd-MON-yyyy'))
- , subpartition p_2006_sep_agreed values
- less than (to_date('01-NOV-2006','dd-MON-yyyy'))
- , subpartition p_2006_sep_late values less than (maxvalue)
- )
- , partition p_2006_oct values
- less than (to_date('01-NOV-2006','dd-MON-yyyy'))
- ( subpartition p_2006_oct_early values
- less than (to_date('15-NOV-2006','dd-MON-yyyy'))
- , subpartition p_2006_oct_agreed values
- less than (to_date('01-DEC-2006','dd-MON-yyyy'))
- , subpartition p_2006_oct_late values less than (maxvalue)
- )
- , partition p_2006_nov values
- less than (to_date('01-DEC-2006','dd-MON-yyyy'))
- ( subpartition p_2006_nov_early values
- less than (to_date('15-DEC-2006','dd-MON-yyyy'))
- , subpartition p_2006_nov_agreed values
- less than (to_date('01-JAN-2007','dd-MON-yyyy'))
- , subpartition p_2006_nov_late values less than (maxvalue)
- )
- , partition p_2006_dec values
- less than (to_date('01-JAN-2007','dd-MON-yyyy'))
- ( subpartition p_2006_dec_early values
- less than (to_date('15-JAN-2007','dd-MON-yyyy'))
- , subpartition p_2006_dec_agreed values
- less than (to_date('01-FEB-2007','dd-MON-yyyy'))
- , subpartition p_2006_dec_late values less than (maxvalue)
- )
- )
复制代码
制造SAA新模板:
- variable l_task_id number;
- variable l_name varchar2(30);
- Rem make a new template to have a minimum of 100 rows only for partitioning
- begin
- :l_name := 'SQLACCESS_PARTITION_SMALL';
- end;
- /
- begin
- dbms_advisor.update_task_attributes(:l_name,null,null,'FALSE');
- dbms_advisor.delete_task(:l_name);
- end;
- /
- begin
- dbms_advisor.create_task(dbms_advisor.sqlaccess_advisor,:l_task_id,:l_name,'For demo purposes only');
- dbms_advisor.set_task_parameter(:l_name,'_PA_MIN_TABLE_SIZE_PART',100);
- dbms_advisor.set_task_parameter(:l_name,'EXECUTION_TYPE','FULL');
- dbms_advisor.set_task_parameter(:l_name,'ANALYSIS_SCOPE','ALL');
- dbms_advisor.set_task_parameter(:l_name,'MODE','COMPREHENSIVE');
- dbms_advisor.update_task_attributes(:l_name,null,null,'TRUE','TRUE');
- end;
- /
复制代码

以下是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模板建议就会完全不一样。
|
|