Skills in Oracle Database segment Management

Welcome to the Oracle community forum and interact with 2 million technical staff in Oracle databases. segments are often used to store all the data of specific logical objects. For example, when a database table or index is created, Oracle will allocate storage space for these objects to store the information they need. Segments are composed of one or more zones. Do not check whether the segment is Or

Welcome to the Oracle community forum and interact with 2 million technical staff> enter the Oracle database, where segments are often used to store all the data of specific logical objects. For example, when a database table or index is created, Oracle will allocate storage space for these objects to store the information they need. Segments are composed of one or more zones. Do not check whether the segment is Or

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

In Oracle databases, segments are often used to store all data of specific logical objects. For example, when a database table or index is created, Oracle will allocate storage space for these objects to store the information they need. Segments are composed of one or more zones. The segment is an inconspicuous role in Oracle databases. In fact, there are still many unknown management skills hidden in it. If the database administrator can master the content, it may be much easier to work.

　　Tip 1: Perform partition segments on a large table.

Table partition segments are used to store data in a partition table. For example, if a table is divided into several partitions, each partition corresponds to a table partition segment. This setting is feasible. According to the segment management rules, a tablespace can contain one or more segments, but one segment can only be stored in one tablespace.

In practice, we often encounter this situation. For example, in the information management system of a large retail enterprise, the total annual data volume of a sales table may reach hundreds of millions, and the total data volume may reach nearly 10 Gb. If it is evenly distributed to each quarter, 2.5 GB is available. At this time, if you do not perform partition and segment management for this sales table, the sales records for this year will be stored in one table. In this case, the database performance will be greatly affected if an annual analysis is required. For example, if you want to know the changes in air conditioning sales in May and may, the database needs to scan the records of nearly 10 Gb in the entire data table. This requires the user to wait for a long time.

However, when designing a database, the database administrator can predict this situation and manage the sales table in multiple partitions. Manage the partition segments of the sales table, that is, divide the sales table into four partition segments, one partition for each quarter. In this case, to collect statistics on air-conditioning sales in March 6 and July, you only need to query 5G data, and the Data Query volume is nearly halved. Obviously, in actual work, partition segment management for large tables can greatly reduce the I/O times and effectively improve the database query performance. If the sales volume is large, you can even divide the sales table into 12 segments. One month corresponds to one segment.

Therefore, you can deploy the data of a large table to multiple different partition segments by using the partition option to manage large tables using Table segments. This management method can reduce the number of I/O (input/output) reads and ultimately improve I/O performance. This is a common method used by database optimization experts to improve database performance. Especially in the information management system of the retail industry, it is often used.

　　Tip 2: properly use indexes to organize table segments.

In the Oracle database, there are three index-related table segments: the index segment, the index partition segment, and the index organization table segment.

Index segments are mainly used to store index data. When the database administrator creates an index, the database system automatically creates an index segment with the same name as the index. When managing index segments, there is a basic principle that an index segment can only be stored in one tablespace.

The index partition is used to store the data of a partition index. He is similar to the table partition section mentioned above. Each partition corresponds to an index partition segment. You can use the partition option to deploy the data of a large index to several different partition segments to reduce the number of inputs and outputs and improve the database performance. However, in terms of management, it has one more restriction than the table partition segment, that is, the index partition can only be created on the partition table, but not on the common table. Other management methods are similar to partition table segments, so we will not repeat them.

The index organization table organizes table data in the B structure. The corresponding segment is the index organization table segment. The management method is similar to that of index segments. When the database administrator creates an index, the segment is automatically created to save relevant data.

There is now a challenge for database administrators. When can I use index segments, when can I use indexes to organize table segments, and when can I use index partition segments? Therefore, the author provides a suggestion for your reference.

Under normal circumstances, if the data in a table often needs to be retrieved by referencing the primary key column, you can consider creating an index organization table, that is, managing it through the index organization segment. If you create an index to organize table segments, the data of the table and table primary key indexes will be stored in the index segments. This improves the query speed. In normal tables, the table and index data are stored in the table segment and index segment respectively. This is the essential difference between the two. In addition, if the database administrator creates an index to organize a table, the database developer must define the primary key column. Otherwise, it will end with a failure.

If the index is large, you can deploy the big index data into several different segments, which can effectively improve the database performance.

[1] [2]