提高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）