Oracle Database performance optimization Policy Selection

The optimization of Oracle database performance is a problem that every DBA should always consider. The factors that affect Oracle database performance optimization are described in detail for your reference.

Database Design is the foundation of application design. Its performance directly affects the performance of applications. Database performance includes the size of the storage space required and the length of query response time. To optimize the performance of the Oracle database, you need to standardize the tables in the database. The normalization paradigm can be divided into the first paradigm, the second paradigm, the third paradigm, the BCNF paradigm, the fourth paradigm, and the fifth paradigm. Generally, the logical database design meets the first three standards of standardization, but the table structure meeting the third paradigm is easy to maintain and basically meets the requirements of practical applications. Therefore, in practical application, it is generally normalized according to the standard of the third paradigm. However, normalization also has disadvantages: because a table is split into multiple tables, multiple table connections are required during query, reducing the query speed.

Due to the slow query speed caused by normalization, some applications need to respond quickly. Some tables should be de-normalized when designing tables. The following methods can be used for anti-Normalization:

1. Split table

Split tables include horizontal and vertical partitions.

Horizontal segmentation divides a table into multiple tables by row, which improves the query speed of each table. However, when querying and updating a table, you must select different tables. Multiple tables must be summarized during statistics, therefore, applications are more complex.

Vertical segmentation is used for tables with many columns. If some columns are accessed more frequently than other columns, the primary key and these columns can be used as a table, use the primary key and other columns as another table. By reducing the column width and increasing the number of rows on each data page, I/O can scan more rows at a time, thus improving the speed of accessing each table. However, due to the multi-Table connection, it is recommended that you query or update columns in different split tables at the same time.

2. Retain redundant Columns

When two or more tables need to be connected frequently in queries, you can add redundant columns to one of the tables to avoid too frequent connections between tables. Since the update operation on redundant columns must synchronize multiple tables, it is generally used when the data of the redundant columns is not changed frequently.

3. Add a derived Column

A derived column is calculated by multiple other columns in the table. Adding a derived column can reduce the statistical operation and greatly shorten the operation time during data aggregation.

Measure the test taker's knowledge about the Oracle file system mechanism.

Explain how to modify the table structure in Oracle

The section of the Oracle database

How Does oracle Automatically Start Multiple Data instances?

Oracle operating system authentication and password file Authentication