oracle| Design | Optimization Preface
The vast majority of Oracle database performance problems are caused by unreasonable database design, only a small number of problems rooted in db Buffer, Share Pool, Redo Log buffer and other memory module configuration unreasonable, I/O contention, DBA responsibilities, such as CPU contention. So unless you are faced with a system that is not changed, we should not focus on the memory, I/O, CPU and other performance adjustment items, but should pay attention to the design of the database table itself is reasonable, the rationality of the library table design is the real leading of the program performance.
Reasonable database design needs to consider the following aspects:
• How the business data is expressed. If an employee has more than one email, you can create multiple email fields such as Email_1, Email_2, email_3 in the T_employee table, or you can build a t_email child table to store. You can even separate multiple email addresses with commas in one field.
• How the data is physically stored. such as the partition of large table, the reasonable design of table space and so on.
• How to establish a reasonable index of the data table. Table indexes are almost the most effective way to improve the performance of data table query, Oracle has a rich type of data table index type, it is particularly important to choose the choice.
In this article, we will focus on the index of the datasheet and will also mention the other two points. Through the analysis of a simple example of the design of the library to lead to the deficiencies in the design, and to correct one after another. Given the raw and inefficient SQL scripts for hand-written library tables, we'll use the current most Popular library table design tool PowerDesigner 10来 to describe the process of table design, so in this article you will also learn about some of the relevant PowerDesigner usage techniques.
A simple example
A developer begins to design an order system that has two main business tables, namely the Order Basic information table and the Order Entry table, which has a master-slave table, where T_order is the order Master table, and T_order_item is the Order Entry table. The design results of the Database Designer are as shown in Figure 1:
Fig. 1 Master Order Form order_id is the order number, the primary key for the T_order, the key value is generated by the sequence named seq_order_id, and item_id is the primary key of the T_order_item table, which produces the key value through a sequence named Seq_order_item, T_ Order_item is associated with a order_id foreign key to the T_order table. The requirements document indicates that the order record will query the data in the following two ways: · Client + order_date+is_shpped: Order and order items are queried according to the "Customer + Order Date + delivery" condition. · Order_date+is_shipped: Order and order items are queried according to the "Order Date + delivery" condition. According to this requirement, the Database Designer established a compound index idx_order_composite on the client, Order_date and is_shpped three fields of the T_order table; T_order_item is a foreign key order_ ID to establish the IDX_ORDER_ITEM_ORDER_ID index. Let's take a look at the final SQL script for the design:/* Order Form/*
CREATE TABLE T_order (
ORDER_ID number is not NULL,
Address VARCHAR2 (100),
CLIENT VARCHAR2 (60),
Order_date CHAR (8),
is_shipped CHAR (1),
Constraint Pk_t_order primary KEY (ORDER_ID)
), create index idx_client on T_order (
CLIENT ASC,
Order_date ASC,
is_shipped ASC);///* Order Entry Child */create table T_order_item (
ITEM_ID number is not NULL,
order_id Number (10),
ITEM VARCHAR2 (20),
COUNT Number (10),
Constraint Pk_t_order_item primary KEY (ITEM_ID)
), create index idx_order_item_order_id on T_order_item (
order_id ASC);
ALTER TABLE T_order_item add constraint Fk_t_order__reference_t_order foreign key (order_id) references T_order (order_id ); We acknowledge that there is no flaw in this design in ER relations, but there are some areas to be optimized: • There is no storage of table data and index data in different tablespaces, and they are stored in the same table space without distinction. In this way, not only will cause I/O competition, but also for the maintenance of the database inconvenience. Oracle automatically creates a normal B-tree index for the primary key column of the table. However, since the primary key values of both tables are provided through a sequence, with strict order (ascending or descending), it is more reasonable to manually assign a reverse index (key index) at this time. • T_ in child table The normal B-tree index of the idx_order_item_order_id established on the Order_item foreign key column order_id is ideal for setting to a compressed index, which is to establish a compressed B-tree index. Because an order will correspond to multiple order entries, this means that there are many order_id column values of the same value in the T_order_item table, and by designating its index as a compressed B-tree index, not only can the storage space required for idx_order_item_order_id be reduced , you will also improve the performance of table operations. • Attempting an index that satisfies the two query conditions, as described previously, by creating a 3-field Idx_order_composite composite index is problematic, in fact using the Order_date+is_ Queries that shipped a composite condition will not take advantage of the Idx_order_composite index. Optimization design 1, separating table and index data table Space Storage 1.1 table data and indexes why you need to use a separate table space Oracle is strongly established that any one application's library table needs to create at least two tablespaces, one for storing table data and the other for storing table index data. Because table data and indexed data are put together, I/O operations of table data and I/O operations of indexes will have an I/O competition affecting system performance and reduce the system's response efficiency. This competition can be avoided by storing table and index data in different tablespaces (for example, one for App_Data, another for App_idx), and on a physical level, where the data files of the two tablespaces are placed on separate physical disks. Having a separate table space means that you can independently provide separate physical storage parameters for table and index data withoutAffect each other, after all, table data and index data have different characteristics, and these characteristics directly affect the physical storage parameter settings. In addition, table data and indexed data are stored separately, bringing data management and maintenance aspects. If you are migrating a business database, in order to reduce the size of the data, you can only move out of the table data table space, in the target database by rebuilding the index data can be generated. 1.2 Table data and indexes using SQL syntax for different table spaces specifies the simplest form of table data and index data storage table space statements as follows. Store table data in App_Data tablespace: Create TABLE T_order (order_id number () 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 PowerDesigner How to operate 1) first, you must create two tablespaces. Through the model->tablespace ... Create two table spaces in list of tablespaces:
Figure 2 Creating Tablespace 2) Specifies the table space for each table data store. Double-click the table in the design area, open the Table Properties Design window, switch to the Options page, and specify the storage tablespace for the table data as shown in Figure 3.
Figure 3 Specifies the storage table space for table Data 3) to specify the storage tablespace for index data for each index. Switch to the Indexes page in table properties, where all the indexes for the table are listed, double-click the index where you want to set the table space, switch to the Options page in the pop-up Index Properties window, and specify the storage tablespace for the index as follows.
Figure 4 Specifying the storage table space of the index data extends the problem of the table space: A table space for application system library tables can be finer divided. First, if there is a lob type field in the table, there is a specific table space assigned to it, because the data for the LOB type is very different from the general data strategy in the management of the physical storage structure, and it is easy to set its physical storage parameters by placing it in a separate table space. Second, you need to consider the DML operational characteristics of the database table data: Based on the frequency of DML (INSERT,UPDATE,DELETE) operations, data that is almost without any DML operation is placed in a separate tablespace. Because a table with very few DML operations can set physical parameters that conform to its attributes: such as Pctfree can be 0 and its buffer_pool specified as keep to cache data in a keep data buffer, and so on. In addition, you can consider separating the different business modules by business needs, primarily to take into account backup issues. Let's say we have a subset of business data that is of great importance and that other business data is less important, so that you can store the two separately to set up different backup strategies. Of course, the uncontrolled refinement of table space will also lead to complex management and deployment, and the reasonable planning of table space to meet business requirements to achieve the best management and performance often requires more trade-offs. 2, explicit primary key columns to establish a Reverse key index 2.1 The principle and application of Reverse key index we know that Oracle automatically indexes the primary key columns for the table, and this default index is the normal B-tree index. The default B-tree index is not ideal for situations in which primary key values are added sequentially (incremental or descending). This is because if the value of the indexed column is in strict order, the hierarchy of the index tree grows quickly as the data row is inserted. The number of I/O reads and writes that occur in the search index are proportional to the level of the index tree, that is, a 5-level B-tree index that can occur up to 5 I/O operations when it is eventually read to the index data. Thus, reducing the number of levels of an index is an important method of indexing performance tuning. If the data in the indexed column is inserted in a strictly orderly manner, the B-tree index tree becomes an asymmetric "skew tree", as shown in Figure 5:
Fig. 5 Asymmetric B-tree Index |
And if the indexed column's data is inserted in random values, we'll get a symmetric index tree, as shown in Figure 6:
Fig. 6 Symmetric B-tree Index |
Comparing Figure 5 and Figure 6, searching for block A in Figure 5 requires 5 I/O operations, while Figure 6 requires only 3 I/O operations.
Since indexed column data is obtained from a sequence, its ordering cannot be circumvented, but when an index is indexed, Oracle allows the value of the indexed column to be reversed, that is, to advance the bit-bit reversal of the column value, such as the 1000,10001,10011,10111,1100 backward value will be 0001, 1001,1101,0011. Obviously, the ordered data of bit reverse processing becomes more random, so the index tree is more symmetrical, thus the query performance of the table is improved.
However, the Reverse key index also has its limitations: if in the where statement, the value of the indexed column needs to be scoped, such as between, <, >, and its Reverse key index is not available, Oracle will perform a full table scan, and only the reverse key indexed columns are <> and = The reverse key index is used when the comparison is done.
2.2 SQL statement with Reverse key index
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 in strict order, so we should discard the default Oracle supplied index and take the explicit primary key to specify a reverse key index.
ORDER_ID is the primary key for the T_order table, the primary key is Pk_order, we set up a reverse key index idx_order_id on the order_id column, and the pk_order_id uses this index, and the SQL statement is as follows:
CREATE TABLE T_order (
ORDER_ID number is not NULL,
CLIENT VARCHAR2 (60),
Address VARCHAR2 (100),
Order_date CHAR (8));
Create unique index idx_order_id on T_order (order_id ASC) reverse;alter table t_order add constraint pk_order primary ke Y (order_id) using index idx_order_id;
Make sure that the SQL statement that creates the idx_order_id is created before the SQL statement that pk_order the primary key, because the primary key needs to be referenced to this reverse key index.
Because the data for the primary key column is unique, the idx_order_id is added with a unique qualification to make it a unique index.
2.3 Powerddesigner How to operate
1 First, you need to establish a reverse key index for the order_id column. Open the T_order Table Properties window, switch to the Indexes page, and create a new index named IDX_ORDER_ID. After filling in the name of the index, double-click the index to eject the Indexes Properties window and select the order_id column in the columns of the window. Then, switch to the Options page and set it as a reverse key index, as shown in Figure 7.
Figure 7 Sets a reverse key index of 2) explicitly specifies the primary key Pk_order use this index. Switch to the Keys page in the Table Properties window, by default, PowerDesigner the primary key specified by T_order as Key1, we rename it to Pk_order, double-click the primary key, eject the key Properties window, Switch to the Options page and specify idx_order_id for Pk_order in Figure 8.
Figure 8 Specifying a specific index for the primary key undeniable PowerDesigner is indeed the most powerful and Easy-to-use database design tool in the industry today, unfortunately, when we assign an index to a table primary key, the resulting statement is problematic in order: The statement that creates the primary key is before the index statement is created: Create Table T_order (...); ALTER TABLE T_order ADD constraint Pk_t_order primary key (order_id) using index idx_order_id;create unique index Idx_orde r_id on T_order (order_id ASC) reverse; We can adjust the PowerDesigner generate SQL statements, Sir, to create a table and index of SQL statements, and then create a table to add primary keys and foreign keys of the SQL statement to achieve the curve for the purpose of saving the nation, see the next step. 3 through the menu Database->generate Database ... Pull up the Database configuration window, switch to the Keys&indexes page, set according to Figure 9:
Figure 9 Setting the option to generate keys and index SQL here, we'll cancel the primary keys and the foreign keys option, and we'll check the indexes to make the table's indexed SQL statement. When you click OK, generate the SQL statement that creates the database table and its index, run the SQL database after it is created, and then follow Figure 10 to create the SQL statement that adds the primary key and foreign key to the table:
Figure 10 Generating SQL statements that create the primary and foreign keys of the table in addition to this setting, you must switch to the tables & Views page, cancel all options, and avoid rebuilding the statement that created the table. 3. Change the index of the foreign key column of the child table to the compression type 3.1 compression index principle and use in the previous example, because an order would correspond to multiple order entries, T_order_item's order_id field always appears with duplicate values, such as: item_id order_id ITEM COUNT
1 100 101 1
2 100 104 2
3 100 201 3
4 200 301 2
5 200 401 1
6 200 205 3 to create a plain uncompressed B-tree index on the order_id column, the physical storage form of the indexed data is as follows:
Figure 11 Duplicate values of index storage order_id that are not compressed are repeated in the index block, which not only increases storage space requirements, but also reduces query performance because queries need to read more blocks of indexed data. Let's take a look at how the indexed data is stored after compression:
Figure 12 The index that compresses the indexed storage compression type eliminates duplicate index values and stores the ROWID associated with the same indexed column values together. In this way, not only saves the storage space, the query efficiency also enhances, really can be called both the United States. Object T_order and T_order_item such a master-slave table query, in general, we must query through the foreign key all associated records, so it is very appropriate to establish a compressed index on the foreign key of the child table. SQL statements for 3.2 compressed indexes the SQL statement that creates the compressed index is very simple, and the SQL for creating a compressed index on T_order_item's order_id is as follows: Create INDEX idx_order_item_order_id on T_ Order_item (order_id ASC) compress; You need to attach the Compress keyword after you create the index statement. 3.3 PowerDesigner How to create a compression index of 1) Open the window of table properties for T_order_item tables, switch to Indexes page, and create a order_id column named Idx_order_item_order The index of the _id. 2) Double-click the Idx_order_item_order_id Pop-up Index Properties window, switch to the Options page, set the index to compressed according to Figure 13:
Figure 13 specifying the index as a compression type 4, creating a composite key index to meet the requirements the designer wants to satisfy the following two combinations of queries through the Idx_order_composite composite Index on the T_order table: · CLIENT + order_date + is_shipped Order_date + is_shipped for ease of elaboration, we have specifically idx_order_composite the creation of SQL statements are listed again: CREATE INDEX idx_order_composite on T_order (CLIENT ASC, order_date ASC, is_shipped ASC); In fact, the composite condition query executed on the client + order_date + is_shipped is applied to the index, and the composite query executed on the ORDER_DATE + is_shipped column does not use this index. This will result in the operation of a full table scan. You can use a number of tools to understand the execution plan for a query statement by using set Autotrace on to query the execution plans of the above two composite queries: Open Sql/plus, and enter the following statement:sql> set Autotrace on
Sql> SELECT * from t_order where CLIENT = ' 1 ' and order_date= ' 1 ' and is_shipped= ' 1 '; The analysis gets the execution plan: SELECT STATEMENT optimizer=choosetable ACCESS (by INDEX ROWID) of ' T_order ' index (RANGE SCAN) of ' Idx_order_co Mposite ' (non-unique) it is visible that Oracle first uses Idx_order_composite to get a record rowid that satisfies the criteria, and then returns records by ROWID. The following query statement:sql> SELECT * from T_order where order_date= ' 1 ' and is_shipped= ' 1 ' is the execution plan: select STATEMENT optimizer=choose Table ACCESS ' T_order ' It is obvious that Oracle performed a full table scan on the T_order table and did not use the Idx_order_composite index. For the composite column index, we conclude that a composite index was established on the col_1,col_2,..., col_n These columns: CREATE INDEX IDX _composite on TABLE1
{
Col_1,
Col_2,
...,
Col_n
} only queries that contain Col_1 (the first field of the composite index) on the WHERE statement use the composite index, and queries that do not contain col_1 do not use the composite index. Back to our example, how do I build an index that satisfies the client + order_date + is_shipped and order_date + is_shipped two queries? Given the small cardinality of the is_shipped column, there are only two possible values: 0, 1. In this case, there are two scenarios: first, to create a composite index for client + order_date + is_shipped and Order_date + is_shipped, and second, to create an index on the client and Order_date columns, respectively, The Is_shipeed column does not establish an index. The first scenario has the fastest query efficiency, but because the client and order_date are repeated two times in the index, taking up a larger storage space. The second scenario client and Order_date will not appear in the index storage two times, more space-saving, query efficiency than the first scenario will be slightly lower, but the impact is not significant. We use the second scenario to create index idx_client and idx_order_date for both client and order_date, and the execution plan for the combination query condition of client + Order_date + is_shipped is: SELECT STATEMENT optimizer=choose TABLE ACCESS (by INDEX ROWID) of ' T_order ' and-equal index (RANGE SCAN) of ' idx_client ' (non-un IQUE) INDEX (RANGE SCAN) of ' idx_order_date ' (non-unique) and the execution plan when the combination condition is Order_date + is_shipped is: SELECT STATEMENT Optimize R=choose TABLE ACCESS (by index ROWID) of ' T_order ' index (RANGE SCAN) of ' idx_order_date ' (non-unique) through such modifications, we got a full The execution plan for two combined queries. It is simple to summarize the example structure of the Order Master table, but its rough design contains many problems, which is also a lot of Oracle physical storageThe storage structure is not well understood by the Database Designer where it is easy to overlook. Under normal circumstances, such a design does not lead to serious system performance problems, but excellence is the quality of every excellent software designer, in addition, for designers, we must be clear such a law: for the performance of the same quality, in the coding level often need more than the design level to pay more hardships. In Oracle to improve the performance of the database needs to consider the problem, there are many mistakes to note, this article covers some of the most common problems. Below, we will improve the database operation performance methods and some misunderstandings to make a summary: • For large tables, consider creating partitioned tables with range partitions, hash partitions, list partitions, and hash partitions, which can be used to make large tables smaller. • Consider the right amount of data redundancy, such as a business table with an approval status , approval needs to go through many steps, each step corresponds to a record of the approval table, the final approval of the record determines the state of the business. We can store a sign of approval status in the business table, to cancel a complex relational table query that requires the approval status of the business through the associated approval table. • Do not make too many relational table queries, some table code tables with little data changes, such as sex, education, marital status, etc. Consider downloading the application to the memory of the application once it is started and caching it. After obtaining the result set from the database, the program uses these cached table code table data to translate the table code fields instead of translating the fields through the relational query between the tables in the database. I often see some startling design: Create a bitmap index on some low cardinality fields (such as sex, marital status) for tables that require frequent DML (INSERT,UPDATE,DELETE) operations. Bitmap indexing is a good thing, but it is useful, and in OLTP systems, bitmap indexes should not appear in tables that require frequent DML operations, and bitmap indexes are only available in DSS systems that are almost without DML operations and are only queried. In addition, clustered and indexed organization tables are better suited to DSS systems than O
