Tips for Oracle program development

Oracle has many advantages, but if it is improperly used, it cannot take advantage of it. here is my little experience in Oracle development, share with you. Oracle has many advantages, but if it is improperly used, it cannot take advantage of it. here is my little experience in Oracle development, share with you.

I. Use of fields in database design

In some table designs, some common sections have basically become a specification. in large systems, you can see traces of these fields. of course, the names of these fields may be different. Common fields are classified into the following types:

1. WHO field

This type of field is used to record the Operation Change information of each line of record, such as who added this line of record and who modified it. the detailed descriptions are as follows:

Field name	Type	Description
LAST_UPDATE_DATE	DATE	Last modification date
LAST_UPDATED_BY	NUMBER (15)	Last modified
CREATION_DATE	DATE	Creation date
CREATED_BY	NUMBER (15)	Created

I. creator

In any system, there is usually a process of permission verification and logon. after logon, the login information is recorded in the system memory, when this person adds a table to the database, the ID value of this operator is also written into the table for later Statistics and auditing.

II. creation time

Similar to the meaning of the creator, the value of this field is generally taken from the server rather than the client when the system is written at the same time as the creator. for example, in oracle, SYSDATE can be directly used as the value of this field.

III. Last modified

After a record is created, it is also possible to be modified. here we need to record the modified person for later auditing.

However, it should be noted that only the last modification information is recorded here. if a record is modified multiple times, the intermediate modification will be untraceable. if you need to record the detailed modification information, you need to use the log function, which exceeds the efficacy of this field.

IV. last modification date

Similarly, if the last modifier is written to the record at the same time, the last modification time is recorded, and the intermediate modification is overwritten.

2. status and validity field

In some news content, a timeliness is often involved, that is, a piece of news may only be visible to the outside in a certain period of time, and cannot be published beyond this period of time, in addition, if some content is found to be faulty and needs to be temporarily blocked, the status field can be used. The details are as follows:

Field name	Type	Description
STATUS	NUMBER	Status
START_DATE	DATE	Effective start date
END_DATE	DATE	Effective end date

I. status

This field is generally expressed in numeric type, 0 indicates invalid, and 1 indicates valid. of course, when using this field, you can escape these two values and display them with "valid" and "invalid, the storage mode is not affected.

Failure is not equal to deletion. after the invalid content is modified by the management program, the status becomes valid and can be restored to normal use.

II. effective start date

If this field has a specific value, the information is valid only when the time exceeds this time. before this date, the information is automatically processed as invalid. Note that, if this field is left blank, skip this condition check to handle it. This allows program flexibility.

III. effective end date

The specific meaning is the same as above, but the content will be invalidated after this period.

3. Logical deletion

There are generally two ways to delete data processed in the database system: physical deletion and logical deletion. The so-called physical deletion means that commands such as delete are directly used in the database, deleting data from a database is not recoverable from normal means. although the data volume can be reduced to some extent, it is not conducive to audit tracking; logical deletion means that the data is not deleted, but the record is marked with a value assigned to a field, indicating that the record has been deleted.

The processing logic of Logical deletion is only used by the application, because the data actually exists in the database.

The fields involved are as follows:

Field name	Type	Description
DELETED	NUMBER	Delete?
DELETE_DATE	DATE	Deletion time
DELETED_BY	NUMBER	Deleted

I. delete a flag

This field has two values. 0 indicates normal, and 1 indicates deleted.

After the deletion mark is marked, the deleted = 0 condition must be added to the judgment condition for the query statement. Otherwise, a major error may occur.

II. deletion time

It is used with the delete mark to indicate the deletion time. you can use the current server time and use sysdate to fill in.

III. deleted

Similar to the last modifier, the specific operator who needs to record the deletion

Unlike a flag, a flag is used to delete a flag. although the front-end cannot display data, the background administrator can adjust the status through the management program, but the deletion flag is different, after the deletion is confirmed, the entire record should be understood as nonexistent.

4. Auto-increment field

An auto-increment field is a field that can be automatically added when used.

The value of this field is generally not clearly defined and only used for a unique identifier. This field is generally set as a primary key.

If the application only targets Oracle, but does not consider 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.

5. 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.

6. split fields

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, the index cannot be used, as a result, the FTS (so-called full table scan) will be generated, so scanning a small table is totally different from scanning a large table. Therefore, we recommend that you store large tables separately, extract several commonly used fields separately, so that even if the full table scan is performed, the efficiency can be better controlled.

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.

Currently, some large systems use this splitting method.

II. rational use of views

A view is a representation of a base table. it does not store data physically, but calls data from a base table as needed, it can be understood as a convenient tool to encapsulate an SQL statement.

View has many advantages:

1. convenience

If a query is complex and referenced in a program, it will be very inconvenient. especially when the SQL statement is repeatedly called in a program, the program will be very bloated, if the SQL statement is encapsulated into a view, the entire program looks very fresh and clean.

2. flexibility

When a table is referenced by multiple programs, if the structure of the table changes, all programs need to be adjusted accordingly, resulting in a huge workload, if only one view is referenced in the program at this time, we only need to modify the View correctly, then all programs will not have any problems.

3. security

A view can contain only a few fields of a base table. in this way, users who use this view can view other confidential fields of the base table when granting public permissions.

View has many advantages. we recommend that you use multiple views in actual work.

3. PACKAGE

A package is a PACKAGE. It includes variables, functions, and stored procedures in a PACKAGE. all public variables can be shared throughout the PACKAGE, this is the most convenient part.

Another feature of the package is that the external interface becomes simpler. no matter how complicated the package is, you only need to open limited interfaces to the outside world.

These features of the package can be found in any document. I will not talk about them any more. here I only emphasize that the package is invalid. when the aspx program references the oracle package, because of the system cache, it will record the package's failure status. if the status of a package has been changed from invalid to valid, the status of the IIS cache record may not be updated automatically, if it is still processed as invalid, system errors may occur. To avoid this error, I usually add a public function in the package named STATUS. this function simply returns the number 1. before calling other functions in the package, first, call this function to check the current status of the package. if 1 is returned correctly, you can continue the subsequent operation. If null is returned, it indicates that the current package is faulty, you can have an interaction to prompt the user for processing. When you need to coordinate multiple packages for processing a transaction, it is very painful if the previous package has been processed successfully and the subsequent package cannot pass, because the transaction may be incomplete, you need to check the status of all packages in advance to eliminate all possible risks in advance.

IV. index

The index function is obvious, which can greatly improve the speed of the check. especially for large tables, if there is no index, full table scan is required. this is a very time-consuming action, therefore, you need to create indexes to speed up.

However, in some cases, the index will have a negative impact, such as a large number of insert or modify/delete operations, because each small action will simultaneously modify the index, this will greatly reduce the efficiency of DML operations. Therefore, before a large number of DML operations, we recommend that you delete the index first, and then re-create the index. in terms of the total time, this can save a lot.

For more articles on Oracle program development tips, please follow the PHP Chinese network!