提高ORACLE数据库的查询统计速度(2)

1. 设置五个回滚段的SQL语句如下：

SELECT SEGMENT_NAME FROM DBA_ROLLBACK_SEGS WHERE INITIAL_EXTENT

UPPPER(OWNER) = 'PUBLIC';

SELECT UPPER(STATUS) FROM DBA_ROLLBACK_SEGS WHERE UPPER(SEGMENT_NAME) = ''

ALTER ROLLBACK SEGMENT RB1 OFFLINE;

ALTER ROLLBACK SEGMENT RB2 OFFLINE;

ALTER ROLLBACK SEGMENT RB3 OFFLINE;

ALTER ROLLBACK SEGMENT RB4 OFFLINE;

ALTER ROLLBACK SEGMENT RB5 OFFLINE;

DROP ROLLBACK SEGMENT RB1;

DROP ROLLBACK SEGMENT RB2;

DROP ROLLBACK SEGMENT RB3;

DROP ROLLBACK SEGMENT RB4;

DROP ROLLBACK SEGMENT RB5;

CREATE PUBLIC ROLLBACK SEGMENT RB1 TABLESPACE ROLLBACK_DATA

STORAGE (INITIAL 512000 NEXT 512000 MAXEXTENTS 121);

CREATE PUBLIC ROLLBACK SEGMENT RB2 TABLESPACE ROLLBACK_DATA

STORAGE (INITIAL 512000 NEXT 512000 MAXEXTENTS 121);

CREATE PUBLIC ROLLBACK SEGMENT RB3 TABLESPACE ROLLBACK_DATA

STORAGE (INITIAL 512000 NEXT 512000 MAXEXTENTS 121);

CREATE PUBLIC ROLLBACK SEGMENT RB4 TABLESPACE ROLLBACK_DATA

STORAGE (INITIAL 512000 NEXT 512000 MAXEXTENTS 121);

CREATE PUBLIC ROLLBACK SEGMENT RB5 TABLESPACE ROLLBACK_DATA

STORAGE (INITIAL 512000 NEXT 512000 MAXEXTENTS 121);

ALTER ROLLBACK SEGMENT RB1 ONLINE;

ALTER ROLLBACK SEGMENT RB2 ONLINE;

ALTER ROLLBACK SEGMENT RB3 ONLINE;

ALTER ROLLBACK SEGMENT RB4 ONLINE;

ALTER ROLLBACK SEGMENT RB5 ONLINE;

COMMIT;

2.将数据量大的库存表等放在一簇内的SQL语句如下：

KCB='CREATE TABLE QC_KCB( '

+' CKNM NUMBER(8) ,'

+' QCNM NUMBER(10) ,'

+' CKKC NUMBER(12,2),'

+' SNCKKC NUMBER(12,2),'

+' LDJ NUMBER(12,2),'

+' BZ VARCHAR(100),'

+' PRIMARY KEY(CKNM,QCNM))'

+' TABLESPACE WXGL_DATA1 ' ;（大数据量的库存表等放在WXGL_DATA1)

QCFL = 'CREATE TABLE QC_QCFL '

+ '(FLBH NUMBER(2) PRIMARY KEY,'

+ ' FLMC VARCHAR(20) '

+ ' ) '

+' TABLESPACE WXGL_DATA2 ' ;(其他表放在WXGL_DATA2）