How to maintain the good performance of Oracle database

Author: Brent
Oracle database is favored by more and more enterprises because of its high reliability, security and compatibility. How to keep Oracle databases in good performance is a concern for many database administrators and may be considered in the following ways, based on the author's experience.
One, zoning
According to practical experience, in a large database, the vast majority of data space is occupied by a small number of tables. Partitioning is generally used to simplify the management of large databases and improve the query performance of applications. Partitioning is the separation of records in dynamic tables into a number of different table spaces, allowing data to be physically partitioned for ease of maintenance, backup, recovery, transactional, and query performance. When used, you can create a view that connects all partitions so that they logically still appear as a whole.
1. Create a partition table
Create Table Employee (
EmpNo varchar2 () 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 part3_ts
Partition PART4 values less than (MAXVALUE)
Tablespace part4_ts
);
Table employee is partitioned according to the Deptno column.
2. Partitioned 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,
);
When there are many transactions in a partition and a global index is used to ensure the uniqueness of the data records in all partitions, the global clause allows you to specify the range value of the index, which can be different from the range value of the table partition when you establish it. Only a local index is established to establish a one by one correspondence between the index partition and the table partition. Therefore, in most cases, you should use a local index partition. With this index, partitioning makes it easy to associate index partitions with table partitions, and local indexes are easier to manage than global indexes.
3. Partition Management
Depending on your needs, you can also use ALTER TABLE commands to add, delete, Exchange, move, modify, rename, divide, and truncate the structure of an existing partition.
Second, the reconstruction index
If the records in the table are frequently deleted or inserted, the amount of index space usage is increasing, even though the total number of records in the table remains unchanged. Although the record was deleted from the index, the use space for the record index entry could not be reused. Therefore, if the table changes, the amount of index space is increasing, regardless of whether the number of records in the table increases, just because the invalid space in the index increases.