Description of common Oracle database fields

The so-called auto-increment field means that the relevant fields can be automatically added to the Oracle database as we actually use it. Generally, the values of these fields have no clear meanings and are only used for a unique identifier. This field is generally set as a primary key.

Oracle Database auto-increment Field

If the application only targets Oracle databases without considering database independence, sequence is nothing more than the best choice. For friends who used to use other databases such as MSSQL in the past, it was too difficult to use Oracle. It took a lot of effort to create a self-incrementing field, but because of this, it also brings advantages that other databases cannot compare. For example, an order system has an order header and an order line. Generally, the Order header is inserted before the order line, for the auto-increment fields of databases such as MSSQL, you only need to know the specific ID value after insertion. After writing, you need to check the value of this field and then use it with the order line; for oracle, you only need to extract a value from sequence and use the header and row together. The most important thing is that sequence has a very high effect and does not need to worry about performance issues.

Oracle Database elastic Fields

When designing the database table structure, it is best to set aside several additional fields, because with the use of the system, there is usually a need to add fields. The advantage of reserved fields is that you only need to enable the reserved fields as long as you need them. You do not need to perform DDL operations. This poses a low risk for database maintenance in the future. In addition, General DDL operations are performed, this will cause the cascade VIEW/PACKAGE program to become invalid. If the elastic field is reserved, this problem will not occur.

Reserved fields can also be divided into three types: string type, numeric type, and date type. You can reserve 10 fields for each type, or use the following style as needed:

 
 

  
  
NUMBER_ATTRIBUTE1   
  
  
STRING_ATTRIBUTE1   
  
  
DATE_ATTRIBUTE1   
 
 

If an elastic field is not enabled, will it occupy too much storage space? The answer is no, because in this large database structure, when only one field is actually used, otherwise, it is only a "Description" and does not occupy the actual space, so it will not cause a waste of space.

Oracle Database shard Field

This is not a field type, but refers to the fact that during table design, a large table can be split into different small tables for storage, such as user tables, including login names and passwords, name, birthday, and so on. In some cases, there may be hundreds of member attributes.

When the data size is small, no matter what kind of storage, there will be no performance problems, but when the data size is large, you must consider performance issues. If the index is reasonable, the query speed will not be too slow regardless of the amount of data. However, in some special cases, if the index cannot be used, a full table scan is generated by the FTS ).

Therefore, scanning a small table is totally different from scanning a large table. Therefore, we recommend that you store large tables separately and extract frequently used fields separately. Even if the whole table is scanned, it can also achieve better control efficiency.

When used, as long as both the primary table and the sub-table have indexes, they can be combined for query, which is basically the same effect as a real large table, although the performance must be a little slower than a real big table, it is worth comparing it with the performance improvement on the other hand.