Some methods of optimizing Oracle database table Design

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:

Fig. 1 Master Order Form

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:

/*订单表*/
create table T_ORDER (
　　ORDER_ID NUMBER(10) 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);
/*订单条目子表*/
create table T_ORDER_ITEM (
　ITEM_ID NUMBER(10) 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 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.