Oracle replication tables, staging tables, and views

Create a table new_table and old_table table structure (no old_table Records)

CREATE TABLE new_table as SELECT * from old_table where 1=0;

Create a table new_table and old_table table structure (with Old_table Records)

CREATE TABLE new_table as SELECT * from Old_table;

Copy a table to another table

INSERT INTO new_table select * from Old_table;

Create a view, delete a view

Create or Replace view **_view as SELECT * from **table;
Drop View **_view;

Create a temporary table

Create global temporary table TableName on commit preserve rows as select * from Others_table

Experience with the actual use of Oracle temporal tables

Temporary tables can only be valid within the current connection;

Temporary tables are not indexed, so the amount of data is larger or multiple queries, it is not recommended;

When the data processing is more complicated, the table is fast, whereas the view is fast;

It is recommended to use cursors when querying data only;

We are currently using Oracle as a database support platform for the actual application, it can be said that the amount of data or a relatively large system, that is, the table of data volume is generally in the millions above the amount of data.

Of course, creating partitions in Oracle is a good choice, but when you find that your app has more than one table associated with it, and that most of these tables are large, and you're associated with one or a few tables, the resulting set of results is very small and the query gets the result set very quickly, So this time I'm thinking of creating a "temp table" in Oracle.

My understanding of the temporal table: Create a table in Oracle, which is not used for other functions, mainly for some unique features of your own software system, and is useless when you run out of data in the table. After Oracle's temporary table is created, it does not occupy the table space, and if you do not specify an Oracle temporary table (including the index of the staging table), the data you insert into the temporary table is stored in the temporary table space (temp) of the Oracle system.

Creation of temporary tables

To create an Oracle staging table, you can have two types of temporary tables:

Session-Level Oracle TEMP Table

A temporary table at the transaction level.

1) session-level temporary table because the data in this temporary table is related to your current session, the data in the temporary table is still present when you do not exit, and when you exit the current session, the data in the temporary table is all gone. Of course, at this point, if you log in another session, you won't see the data inserted into the temporary table in the other session.

That is, the data inserted by the two different sessions is irrelevant. When a session exits, the data in the temporary table is truncated (TRUNCATE TABLE, the data is emptied). Session-level Temporal table creation method:

1.Create Global Temporary Table table_name

2. (Col1 type1,col2 Type2 ...) On Commit Preserve Rows;

Example:

1.create Global temporary table Student

2. (stu_id number (5),

3.CLASS_ID Number (5),

4.stu_name Varchar2 (8),

5.stu_memo varchar2 (+)) on Commit Preserve Rows;

2) A transaction-level temporary table is a transaction-related, when a transaction commits or a transaction is rolled back, the data in the Oracle staging table is truncated itself, and the other content and session-level temporal tables are consistent (including when exiting the session, the transaction-level temporary table is automatically truncated). How to create a transaction-level temporary table:

1.Create Global Temporary Table table_name

2. (Col1 type1,col2 Type2 ...) On Commit Delete Rows;

Example:

1.create Global temporary table Classes

2. (class_id number (5),

3.class_name Varchar2 (8),

4.class_memo varchar2 (+)) on Commit delete Rows;

3) Differences between the two types of temporary tables

The session-level temporary table takes on commit preserve rows, while the transaction level takes on commit delete rows, and the session level is only truncated when the session ends the temporary table, and the transaction-level temp table is either commit, ROLLBAC K or the end of the session, the data in the Oracle staging table will be truncated

4) When to use temporary tables

1), when an SQL statement is associated with a table of 2 and more, and is associated with some small table. A large table can be split and a smaller result set is stored in a temporary table

2), the program execution process may need to store some temporary data, the data in the whole process of the session is required to use and so on.

3. Example: slightly

4. Deficiencies of the temporary table

1) LOB objects are not supported, which may be based on the operational efficiency of the designer, but it is not possible to use the temporary table when the functionality is actually needed in the application.

2) Primary foreign key relationship not supported

So, for these reasons, we can create our own Oracle temporary tables to compensate for the shortcomings of the Oracle temp table

The above are my test, but the following is the search method on the Internet, I did not test, but I think the feasibility is very strong, there is time to test

To create a method:

1. Create the table structure of the temporary data table in the form of a regular table, but add a SessionID <NUMBER> column in the primary key of each table to distinguish between different sessions. (Can have LOB columns and primary foreign keys)

2. Write a user logoff trigger to delete all records inserted by this session when the user ends the session (SessionID equals the record of this session ID).

3. When the program writes data, it writes the current session ID (SessionID) to the table.

4. When the program reads the data, it only reads the same record as the current session ID.

Extended design with enhanced features:

1, you can set up a view on the data table, the view to record the filter condition is the current session of the SessionID.

2. The SessionID column in the data table can be implemented by trigger to achieve the transparency of the application layer.

3. Advanced users can access global data to achieve more complex functions.

Advantages of extending temporary tables:

1. The same functionality as Oracle's session-based Oracle TEMP table is implemented.

2, support Sdo_geometry and other LOB data types.

3. Support the primary foreign key connection between tables, and the primary foreign key connection is session-based.

4. Advanced users can access global data to achieve more complex functions

Oracle replication tables, staging tables, and views