The Oracle tutorial you are looking at is a number of ways to optimize the design of Oracle library tables.
Objective
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:
ORDER_ID is the order number, the primary key for the T_order, which produces the key value through a 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 the 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 this design:
We acknowledge that the design does not have a flaw in ER relations, but there are the following areas to be optimized:
• No table data and index data are stored in different table spaces, and they are stored in the same tablespace without distinction. This will not only cause I/O competition, but also bring inconvenience to the maintenance of the database.
· Oracle automatically creates a normal B-tree index for the primary key column of the table, but because the primary key values for both tables are supplied by the sequence, with strict order (ascending or descending), it is more reasonable to manually assign a key index (reverse key) to it.
• The normal B-tree index of the idx_order_item_order_id established on the T_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.
• It is problematic to attempt to index only by establishing a 3-field Idx_order_composite composite index that satisfies the two query conditions as described above, and in fact queries using order_date+is_shipped compound conditions will not take advantage of Idx_order_ Composite index.
[NextPage]
Optimized design
1, the table data and index data separate table space storage
1.1 Table data and indexes why you need to use a separate table space
Oracle strongly establishes 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 both table and index data without interacting with each other, since table and index data have different characteristics that directly affect the setting of physical storage parameters.
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
The simplest form of specifying table data and index data store tablespace statements is as follows.
To store table data in a App_Data table space:
CREATE TABLE T_order (order_id number () not NULL, ...) Tablespace App_Data;
To store index data in a APP_IDX tablespace:
Create INDEX idx_order_item_order_id on T_order_item (order_id ASC) tablespace app_idx;
How to operate in 1.3 PowerDesigner
1 First, you must create two table spaces. Through the model->tablespace ... Create two table spaces in list of tablespaces:
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.
3 Specifies the storage table space 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.
To extend the table space problem: A table space that applies a system library table can be more finely partitioned.
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.
[NextPage]
2, the explicit primary key column to establish a reverse key index
2
[1] [2] [3] Next page
The Oracle tutorial you are looking at is: a number of ways to optimize the design of Oracle library tables. 1 The principle and use of reverse key indexing
We know that Oracle automatically indexes the primary key columns of 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:
And if the indexed column's data is inserted in random values, we'll get a symmetric index tree, as shown in Figure 6:
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:
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.
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.
Admittedly powerdesigner is indeed the most powerful and Easy-to-use database design tool in the industry, but unfortunately, when we assign an index to a table primary key, the statement that it produces is problematic in order: The statement that creates the primary key is before the index statement is created:
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:
Here, we cancel the primary keys and the foreign keys option, and check the indexes to make the index SQL statement for the table to be generated.
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:
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.
[NextPage]
3. Change the index of the foreign key column of the child table to compression type
Principle and application of 3.1 compression index
In the previous example, because an order would correspond to more than one order entry, the T_order_item order_id field would always appear with duplicate values, such as:
To create a plain uncompressed B-tree index on the order_id column, the physical storage of the indexed data is as follows:
ORDER_ID duplicate values 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:
A compressed index 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.
3.2 SQL statements for compressed indexes
The SQL statement to create a compressed index is very simple, and the SQL for creating a compressed index on the T_order_item order_id is as follows:
You need to attach the Compress keyword after you create the index statement.
3.3 PowerDesigner How to create a compressed index
1 Open T_order_item Table Properties window, switch to Indexes page, create an index named idx_order_item_order_id for order_id column.
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:
4, to establish a composite key index to meet the needs
The designer expects a query that satisfies the following two combinations of conditions 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 listed the Idx_order_composite SQL statement again:
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:
An analysis of the resulting implementation plan is:
It can be seen that Oracle first uses Idx_order_composite to get the record rowid that satisfies the condition, and then returns records by ROWID.
and the following query statement:
The execution plan for:
It is clear that Oracle performed a full table scan on the T_order table and did not use the Idx_order_composite index.
To the composite column index, we come to the conclusion that:
prev [1] [2] [3] Next page
The Oracle tutorial you are looking at is a number of ways to optimize the design of Oracle library tables. Suppose you set up a composite index on these columns in the 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 when the combination query condition is client + order_date + is_shipped is:
The execution plan when the combination condition is Order_date + is_shipped is:
Through this transformation, we have a implementation plan that satisfies the two combination queries.
Summarize
The Order-master table instances throughout this article are simple in structure, but their rough design contains 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 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 has an approval status, approval needs to go through many steps, each step corresponding 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 needs to get the business approval status through the association approval list each time.
• Do not make too many relational table queries, some table code tables with little data changes, such as sex, education, marital status, and so on, can be considered to be cached in the application's memory once the application is started, after obtaining the result set from the database, The program uses these cached table code table data to translate these form fields, instead of translating the fields through the relational query between tables in the database.
• Often see some startling design: Create a bitmap index on some low base 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 rather than OLTP systems.
Previous page
prev [1] [2] [3]
