Table data and index data separate table Space storage benefits

logical and physical structure of Oracle database

The logical structure of an Oracle database is made up of database objects, such as database table spaces, tables, indexes, segments, views, stored procedures, triggers, and so on. The logical storage structure of a database (table space, etc.) determines how the physical space of a database is used, and database objects such as tables, indexes, etc. are distributed in each table space.

The physical structure of an Oracle database is viewed from the operating system level and consists of a file that is physically divided into: data files, log files, control files, and parameter files. The data file holds all the data information, the log file holds the log information generated during the database running, it is repeatedly overwritten, and if it is not archived, the log information that has been overwritten will not be recovered; the control file records the key structure information of the whole database, if it is destroyed, The entire database will not work and recover; The parameters file has many configuration parameters for the Oracle database that are read when the database is started.

　　optimization of logical Structure

Logical structure optimization in layman's terms, by adding, reducing, or adjusting the logical structure to improve the efficiency of the application, the following paper analyzes the optimization of the Oracle logical structure by the discussion of the design of the basic table and the index and cluster.

1. Basic table Extension

Database performance includes the size of storage space requirements and the length of query response time two. To optimize database performance, the tables in the database need to be normalized. In general, the logical database design satisfies the third normal form the table structure to be easy to maintain and basically satisfies the actual application request. Therefore, the actual application is generally standardized according to the standard of the third paradigm, so as to ensure the consistency and integrity of the database, designers often design too much inter-table association, in order to reduce data redundancy as much as possible. In practice, however, this practice is sometimes detrimental to the performance of the system: when a process fetches data from multiple tables, it causes a large number of connection operations, scans the entire table when part of the data is needed, and consumes the disk's I/O and CPU time.

In order to solve this problem, the table should be designed to consider the anti-normalization of some tables, the method has the following: first, the partition table. Split table can be divided into horizontal split table and vertical split table two: Horizontal segmentation is the row of a table into more than one table, which can improve the query speed of each table, but the query, update to select a different table, statistics to summarize multiple tables, so the application is more complex. Vertical partitioning is a table with many columns, and if some columns are accessed much more frequently than other columns, you can use the primary key and the columns as one table, and the primary key and other columns as another table. By reducing the column width, you increase the number of rows per data page, and I/O can scan more rows at a time, increasing the speed of accessing each table. However, due to the multi-table connection, it should be used when querying or updating columns in different partitioned tables in a relatively small number of cases. The second is to keep the redundant columns. When two or more tables often require a connection in a query, you can add a number of redundant columns on one of the tables to avoid too frequent connections between tables, generally in cases where data from redundant columns is not constantly changing. The third is to increase the derived column. Derived columns are calculated from the other columns in the table, and increasing the derived columns can reduce statistical operations, which can greatly shorten the calculation time when the data is summarized.

Therefore, in the design of a database, 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 denormalized. For data that is frequently modified but not frequently accessed, the internal design should be physically normalized. It is sometimes necessary to use a normalized table as the basis for the logical database design, and then to physically denormalized the data based on the needs of the entire application system. Both norms and counter-norms are based on the actual operation of the constraints, out of the actual two are meaningless. Only by combining the two rationally can we supplement each other and give full play to the merits of each.

2. Index and Cluster

Index is one of the most effective methods to improve the efficiency of retrieval, the index maps the logical values in the table to the secure ROWID, can quickly locate the physical address of the data, can greatly accelerate the query speed of the database, a database application system with reasonable index may be dozens of times times more efficient than a database application system without indexing. , but not as many indexes as possible, indexing on data columns that often need to be modified will result in the continuous reorganization of the index b* tree, resulting in degraded system performance and wasted storage space. Indexes built on a large table sometimes do not improve the data query speed, but can affect the performance of the whole database. This is mainly related to the data management of the SGA, Oracle in the data Block cache management, the index data than the normal data has a higher residency permissions, in the space competition, Oracle will first move out of the ordinary data, the large table with the index of the data query, The index data may run out of all the block cache space, and Oracle will have to read and write disk frequently to get the data, so after partitioning a large table, you can create a partitioned index on the appropriate partition.

Oracle provides another way to increase query speed, which is clustering (Cluster). The so-called clustering, simply put a few tables together, according to a certain public properties mixed storage. Clustering stores data from multiple tables in the same Oracle block based on common code values, retrieving a set of Oracle blocks to obtain data from two tables at the same time, thus reducing the number of Oracle blocks that need to be stored, thereby improving application performance.

For the optimization of the logical structure, the table and index data should also be stored separately from the table space, using separate table spaces respectively. Because if table data and index data are put together, I/O operations of table data and index I/O operations will result in I/O competition affecting system performance, reducing the system's response efficiency. This competition can be avoided by storing table and index data in separate tablespaces and placing the data files of the two tablespaces on separate physical disks at the physical level.

　　optimization of physical structure

Database data is ultimately stored on a physical disk, access to the data is to read and write to these physical disks, so the optimization of these physical storage is an important part of system optimization. For the physical storage structure optimization, the main reason is to rationally allocate the physical storage address of the logical structure, so that it can not reduce the number of read and write to the physical storage, but to make these read and write as parallel as possible, reduce the disk read and write competition, thereby improving the efficiency, but also by the physical storage to reduce the unnecessary physical storage structure expansion , thus improving system utilization.

1, disk read and write parallel optimization

For the physical reading and writing of the database, the Oracle system itself will be optimized as much as possible, for example, in one of the simplest table retrieval operations, if the index on the table structure and the retrieval domain is not on a physical structure, the retrieval of the index and the retrieval of the table are carried out in parallel during the retrieval process.

2. Operation Parallel optimization

Operation parallel optimization is based on the statistical results of the operation statement, the first is to count the frequency of access to the tables, the connection frequency between the tables, according to the following principles to allocate table space and physical disk, reduce the system process and user process disk I/O competition; the tables that need to be connected are separated from the table space/physical disk; The table of the high frequency access is separated from the tablespace/physical disk, and the table structure and index of the table that is often needed to be retrieved are separated on the tablespace/physical disk.

3. Reduce storage structure expansion

If the database of the application system is weak and growing or shrinking, such a system is efficient during the non-dynamic change cycle, but the performance is poor during the dynamic change cycle due to the dynamic expansion of Oracle. In the process of dynamic expansion, Oracle must expand and allocate new storage space when creating rows, row changes, and the need to acquire default values as the storage requirements, and the extension of the table is often not the end of the matter, and may result in the growth of data files and tablespaces, which can cause the online system to react slowly. For such a system, the best way is to pre-allocate enough size and appropriate growth at the time of establishment. When an object is established, it is sufficient to calculate their size according to the application, then define the values of objects initial, next and minextents based on the data, so that the database can achieve a good balance between physical storage and dynamic growth times, so that these objects do not grow frequently. Also does not occupy the database too much.

　　Conclusion

Optimizing the Oracle database is very important to improve the usability and efficiency of the computer system, especially in the Oracle database design and development phase, the logical structure and the physical structure of the effective optimization design, the creation of a reasonable layout of the database, you can get the minimum system overhead, Can fundamentally improve the overall performance of the application system, for future database Performance tuning and utilization have great benefits.