Ticket #1: itmsplit.sql

--ŸÉ±ížÄÃû±ž·Ý
alter table ENT_PRODUCT rename to ENT_PRODUCT_BAK20110428;

--œšÐ±í
  CREATE TABLE ENT_PRODUCT 
   (    ID NUMBER NOT NULL ENABLE, 
        TYPE_ID NUMBER, 
        USER_ID NUMBER, 
        PRODUCT_ID NUMBER, 
        NAME VARCHAR2(100 BYTE) NOT NULL ENABLE, 
        CREATION_DATE DATE, 
        TYPE NUMBER, 
        SMALL_IMG VARCHAR2(255 BYTE), 
        PRODUCT_INTRO VARCHAR2(4000 BYTE), 
        RETAIL_VALID_DATE DATE, 
        VALIDATION CHAR(1 BYTE), 
        REF_PRICE NUMBER(10,2), 
        CHANGE_RATE NUMBER(10,2), 
        ADUIT_BY VARCHAR2(50 BYTE), 
        ADUIT_DATE DATE, 
        VERSION NUMBER DEFAULT 1, 
        PROVINCE VARCHAR2(20 BYTE), 
        CITY VARCHAR2(20 BYTE), 
        DISTRICT VARCHAR2(20 BYTE), 
        TITLE VARCHAR2(100 BYTE), 
        IMG VARCHAR2(100 BYTE), 
        BRAND_NAME VARCHAR2(50 BYTE), 
        EDITOR_RECOMMEND NUMBER DEFAULT 0, 
        EDITOR_SMALL_RECOMMEND NUMBER DEFAULT 0, 
         CONSTRAINT PK_ENT_PRODUCT PRIMARY KEY (ID)
   );


  CREATE TABLE ENT_PRODUCT_ITEM 
   (    COMPANY_PRODUCT_ID NUMBER NOT NULL ENABLE, 
        STATUS NUMBER, 
        PRODUCT_ORDER NUMBER DEFAULT 999, 
        HAVE_PRODUCT CHAR(1 BYTE), 
        RETAIL_PRICE NUMBER(10,2) DEFAULT 0, 
        PRICE_STATUS NUMBER DEFAULT 0, 
        ONLINE_FLAG NUMBER DEFAULT 1, 
        WARRANTY VARCHAR2(500 BYTE), 
        SELL_STATUS NUMBER DEFAULT 1, 
        COUNT NUMBER, 
        START_DATE DATE, 
        END_DATE DATE, 
        PAYMENT VARCHAR2(30 BYTE) DEFAULT 'Ö§ž¶±Š', 
        ADD_COUNT NUMBER DEFAULT 0, 
        LAST_UPDATE_BY VARCHAR2(50 BYTE), 
        FIRST_ON_SELL_TIME DATE, 
        SOLD_COUNT NUMBER DEFAULT 0, 
        LAST_RETAIL_PRICE NUMBER(10,2) DEFAULT 0, 
        LAST_UPDATE_DATE DATE, 
        VERSION NUMBER DEFAULT 1, 
        PRODUCT_ID NUMBER, 
        USER_ID NUMBER, 
         CONSTRAINT PK_ENT_PRODUCT_ITEM PRIMARY KEY (COMPANY_PRODUCT_ID)
  );


--disableбíÖ÷ŒüÔŒÊø 
alter table ENT_PRODUCT disable constraint PK_ENT_PRODUCT;
alter table ENT_PRODUCT_ITEM disable constraint PK_ENT_PRODUCT_ITEM;

--57s
INSERT INTO ENT_PRODUCT
SELECT ID,TYPE_ID,USER_ID,PRODUCT_ID,NAME,CREATION_DATE,TYPE, 
SMALL_IMG,PRODUCT_INTRO,RETAIL_VALID_DATE,VALIDATION,REF_PRICE, 
CHANGE_RATE,ADUIT_BY,ADUIT_DATE,VERSION,PROVINCE,CITY,DISTRICT, 
TITLE,IMG,BRAND_NAME,EDITOR_RECOMMEND,EDITOR_SMALL_RECOMMEND
FROM ENT_PRODUCT_BAK20110428;

--56s
INSERT INTO ENT_PRODUCT_ITEM
SELECT ID,STATUS,PRODUCT_ORDER,HAVE_PRODUCT,RETAIL_PRICE,PRICE_STATUS, 
ONLINE_FLAG,WARRANTY,SELL_STATUS,COUNT,START_DATE,END_DATE,PAYMENT, 
ADD_COUNT,LAST_UPDATE_BY,FIRST_ON_SELL_TIME,SOLD_COUNT,LAST_RETAIL_PRICE, 
LAST_UPDATE_DATE,VERSION,PRODUCT_ID,USER_ID
FROM ENT_PRODUCT_BAK20110428;

--enableбíÖ÷ŒüÔŒÊø
--147s
alter table ENT_PRODUCT enable constraint PK_ENT_PRODUCT;
alter table ENT_PRODUCT_ITEM enable constraint PK_ENT_PRODUCT_ITEM;
 
--87s
  CREATE INDEX IDX_ENT_PRODUCT_0428_USER_ID ON ENT_PRODUCT (USER_ID);
--71s
  CREATE INDEX IDX_ENT_PRODUCT_0428_1 ON ENT_PRODUCT (PRODUCT_ID, USER_ID);
--79s
  CREATE INDEX IDX_ENT_PRODUCT_0428_TYPE_ID ON ENT_PRODUCT (TYPE_ID);
--92s
  CREATE INDEX IDX_ENT_PRODUCT_ITEM_IND_LUD ON ENT_PRODUCT_ITEM (LAST_UPDATE_DATE);
--149s
  CREATE INDEX IDX_ENT_PRODUCT_ITEM_1 ON ENT_PRODUCT_ITEM (START_DATE, END_DATE, SELL_STATUS);

  CREATE INDEX IDX_ENT_PRODUCT_ITEM_USER_ID ON ENT_PRODUCT_ITEM (USER_ID);

  CREATE INDEX IDX_ENT_PRODUCT_ITEM_2 ON ENT_PRODUCT_ITEM (PRODUCT_ID, USER_ID);