In large database systems, the query statistics are often used, but for systems with large data volume, users often feel very slow when performing complex query statistics, and cannot meet application requirements, which requires us to make reasonable settings when designing database systems. Increase the speed of query statistics. This article combines the author's project development experience, explains the specific setup method. Taking the Oracle7.33 database system as an example, we combine the characteristics of the project when developing a large Oracle database system, in accordance with the security and efficiency principles, the database has been physically designed to greatly improve the query statistics of the database. Summarize as follows: 1) Expand data table space to 500m, used to store data of this system; 2) The initial size of the section is 10K, the growth rate is 10K, the growth rate is 1; 3) User temporary space increase Large 40m; 4) System temporary table space and rollback segment table space increased by 40M, and newly built four backgrous; 5) Need to join inquiry, and the amount of data is large, the list, the list, the send and receive sheet In a cluster; 6) Provide timed backups, the backup file is placed on another machine. Setting the SQL statement of the data table space is as follows: create tablespace wxgl_data1 datafile 'wxgl_data1 datafile' wxgl_data1.ora 'size 500m online; adds the SQL statement of the system temporary table space and the split segment table space as follows: ALTER TABLESPACE TEMPORY_DATA Add DataFile' tmp2orcl.ora 'size 40M ; ALTER TABLESPACE ROLLBACK_DATA ADD DATAFILE 'RBS2ORCL.ORA' SIZE 40M; data space in the data file specified set of SQL statements as follows: CREATE USER ZBGL IDENTIFIED BY ZBGL; GRANT DBA tO ZBGL; ALTER USER ZBGL DEFAULT TABLESPACE WXGL_DATA1 TEMPORARY TABLESPACE TEMPORARY_DATA;
1. Set five rollback following SQL statement: 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 512 000 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. Put the inventory of the data in a cluster in a cluster as follows: 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 )) ' '
