Some common good habits of database design and use

Good habits require constant cultivation and reinforcement until they become a habit, and then become part of the mind.

Before or after work, I have to give my family a read "Zi Jing" or "disciple rules", he may not understand the content of these words, but I hope that this knowledge can accompany him to grow up as part of thought and action.

When we design the system architecture and database planning, there are some basic principles.

I don't know if it's okay, learn more.

Do not understand the matter, see more times to understand

Perseverance is life.

Database common good habits record down, nothing to turn over.

Design tables and indexes, etc.

1. Select the appropriate small segment to reduce the size of database row data and improve the efficiency of index matching to improve database performance. If the date uses date instead of datetime, type or tag using tinyint instead of smallint and int, using fixed-length field instead of the fixed-length field (such as Char instead of VARCHAR2), the data row size can be reduced more or less, and the hit rate of the database buffer pool is increased;

2. The primary key, the selection of the table index of the stability and efficiency has a great impact, it is generally recommended to consider the database self-increase or self-maintenance of the unique values.

3. The appropriate redundancy of some data, so in the design of the table although uncomfortable, but can improve a lot of performance;

4. If the business content is more, do not put all the fields in a table, so that the table space will be very large, query speed is very slow, to be clear "which fields commonly used, which fields are not used", try to put the infrequently used fields in the schedule, and the main table one-to-one association.

5. Indexing in high-separation fields, such as the phone number field in the user table, is highly efficient;

6. Type, state and other content basic changes in a few fields, in principle, the bitmap index is more effective than the normal index, but to create a bitmap index, it is necessary to verify whether efficiency is improved;

7. Do not rely too much on indexing, in general, creating an index increases the corresponding query speed, but too many indexes affect the input write.

Some habits of using tables

1. As we write Java programs, we can use the Apache Commons Toolkit, the use of Oracle's own internal functions can not only improve the development efficiency, but also improve the efficiency of SQL statement execution;

2. Try to write SQL statements as dm=? , and then assign a value instead of direct dm=9, so that Oracle compiles the statement every time it executes, and the previous method, Oracle compiles only once.

3. The efficiency of exists is high in most cases, depending on the size of the master table data, when selecting in or exist operations.

3.

4. Not in the best in the code do not appear, efficiency is not high, if must be used, you can use minus or not exists instead.

4.

5. In the Where condition, try not to use To_char,to_date,upper and other methods for format conversion. Conversion and calculation operations are placed as far as possible = right, and if the conversion is done on the left, a full table scan is performed, and if the index is not function-based, the index no longer works when using functions in the WHERE clause for indexed columns.

6. Do not use a complex logic with a statement to achieve, if the complex statement execution is not ideal, you can try to break it into a simple statement to see if efficiency increases.

7. Try not to use the "<>" or "! =" operator. Handling of non-equal operators results in a full table scan, which can be replaced with "<" or ">". For example: a<>0 instead of a>0 or a<0,a<> "to a>", substituting ">=" for ">".

8. Oracle stops using indexes to perform a full table scan when the is null or is not NULL appears in the WHERE clause. You can consider setting the index column to notnull when designing the table. This allows you to replace the null operation with another operation.

8.

9. When the wildcard "%" or "_" is the first character of the query string, the index is not used, so generally do not appear as the first character. Or as much as possible without this way;

10. For a connected column "| |", the last Connection column index will be invalid. Avoid connections as much as possible, either by separating them or by using functions that do not function on the columns.

10.

11. When comparing columns with different data types, the index is invalidated.

The union operator filters the results, eliminates duplicates, and may cause the disk to be sorted if the amount of data is large. If you do not need to delete duplicate records, you should use UNION ALL.

12.

Oracle processes multiple query conditions in the WHERE clause from bottom to top, so that the table join statement should be written before other where conditions, the condition that the maximum number of records can be filtered must be written at the end of the WHERE clause. Oracle handles the table names in the FROM clause from right to left, so if you include more than one table in the FROM clause, the table with the fewest records is placed last. ORACLE10G and the above version, as well as on this issue is optimized, but this habit is good, worth preserving.

The non-indexed columns in the by-pass statement degrade performance and can be processed by adding an index. If the amount of data ordered is large, the impact on performance is very large, try to avoid using. The uniqueness index is a better compromise method;

Some common good habits of database design and use