Anatomy of Oracle Logical Structure Optimization

Oracle Logical Structure Optimization improves the efficiency of its applications by increasing, decreasing, or adjusting logical structures, the following describes how to optimize the logical structure of Oracle through the design of its basic tables, the discussion of indexes and clustering. The following describes the main content of the article.

Basic table Extension

Database performance includes the size of the storage space required and the length of query response time. To optimize the database performance, you need to standardize the tables in the database. In general, the logical database design meets the third paradigm. The table structure is easy to maintain and basically meets the requirements of practical applications.

Therefore, in practical applications, the standards of the third paradigm are generally normalized to ensure the consistency and integrity of the Oracle logical structure database. Designers often design too many table associations, to minimize data redundancy. However, in practical applications, this approach is sometimes not conducive to the optimization of system operation performance. For example, a large number of connection operations are triggered when the process obtains data from multiple tables, and the entire table needs to be scanned when part of the data is required, this consumes disk I/O and CPU time.

To solve this problem, we should also consider anti-normalization of some tables when designing tables. There are several methods: first, table segmentation. Split tables can be divided into horizontal split tables and Vertical Split tables: horizontal split is divided into multiple tables according to the rows in a table, which can improve the query speed of each table, however, you need to select different tables for query and update, and multiple tables should be summarized during statistics, so the application will be 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 (similarly, memory-based page access ), this increases the access speed to 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. The second is to 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, it is generally used when the data in the redundant column is not changed frequently.

Third, 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. Is it better to reduce the calculation time through periodic carry-over during data aggregation ?).

Therefore, in the database design, data should be organized in two categories: frequently accessed data and frequently modified data. For data that is frequently accessed but not frequently modified, the internal design should be physically nonstandard. For data that is frequently modified but infrequently accessed, internal design should be physically normalized.

Sometimes it is necessary to use a standardized table as the basis for the design of the Oracle logical structure database, and then physically non-standardized data based on the needs of the entire application system. Both standards and anti-standards are constraints based on actual operations, and they are meaningless. Only by reasonably combining the two can they complement each other and give full play to their respective advantages.

Article by: http://www.programbbs.com/doc/3414.htm