Tips for keeping Oracle databases in good performance

Today, Oracle databases are favored by more and more enterprises because of their high reliability, security and compatibility. The reader may wish to consider some of the following aspects of how to keep the Oracle database permanently in good performance.

Partition
According to practical experience, in a large database, the vast majority of database space is occupied by a small number of tables. How to simplify the large database and management, how to improve the application of query performance, can generally use partitioning this method. Partitioning is the dynamic separation of records from a table into a number of different table spaces, making the data physically fragmented to facilitate 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, the establishment of the 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 values 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, the 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,
);
Use a global index when there are many transactions in the partition and to ensure the uniqueness of data records in all partitions, such as:
Create index Employee_deptno on Employee (DeptNo)
Global partition by range (DeptNo)
(
Partition PART1 values less than (11)
Tablespace Part1_ndx_ts,
Partition PART2 values less than (21)
Tablespace Part2_ndx_ts,
Partition PART3 values less than (31)
Tablespace part3_ndx_ts
Partition PART4 values less than (MAXVALUE)
Tablespace part4_ndx_ts
);
When a global index is established, 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. 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.