Several methods to optimize the design of Oracle Database and table

The ORACLE tutorial is: several methods to optimize the design of Oracle database tables.

Preface

Most Oracle Database performance problems are caused by unreasonable Database design. Only a small number of problems are rooted in the unreasonable configuration of memory modules such as Database Buffer, Share Pool, and Redo Log Buffer, and I/O contention, CPU contention and other DBA duties. Therefore, unless we are faced with a system that has been completed and cannot be changed, we should not focus too much on memory, I/O, CPU and other performance adjustment projects, however, we should pay attention to the rationality of the database table design. The rationality of the database table design is the true master of program performance.
Reasonable database design should consider the following aspects:

· How business data is expressed. If an employee has multiple emails, you can create multiple Email fields such as email_1, email_2, and email_3 in the T_EMPLOYEE table, or create a T_EMAIL sub-table for storage, you can even use commas to separate multiple Email addresses and store them in one field.

· How data is physically stored. Such as the partition of a large table and the rational design of the tablespace.

· How to create a proper data table index. Table indexes are almost the most effective way to improve the query performance of data tables. Oracle has a wide range of data table index types, so it is particularly important to choose between them.

In this article, we will focus on the indexing of data tables, and we will also mention the other two points. Through the analysis of a simple database table design instance, the deficiencies in the design are extracted and corrected one by one. Considering that manual Writing of database and table SQL scripts is primitive and inefficient, we will use PowerDesigner 10, the most popular database and table design tool, to describe the table design process, so in this article, you will also learn some related PowerDesigner usage skills.

A simple example

A developer starts to design an Order System, which has two major business tables: the basic order information table and the order entry table, which have a master-slave relationship, here, T_ORDER is the main table of orders, while T_ORDER_ITEM is the table of order entries. The design result 1 of the Database Designer is as follows:

ORDER_ID is the order number, which is the primary key of T_ORDER. Key values are generated through the sequence named SEQ_ORDER_ID, while ITEM_ID is the primary key of the T_ORDER_ITEM table, and key values are generated through the sequence named SEQ_ORDER_ITEM, t_ORDER_ITEM is associated with the T_ORDER table through the foreign key of ORDER_ID.

The requirement document indicates that order records will be queried in the following two ways:

· CLIENT + ORDER_DATE + IS_SHPPED: queries the order and order entries based on the conditions of "customer + order date + whether to ship.

· ORDER _ DATE + IS_SHIPPED: queries the ORDER and ORDER entries based on the "order date + whether to ship" condition.

Based on this requirement, the Database Designer creates a composite index IDX_ORDER_COMPOSITE on the CLIENT, ORDER_DATE, and IS_SHPPED fields of the T_ORDER table. The IDX_ORDER_ITEM_ORDER_ID index is created for the T_ORDER_ITEM foreign key ORDER_ID.

Let's take a look at the final SQL script of the design:

We acknowledge that this design does not have any defects in the ER relationship, but there are the following areas to be optimized:

· Table data and index data are not stored in different tablespaces, but stored in the same tablespace without distinction. This will not only cause I/O competition, but also cause inconvenience to the database maintenance work.

· ORACLE will automatically create a normal B-Tree index for the primary key columns of the table. However, since the primary key values of both tables are provided through the sequence, with strict order (ascending or descending), it is more reasonable to manually specify a reverse key index for it.

· The ordinary B-Tree index of IDX_ORDER_ITEM_ORDER_ID created on the sub-table's T_ORDER_ITEM foreign key column ORDER_ID is very suitable for setting it as a compressed index, that is, creating a compressed B-Tree index. Because an order corresponds to multiple order entries, this means that the T_ORDER_ITEM table has many ORDER_ID column values with the same value. By specifying its index as a compressed B-Tree index, this not only reduces the storage space required by IDX_ORDER_ITEM_ORDER_ID, but also improves the performance of table operations.

· An attempt to create an index that contains only the three-field IDX_ORDER_COMPOSITE composite index to meet the two query conditions described above is problematic, in fact, the query using the ORDER_DATE + IS_SHIPPED compound condition will not use the IDX_ORDER_COMPOSITE index.

[NextPage]

Optimization Design

1. Separate Table data from index data for table space Storage

1.1 why independent tablespaces are required for table data and Indexes

Oracle is strongly established. Any database table in an application must create at least two tablespaces, one of which is used to store table data and the other is used to store table index data. If table data and index data are put together, the I/O operations of table data and index I/O operations will affect the system performance, reduce system response efficiency. Store table data and index data in different tablespaces (for example, APP_DATA and APP_IDX ), and put the data files of the two tablespaces on different physical disks at the physical level, this competition can be avoided.

Having an independent tablespace means that you can independently provide independent physical storage parameters for table data and index data without affecting each other. After all, table data and index data have different features, these features directly affect the setting of physical storage parameters.

In addition, the independent storage of table data and index data brings about data management and maintenance. For example, when you migrate a business database, to reduce the data size, you can only migrate the table data tablespace and re-index the target database to generate index data.

1.2 SQL syntax for using different tablespaces for table data and Indexes

The simplest format of table data and index data storage table space statement is as follows.

Store table data in the APP_DATA tablespace:

Create table T_ORDER (ORDER_ID NUMBER (10) not null ,...) Tablespace APP_DATA;

Store the index data in the APP_IDX tablespace:

Create index IDX_ORDER_ITEM_ORDER_ID on T_ORDER_ITEM (ORDER_ID ASC) tablespace APP_IDX;

1.3 How to operate PowerDesigner

1) first, you must create two tablespaces. Use Model-> Tablespace... to create two Tablespaces IN List of Tablespaces:

2) Specify the table data storage tablespace for each table. Double-click a Table in the design area, open the Table Properties design window, switch to the options page, and specify the Table data storage tablespace as shown in figure 3.

3) Specify the storage tablespace for each index. Switch to the Indexes page in Table Properties. All Indexes of the Table are listed here. Double-click the Index of the tablespace to be set. In the displayed Index Properties window, switch to the Options page, specify the index storage tablespace as follows.

Extend the tablespace problem: the tablespace of an application system database table can be further divided.

First, if a field of the LOB type exists in the table, you can specify a specific tablespace for it, because the LOB type data is significantly different from the general data policy in the management of the physical storage structure, put it in an independent tablespace to easily set its physical storage parameters.

Next, we need to consider the DML operation features of the database table data: according to the frequency of DML (INSERT, UPDATE, DELETE) operations, the data with almost no DML operations should be placed in an independent tablespace, because a table with few DML operations can set physical parameters that match its characteristics: for example, PCTFREE can be set to 0, and its BUFFER_POOL can be set to KEEP, so that data can be cached in the KEEP data cache area, not all.

In addition, you can also consider storing different business modules separately based on business needs, which mainly takes into account the backup problem. Assume that some of our business data is very important, while other business data is relatively less important. In this way, we can store the two separately to set different backup policies.

Of course, uncontrolled and refined tablespaces will also lead to complicated management and deployment. Therefore, it is often necessary to reasonably plan tablespaces based on business needs to achieve the best management and performance.

[NextPage]

2. Create a reverse key index for the primary key column explicitly

2

[1] [2] [3] Next page

The ORACLE tutorial is: several methods to optimize the design of Oracle database tables .. 1. Principle and purpose of reverse key index

We know that Oracle will automatically create an index for the primary key column of the table. This default index is a normal B-Tree index. The default B-Tree index is not ideal when the primary key value is added in order (ascending or descending. This is because when the values of the index column are strictly ordered, the index tree level increases rapidly with the insertion of data rows. The number of I/O reads and writes of an index is directly proportional to the layer level of the index Tree. That is to say, a Tree with five levels of B-Tree indexes, up to five I/O operations may occur when the index data is finally read. Therefore, reducing the layer level of the index is an important way to adjust the index performance.

If the data in the index column is inserted in a strict order, the B-Tree index Tree will become an asymmetric "", as shown in Figure 5:

If the data in the index column is inserted as a random value, we will get a symmetric index tree, as shown in 6:

Compared with figure 5 and figure 6, if Block A is found in Figure 5, I/O operations are performed five times, and figure 6 only needs three I/O operations.

Since the index column data is obtained from the sequence, its orderliness cannot be circumvented. However, when an index is created, Oracle allows the reverse direction of the index column value, that is, the reverse direction of the bits of the column values in advance, such as 1100, 10001, 10011,10111, and after the reverse direction will be. Obviously, the ordered data after bit reverse processing becomes more random, so that the resulting index tree is more symmetrical, thus improving the query performance of the table.

However, the reverse key index also has its limitations: If the WHERE statement requires range-based search of the index column values, such as BETWEEN, <,>, the reverse key index cannot be used. In this case, Oracle performs a full table scan. Only when the <> and = operations are performed on the reverse key index column, the reverse key index is used.

2.2 reverse key index SQL statement

Back to our example above, because the primary key values of T_ORDER and T_ORDER_ITEM come from the sequence, the primary key values are strictly ordered, so we should discard the index provided by the default Oracle, the reverse key index is explicitly specified for the primary key.

ORDER_ID is the primary key of the T_ORDER table. The primary key is named PK_ORDER. We create a reverse key index IDX_ORDER_ID for the ORDER_ID column and use this index for PK_ORDER_ID. The SQL statement is as follows:

Make sure that the SQL statement for creating IDX_ORDER_ID must reference this reverse key index before creating the SQL statement for the primary key of PK_ORDER.

Because the data in the primary key column is unique, the unique index is added to IDX_ORDER_ID.

2.3 How to operate PowerdDesigner

1) first, you need to create a reverse key index for the ORDER_ID column. Open the Table Properties window of T_ORDER, switch to the Indexes page, and create an index named IDX_ORDER_ID. After entering the Index name, double-click the Index. The Index Properties window is displayed. In the Columns window, select the ORDER_ID column. Switch to the Options page and set it as a reverse key index in figure 7.

2) explicitly specify the primary key PK_ORDER to use this index. Switch to the Keys page in the Table Properties window. By default, the primary Key name specified by PowerDesigner is Key1, Which is changed to PK_ORDER. Double-click this primary Key to bring up the Key Properties window, switch to the Options page and specify IDX_ORDER_ID for PK_ORDER as shown in figure 8.

It is undeniable that PowerDesigner is indeed the most powerful and easy-to-use database design tool in the industry, but unfortunately, when we specify an index for the primary key of the table, the statements generated by it have problems in order: that is, the statement for creating the primary key is located before the index statement:

You can adjust the settings of SQL statements generated by PowerDesigner to create tables and indexes, create an SQL statement that adds a primary key and a foreign key to the table to save the country. See the next step.

3) choose Database> Generate Database... to bring up the Database Configuration window, switch to the Keys & Indexes page, and set it as shown in Figure 9:

Here, we deselect the Primary Keys and Foreign keys options and select Indexes to generate only the index SQL statement of the table.

Click "OK" to generate an SQL statement for creating a database table and its indexes. Run the SQL statement to create a database. Then, follow figure 10 to create an SQL statement for adding a primary key and a foreign key to the table:

In addition, you must switch to the Tables & Views page to cancel all options to avoid generating statements for table creation again.

[NextPage]

3. Change the index of the foreign key column of the sub-table to the compressed type.

3.1 principles and uses of compressed Indexes

In the previous example, because one order corresponds to multiple order entries, the ORDER_ID field of T_ORDER_ITEM always contains repeated values, for example:

Create a common uncompressed B-Tree index on the ORDER_ID column. The physical storage of the index data is as follows:

The duplicate value of ORDER_ID appears in the index block, which not only increases the storage space requirement, but also reduces the query performance by = because more index data blocks need to be read during query. Let's take a look at the storage method of the compressed index data:

Compressed indexes eliminate duplicate index values and store the rowids associated with the same index column values. This not only saves storage space, but also improves query efficiency.

When you query a Master/Slave table such as T_ORDER and T_ORDER_ITEM, you must use a foreign key to query all associated records of the sub-table, therefore, it is very suitable to create a compressed index on the foreign key of the sub-table.

3.2 SQL statements for compressed Indexes

The SQL statement for creating a compressed index is very simple. The SQL statement for creating a compressed index on ORDER_ID of T_ORDER_ITEM is as follows:

You need to attach the compress keyword to the statement for creating the index.

3.3 How does PowerDesigner create a compressed index?

1) Open the Table Properties window of the T_ORDER_ITEM Table, switch to the Indexes page, and create an index named IDX_ORDER_ITEM_ORDER_ID for the ORDER_ID column.

2) double-click IDX_ORDER_ITEM_ORDER_ID to bring up the Index Properties window, switch to the Options page, and set the Index to the compressed type as shown in Figure 13:

4. Create a composite key index

The designer wants to query the IDX_ORDER_COMPOSITE index on the T_ORDER table to meet the following two combination conditions:

· CLIENT + ORDER_DATE + IS_SHIPPED

· ORDER _ DATE + IS_SHIPPED

For convenience, we will list the SQL statements used to create IDX_ORDER_COMPOSITE again:

In fact, the composite condition query executed on the CLIENT + ORDER_DATE + IS_SHIPPED column will apply this index, while the composite query executed on the ORDER_DATE + IS_SHIPPED column will not use this index, therefore, a full table scan operation will be performed.

You can use many tools to understand the execution plan of the query statement, and use set autotrace on to query the execution plans of the preceding two composite queries:

Open SQL/Plus and enter the following statement:

The execution plan is as follows:

It can be seen that Oracle uses IDX_ORDER_COMPOSITE to obtain the record ROWID that meets the condition, and then returns the record through ROWID.

The following query statement:

The execution plan of is:

Obviously, Oracle performs a full table scan on the T_ORDER table, and the IDX_ORDER_COMPOSITE index is not used.

For composite column index, we come to the conclusion:

　

Previous Page [1] [2] [3] Next page

The ORACLE tutorial is: several methods to optimize the design of Oracle database tables. Suppose in COL_1, COL_2 ,..., COL_n these columns create a composite index:

This composite index is used only for queries containing COL_1 (the first field of the composite index) in the WHERE statement, but not for queries without COL_1.

Back to our example, how can we create indexes that meet the requirements of CLIENT + ORDER_DATE + IS_SHIPPED and ORDER_DATE + IS_SHIPPED queries?

Considering that the base of the IS_SHIPPED column is small, there are only two possible values: 0, 1. In this case, there are two solutions: first, create a composite index for CLIENT + ORDER_DATE + IS_SHIPPED and ORDER_DATE + IS_SHIPPED respectively. Second, create an index for the CLIENT and ORDER_DATE columns respectively, the IS_SHIPEED column does not have an index.

In the first solution, the query efficiency is the fastest, but the CLIENT and ORDER_DATE appear twice in the index, occupying a large storage space. In the second solution, the CLIENT and ORDER_DATE will not appear twice in the index storage, which is more space-saving. The query efficiency is slightly lower than that in the first solution, but has little impact.

We use the second solution to create the index IDX_CLIENT and IDX_ORDER_DATE respectively for the CLIENT and ORDER_DATE respectively. The execution plan for the Combined Query condition is CLIENT + ORDER_DATE + IS_SHIPPED:

When the combination condition is ORDER_DATE + IS_SHIPPED, the execution plan is:

Through this transformation, we get an execution plan that satisfies the two types of combined queries.

Summary

The structure of the master-slave table instance that runs through this article is very simple, but its rough design involves many problems, which are easily overlooked by many database designers who do not have a good understanding of the Oracle physical storage structure.

Under normal circumstances, such a design will not cause serious system performance problems, but excellence is the quality of every outstanding software designer. In addition, for designers, we must be clear about this rule: For the performance improvement of the same quality, it is often more difficult at the coding level than at the design level.

There are still many mistakes to consider when improving the database performance in Oracle. This article covers some of the most common problems. Below, we will summarize the Methods to Improve database operation performance and some misunderstandings:

· For large tables, you can consider creating partition tables. Partition tables include range partitions, hash partitions, list partitions, and hash partitions. Through this, you can convert large tables into small tables.

· Consider proper data redundancy. For example, if a business table has an approval status, the approval process takes multiple steps. Each step corresponds to a record in the approval form, the record finally approved determines the business status. We can store a mark of the approval status in the business table to cancel the complex query of the associated table that needs to obtain the business approval status through the Associated approval table each time.

· Do not perform too many associated table queries. Some table tables that have almost no data changes, such as gender, education, marital status, etc, you can consider downloading it to the application memory for cache at one time when the application starts. After obtaining the result set from the database, then, the program uses the cached table code table data to translate these table code segments, instead of translating these fields in the database through association queries between tables.

· I often see some amazing designs: some low-base fields (such as gender and marital status) in tables that require frequent DML (INSERT, UPDATE, DELETE) Operations) create a bitmap index. Bitmap indexes are good, but they are applicable. In OLTP systems, bitmap indexes should not appear in tables that require frequent DML operations, bitmap indexes are only applicable to DSS systems that do not perform DML operations and only query. In addition, clustering and index organization tables are more suitable for DSS systems than OLTP systems.

Previous Page

Previous Page [1] [2] [3]