Several ways to maintain the excellent performance of Oracle databases

Oracle databases are favored by more and more enterprises for their high reliability, security, and compatibility. Many database administrators are concerned about how to maintain excellent performance of Oracle databases. Based on my experience, we suggest you consider the following aspects.

I. Partitioning

Based on actual experience, the vast majority of data space in a large database is occupied by a small number of tables. To simplify the management of large databases and improve the query performance of applications, you can use partitioning. The so-called partition is the separation of records in the dynamic table to several different tablespaces, so that the data is physically separated to facilitate maintenance, backup, recovery, transaction and query performance. When used, you can create a view that connects all partitions so that it still appears logically as a whole.

1. Create a partition table

 

Table Employee partitions Based on the DeptNo column.

2. Partition Index

 

Global indexes are used when many transactions occur in a partition and the uniqueness of data records in all partitions must be ensured. When a Global index is created, the Global clause allows you to specify the index range value, this range value can be different from the range value of the table partition. Only when a local index is created will the index partition have a one-to-one correspondence with the table shard. Therefore, you should use partial index Partitioning in most cases. If this index is used, partitions can easily associate index partitions with table partitions, making local indexes easier to manage than global indexes.

 

3. Partition Management

Based on actual needs, you can also use the Alter table command to add, delete, exchange, move, modify, rename, divide, and truncated an existing partition structure.

Ii. Re-Indexing

If records in a table are frequently deleted or inserted, the index space usage increases even though the total number of records in the table remains unchanged. Although the record is deleted from the index, the space used for this record index item cannot be reused. Therefore, if the table changes, the index space increases constantly, regardless of whether the number of records in the table increases, because the invalid index space increases.

The Alter index rebuild command is required to reclaim the space used by deleted records. You can create a regular batch processing program to rebuild the index of the most active table. This batch processing program can run in idle time to avoid conflicts between the program and other applications. If you can stick to the index planning, you can recycle unused space in time to improve the space utilization.

1 2 Next Page