The Oracle database is favored by its high reliability, security, compatibility and more and more companies. How to keep the Oracle database excellent performance, this is a question of many database administrators concerned. According to the author's experience, you may wish to consider the following aspects. First, according to the actual experience, most of the data space is occupied by a small amount in a large database. In order to simplify the management of large databases, improve the application's query performance, can generally use this means of partition. The so-called partition is the record in a dynamic table to a number of different tablespaces, making the data physically split, facilitating maintenance, backup, recovery, transaction and query performance. A view that connects all partitions can be created when used, making it logically still appearing in one whole. 1. Establish the partition table Create table Employee (EmpNo varchar2 (10) primary key, Name varchar2 (30), DeptNo Number (2)) Partition by range (DeptNo) (partition PART1 values less than (11) tablespace PART1_TS, partition PART2 values less than (21) TABLESPACE Part2_TS, Partition Part3 Valuse Less Than (31) TableSpace Part 3_TSPartition Part4 Values Less Than (MaxValue) TableSpace Part4_ts); Table Employee is partitioned in accordance with the DEPTNO column. 2. Partition index Create index Employee_DeptNo on Employee (DeptNo) local (partition PART1 tablespace PART1_NDX_TS, partition PART2 tablespace PART2_NDX_TS, partition PART3 tablespace PART3_NDX_TS, partition PART4 tablespace PART4_NDX_TS,); there were many affairs when the partition and to ensure that the data records of all partitions of The global index is used when the global index is established, and the Global clause allows the specified range value to be specified, which can be different from the range of the table partition. Only establishing local indexing will enable the index partition to establish a correspondence between the table partition. Therefore, in most cases, a local index partition should be used. If this index is used, the partition can easily establish an index partition with the table partition, and the local index is easier to manage than the global index. 3. The partition management can also use the Alter Table command to increase, delete, exchange, move, modify, rename, and cut, truncated a structure that exists. Second, reconstruction index If the record is frequently deleted or inserted, although the total record in the table remains unchanged, the amount of index space is increasing. Although the record is deleted from the index, the usage space of the record index cannot be reused. Therefore, if the table changes, the index space is increasing, regardless of whether the number of records increases in the table, only because the invalid space in the index increases. To recover spaces that have been used to be removed, you need to use the alter index rebuild command. You can make a regular running batch program to rebuild the index of the most active table. This batch program can be run when you are idle to avoid conflicts with other application. If you can adhere to the program plan of the index, you can recover those unused spaces and improve spatial utilization. Third, the fragmentation of segments When generating a database object (a table or an index), the table space is specified by the user default or specified value. A segment generated in the table space for storing the information of the object.
