--旧表改名备份
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);