Shulou(Shulou.com)06/01 Report--

Basic suggestions for physical design of the table: 1. Except for special requirements, it is recommended to use a common heap table (heap table). 2. When creating each table, it is recommended to establish the tablespace instead of the default tablespace. 3. It is recommended that according to the dml operation frequency value, if the update operation frequency is high, the PCTFREE can be set to higher, otherwise, the PCTFREE can be set to lower. 4. When there are a large number of delete operations in the table, the increase of PCTUSED value will increase the utilization rate of space. 5. Set INITRANS larger for tables and indexes with high concurrent visits, especially for indexes, such as setting INITRANS to 10. 6, and consider IOT table technology for smaller codes and parameter tables 7. If a static table is closely related, you can use the method of clustering table.

The basic suggestions for index design are as follows: 1. Generally speaking, the trading system is established as a B* tree index, and for the database warehouse, Bitmap, Bitmap join and other indexes can be considered. 2. By analyzing the data access mode of the application software, the index design is carried out. 3. The index and data are stored in different table spaces. 4. Do not operate on the index field. 5. Do not format the index field. Do not use functions on index fields. 7. Strengthen the analysis of use and index efficiency. 8. Do not use null values for index fields, because indexes cannot store null values.

B* tree single-field index design suggestions: 1. Analyze the constraint fields in the sql statement. 2. If the constraints are not fixed, it is recommended to create a common B* index for a single field. 3. Select the field with the highest selectivity to establish the index. 4. If it is a winning join sql statement, pay attention to whether the join field of the driven table (drived table) needs to create an index. 5. Analyze the implementation plan and evaluate the effect in quantitative form through a variety of sql analysis tools.

Composite index design recommendation 1. Analyze the constraint fields in the sql statement. 2. If the constraint fields are relatively fixed, priority should be given to creating a common B* tree composite index with multiple fields. 3. If the field is the primary key or the only condition, or if the field is highly optional, although the constraint field is relatively fixed, it is not necessary to build a composite index. A single-field index can be built to reduce the cost of the composite index. 4. When the first field of the composite index is used as a constraint, the composite index will be enabled, and the fields of the composite index will be sorted according to the level of selectivity.

The suggestion of this paper is extracted from teacher Luo Min's "oracle understanding High performance Optimization".

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.