oracle簇表 数据抽取,Oracle聚簇表

tacustomer有200万左右的记录数，记录长度上限(各个字段最大长度之和，如VARCHAR2(100)视作最长100个字节)约800个字节。taaccoinfo表约200万，记录长度上限为788个字节。两个表以customerid为关联字段，一个customer对应多个taaccoinfo.taaccountid(基本是1对1或1对2关系)。两表连接，若各自表上没有选择过滤条件，则执行时分别Table Full Scan。不知簇表能否提高性能(同事说簇表更新效率很低)。做了实验，结果在簇表数据插入上无法通过。

CREATE CLUSTER cust_fundacco (CUSTOMERID NUMBER(10,0))

TABLESPACE ts_ta;

CREATE TABLE "TA"."LCM_TACUSTOMER"

( "CUSTOMERID" NUMBER(10,0) NOT NULL ENABLE,

"LATENTCUSTOMERID" NUMBER(10,0),

"LOGINNAME" VARCHAR2(20),

"WEBPWD" VARCHAR2(50),

"SERVICEPWD" VARCHAR2(50),

"CUSTOMERLEVELSIGN" CHAR(1),

"CUSTOMERINTEGRAL" NUMBER(10,0),

"CUSTOMERTYPESIGN" CHAR(1),

"CUSTOMERATTRSIGN" CHAR(5),

"BILLSIGN" CHAR(1),

"WEBREGISTERDATE" DATE,

"CUSTOMERNAME" VARCHAR2(60),

"SHORTNAME" VARCHAR2(20),

"CERTIFICATETYPE" CHAR(1),

"CERTIFICATENO" VARCHAR2(30),

"SEX" CHAR(1),

"BIRTHDAY" VARCHAR2(8),

"VOCATION" CHAR(2),

"EDUCATION" CHAR(2),

"ANNUALINCOME" VARCHAR2(8),

"NATIONALITY" CHAR(3),

"MOBILETELNO" VARCHAR2(24),

"CORPTEL" VARCHAR2(22),

"TELNO" VARCHAR2(22),

"FAXNO" VARCHAR2(24),

"EMAILADDRESS" VARCHAR2(40),

"CORPNAME" VARCHAR2(40),

"CITYNO" VARCHAR2(10),

"ADDRESS" VARCHAR2(100),

"POSTCODE" CHAR(6),

"LASTUPDATEDATE" DATE,

"LASTUPDATEWAY" CHAR(1),

"BILLPERSON" VARCHAR2(60),

"BILLADDRESS" VARCHAR2(100),

"BILLPOSTCODE" CHAR(6),

"SIGNOFVALIDADDRESS" CHAR(1)

--,CONSTRAINT "PK_LCM_TACUSTOMER" PRIMARY KEY ("CUSTOMERID")

) CLUSTER cust_fundacco (CUSTOMERID)

;

CREATE TABLE "TA"."LCM_TAACCOINFO"

( "TAACCOUNTID" VARCHAR2(12) NOT NULL ENABLE,

"TAACCOUNTTYPE" VARCHAR2(2),

"INDIVIDUALORINSTITUTION" CHAR(1),

"CERTIFICATETYPE" CHAR(1),

"CERTIFICATENO" VARCHAR2(30),

"DISTRIBUTORCODE" VARCHAR2(3),

"REGIONCODE" VARCHAR2(10),

"BRANCHCODE" VARCHAR2(9),

"INVESTORNAME" VARCHAR2(60),

"SHORTNAME" VARCHAR2(12),

"NATIONALITY" CHAR(3),

"INSTREPRNAME" VARCHAR2(20),

"TRANSACTORNAME" VARCHAR2(20),

"TRANSACTORCERTTYPE" CHAR(1),

"TRANSACTORCERTNO" VARCHAR2(18),

"ADDRESS" VARCHAR2(60),

"POSTCODE" VARCHAR2(6),

"TELNO" VARCHAR2(22),

"FAXNO" VARCHAR2(24),

"EMAILADDRESS" VARCHAR2(40),

"MOBILETELNO" VARCHAR2(24),

"BPNO" VARCHAR2(20),

"INVESTORSBIRTHDAY" VARCHAR2(8),

"SEX" CHAR(1),

"EDUCATIONLEVEL" CHAR(2),

"FAMILYSCALE" NUMBER(*,0),

"VOCATIONCODE" VARCHAR2(2),

"ANNUALINCOME" VARCHAR2(8),

"CALLCENTER" CHAR(1),

"INTERNET" CHAR(1),

"SELFHELP" CHAR(1),

"DELIVERTYPE" CHAR(1),

"CONFIRMMAILTYPE" CHAR(1),

"FREEZINGDEADLINE" VARCHAR2(8),

"FROZENCAUSE" CHAR(1),

"BANKACCTNAME" VARCHAR2(60),

"BANKACCTNO" VARCHAR2(28),

"BANKCODE" VARCHAR2(9),

"ACCOUNTINITDATE" DATE,

"ACCOUNTLASTUPDATEDATE" DATE,

"ACCOUNTSTATUS" VARCHAR2(2),

"FIRSTINVESTDATE" DATE,

"ANNELTRANSTIMES" NUMBER(*,0),

"BROKER" VARCHAR2(12),

"MEMO" VARCHAR2(48),

"CORPTEL" VARCHAR2(22),

"CORPNAME" VARCHAR2(40),

"SPECIALCODE" VARCHAR2(20),

"ACTCODE" VARCHAR2(3),

"SERVICEINITPWD" VARCHAR2(40),

"INPUTTATYPE" CHAR(1),

"INPUTFILEDATE" CHAR(8),

"CUSTOMERID" NUMBER(10,0),

"C_PROVINCE" VARCHAR2(20)

--,CONSTRAINT "PK_LCM_TAACCOINFO" PRIMARY KEY ("TAACCOUNTID")

) CLUSTER cust_fundacco (CUSTOMERID);

CREATE INDEX cust_fundacco_index

ON CLUSTER cust_fundacco

TABLESPACE ts_ta

;

alter table LCM_TACUSTOMER nologging;

alter table LCM_TAACCOINFO nologging;

INSERT /*+ append */ INTO LCM_TACUSTOMER

SELECT * FROM tacustomer;

INSERT /*+ append */ INTO LCM_TAACCOINFO SELECT * FROM taaccoinfo;