Article Title dual table use in Oracle Database, oracledual

Article Title dual table use in Oracle Database, oracledual

I. business scenarios
The business process needs to be written and updated, so there are original tables and historical tables.
The table ID must be unique and the IDs of the two tables are associated. In addition, the database may be migrated later.

Ii. Solution Selection

Solution 1: Set id to int type for auto-growth.

This method is easy to program. You do not need to consider the uniqueness of the id. It is maintained by the database. However, there are disadvantages in this business scenario.
1. Two tables are associated here. The data uniqueness cannot be ensured because of the auto-increment of IDs, and the data ing between the original table and the historical table cannot be guaranteed. For example, you need to insert the same data to the original table A and the historical table A_HISTORY. After Table A is inserted, the ID of Table A cannot be obtained (the ID of Table A is the primary key, and data uniqueness cannot be ensured according to other conditions) the same data entry cannot be inserted between the_HISTORY table and table.
2. Because the database needs to be migrated in the later stage, and the ID increases automatically. Errors may occur during data migration.

Solution 2: dual table
Dual tables are system tables created by SYS users. They are separated from user tablespaces by default in system tablespaces. By creating sequences in dual tables, you can control the uniqueness of table IDs.

Iii. Implementation
1. Visualized operations
In oracle SQL developer. In the system tablespace schema directory. Create a sequence Update_sequence and set the progressive step size, minimum value, and maximum value.

2. SQL statements

Create SEQUENCE Update_sequence increment by 1 MAXVALUE 5000 CYCLE;

You can create a sequence using either of the following methods:
SELECT UPDATE_SEQUENCE.NEXTVAL FROM DUAL
The ID of the original and historical tables. This ensures the uniqueness of the id and the ing between the original table and the historical table.