Shulou(Shulou.com)05/31 Report--

Editor to share with you how ORACLE creates tablespaces separately for the index. I hope you will get something after reading this article. Let's discuss it together.

The logical structure of Oracle database is composed of some database objects, such as database table space, table, index, segment, view, stored procedure, trigger and so on. The logical storage structure of the database (table space, etc.) determines how the physical space of the database is used, and database objects such as tables and indexes are distributed in each table space.

The physical structure of Oracle database, viewed from the operating system level, is composed of files, which can be physically divided into data files, log files, control files and parameter files. All the data information is stored in the data file; the log file stores the log information generated during the operation of the database, which is repeatedly overwritten and used, and the overwritten log information cannot be recovered without archiving; the control file records the key structure information of the whole database, and if it is destroyed, the whole database will not be able to work and recover. Many of the configuration parameters for the Oracle database are set in the parameter file, and this information is read when the database starts.

Optimization of logical structure

In popular words, logical structure optimization is to improve the efficiency of application by adding, decreasing or adjusting logical structure. The following is to analyze the optimization of ORACLE logical structure through the design of basic tables and the discussion of indexing and clustering.

1. Basic table extension

Database performance includes two aspects: the demand for storage space and the query response time. In order to optimize database performance, tables in the database need to be normalized. Generally speaking, the logical database design meets the requirements of the third normal form that the table structure is easy to maintain and basically meets the requirements of practical applications. Therefore, in practical application, it is generally standardized according to the standard of the third normal form, so as to ensure the consistency and integrity of the database. Designers often design too many associations between tables to reduce data redundancy as much as possible. However, in practical applications, this practice is sometimes not conducive to the optimization of system performance: for example, when the process obtains data from multiple tables, it causes a large number of join operations, and scans the whole table when part of the data is needed, which consumes the disk Ibank O and CPU time.

In order to solve this problem, some tables should be considered to be de-normalized at the same time when designing tables. There are the following methods: one is to divide tables. Split tables can be divided into horizontal split tables and vertical split tables: horizontal split divides a table into multiple tables according to rows, which can improve the query speed of each table, but select different tables when querying and updating, and summarize multiple tables when statistics, so the application will be more complex. Vertical segmentation is for a table with many columns, if some columns are accessed much more frequently than others, you can use the primary key and these columns as one table, and the primary key and other columns as another table. By reducing the width of the columns and increasing the number of rows per data page, more rows can be scanned at a time by Ipicuro, thus increasing the speed of accessing each table. However, because of the multi-table join, it should be used in fewer cases where columns in different split tables are queried or updated at the same time. The second is to retain redundant columns. When two or more tables often need to be joined in a query, a number of redundant columns can be added to one of the tables to avoid joining between tables too frequently, which is generally used when the data of redundant columns does not change frequently. The third is to increase the number of students. Derived columns are derived from the calculation of many other columns in the table. Adding derived columns can reduce statistical operations and greatly reduce the operation time when data are summarized.

Therefore, in the design of the database, data should be organized into two categories: frequently accessed data and frequently modified data. For data that is accessed frequently but not frequently modified, the internal design should not be physically standardized. For data that is frequently modified but not frequently accessed, the internal design should be physically normalized. Sometimes it is necessary to use the standardized table as the basis of the logical database design, and then physically unstandardize the data according to the needs of the whole application system. Both norms and anti-norms are constraints based on actual operation, and neither of them makes sense without practice. Only by combining the two reasonably can we complement each other and give full play to their respective advantages.

2. Indexing and clustering

Creating an index is one of the most effective ways to improve the retrieval efficiency. An index maps the logical values in a table to a secure RowID, which can quickly locate the physical address of the data, and can greatly speed up the query speed of the database. A database application system with a reasonable index may be tens of times more efficient than a database application system without an index, but it is not that the more indexes the better. Building an index on those data columns that often need to be modified will lead to the continuous reorganization of the index B* tree, resulting in a decline in system performance and a waste of storage space. For an index established by a large table, sometimes it can not improve the speed of data query, but will affect the performance of the whole database. This is mainly related to the data management mode of SGA. When Oracle manages the block cache, the index data has higher resident rights than ordinary data. When competing for space, Oracle will first move out ordinary data. When querying large indexed tables, the index data may use up all the block cache space, and Oracle has to read and write to disk frequently to obtain data, so After partitioning a large table, you can establish a partitioned index based on the corresponding partition.

Oracle provides another way to improve query speed, which is Cluster. The so-called clustering, to put it simply, is to put several tables together and store them according to certain common attributes. Clustering stores the data of multiple tables in the same Oracle block according to the common code value, and then retrieves a group of Oracle blocks to get the data of two tables at the same time, so that the Oracle blocks that need to be stored can be reduced, thus the performance of the application can be improved.

For logical structure optimization, table data and index data should also be stored in separate table spaces, using separate table spaces. Because if the table data and the index data are put together, the Icano operation of the table data and the Icano operation of the index will produce the Icano competition that affects the performance of the system and reduce the response efficiency of the system. This competition can be avoided by storing table data and index data in different tablespaces and placing the data files of the two tablespaces on different physical disks at the physical level.

Optimization of physical structure

The data of the database is finally stored on the physical disk, and to access 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 optimization of physical storage structure, it is mainly to allocate the physical storage address of logical structure reasonably. although it can not reduce the number of reading and writing to physical storage, it can make these reads and writes as parallel as possible and reduce the competition between disk reading and writing. in order to improve efficiency, we can also reduce unnecessary physical storage structure expansion through precise calculation of physical storage, so as to improve system utilization.

1. Parallel optimization of disk read and write

For the physical read and write of the database, the Oracle system itself will optimize as much as possible. For example, in the simplest table retrieval operation, if the table structure and the index on the retrieval domain are not on the same physical structure, then in the retrieval process, the index retrieval and the table retrieval are carried out in parallel.

2. Operation parallel optimization

The optimization of operation parallelism is based on the statistical results of operation statements, first of all, count the access frequency of each table and the connection frequency between tables, and allocate table space and physical disk according to the following principles according to these data. reduce disk I / O competition between system processes and user processes; separate tables that need to be connected on tablespaces / physical disks; separate tables with high-frequency access on tablespaces / physical disks Separate the table structure and index of tables that often need to be retrieved on the tablespace / physical disk.

3. Reduce the expansion of storage structure

If the database of the application system is fragile and is constantly growing or shrinking, the efficiency of such a system is reasonable in the non-dynamic change cycle, but the performance is very poor in the dynamic change cycle, which is caused by the dynamic expansion of Oracle. In the process of dynamic expansion, Oracle must, according to the requirements of storage, expand and allocate new storage space when creating rows and row changes to obtain default values, and the expansion of tables is often not the end of things, but may also lead to the growth of data files and tablespaces, which will lead to slow response of online systems. For such a system, the best way is to allocate sufficient size and appropriate growth in advance when it is built. When an object is established, it is necessary to fully calculate their size according to the application, and then define the values of objects Initial, Next and Minextents according to these data, so as to make the database reach a good balance in physical storage and dynamic growth times, so that these objects neither grow frequently nor occupy the database too much.

After reading this article, I believe you have a certain understanding of "how ORACLE creates table spaces separately for indexes". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.