提高ORACLE数据库的查询统计速度(2)
1. 设置五个回滚段的SQL语句如下：
SELECT SEGMENT_NAME FROM DBA_ROLLBACK_SEGS WHERE INITIAL_EXTENT < 512000 AND
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）
|
上海徐家汇 漕溪北路38号11楼A室 
+0086-21-61121 021 
+0086-21-64877858
w71365@gmail.com