Oracle copy tables, temporary tables, and views

Oracle copy tables, temporary tables, and views
Create a new_table table with the same structure as the old_table Table (no old_table Records)

Create table new_table as select * from old_table where 1 = 0;

Create a new_table table with the same structure as the old_table Table (records with old_table)

Create table new_table as select * from old_table;

Copy one table to another

Insert into new_table select * from old_table;

Create and delete views

Create or replace view ** _ view as select * from ** table;
Drop view ** _ view;

Create temporary table

Create global temporary table tablename on commit preserve rows as select * from others_table

Experience in using temporary Oracle tables

Temporary tables can only be valid in the current connection;

Temporary tables are not indexed, so it is not recommended when the data volume is large or multiple queries are performed;

When data processing is complex, tables are faster, while views are faster;

We recommend that you use a cursor when only querying data;

Currently, Oracle is used as the actual application of the database support platform. It can be said that the data volume is still a relatively large system, that is, the table data volume is generally more than one million data volume.

Of course, creating partitions in Oracle is a good choice, but when you find that your application has multiple table associations, and most of these tables are large, when you associate a table, you find that the result set obtained after one or more tables are joined is very small and the query speed for this result set is very fast, in this case, I want to create a "temporary table" in Oracle ".

My understanding of temporary tables: Create a table in Oracle. This table is not used for any other functions, but mainly used for some special functions of the software system, when you run out, the data in the table is useless. After creating a temporary Oracle table, it basically does not occupy the tablespace. If you do not specify that the temporary Oracle table (including the index of the temporary table) is empty, the data you insert into the temporary table is stored in the temporary tablespace (TEMP) of the Oracle system ).

Temporary table Creation

Create an Oracle temporary table. There are two types of temporary tables:

Session-level temporary Oracle table

Transaction-level temporary table.

1) SESSION-level temporary table because the data in this temporary table is related to your current SESSION. When your current SESSION does not exit, the data in the temporary table will still exist, when you exit the current SESSION, all the data in the temporary table is lost, of course, if you log on to another SESSION at this time, you will not be able to see the data inserted into the temporary table in another SESSION.

That is, the data inserted by two different sessions is irrelevant. When a SESSION exits, the data in the temporary table is truncated (truncate table, that is, data is cleared. Session-level temporary 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 (200) on Commit Preserve Rows;

2) A transaction-level temporary table is a transaction-related temporary table. When a transaction is committed or rolled back, data in the Oracle temporary table will be truncated by itself, the other content is consistent with the SESSION-level temporary table (including when the SESSION is exited, the transaction-level temporary table will be automatically truncated ). How to create a temporary transaction 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 (200) on Commit delete Rows;

3) differences between the two temporary tables

Session-level temporary tables use on commit preserve rows, while transaction-level temporary tables use on commit delete rows. In usage, session-level data is truncated only when the session ends temporary tables, in addition, the data in the temporary transaction table will be truncated, whether it is commit, rollback, or session termination.

4) when to use a temporary table

1) When an SQL statement is associated with two or more tables, it is associated with some small tables. You can split a large table and store a small result set in a temporary table.

2) some temporary data may need to be stored during program execution, which is required throughout the program session.

3. Example: omitted

4. Limitations of temporary tables

1) the lob object is not supported. This may be the consideration of the designer's running efficiency, but temporary tables cannot be used when this function is required in actual applications.

2) The primary/foreign key relationship is not supported.

Therefore, for the above reasons, we can create a temporary Oracle table by ourselves to make up for the shortcomings of the temporary Oracle table.

The above are all tested by myself, but below is the method found on the Internet. I have not tested it, but I think it is very feasible. I have time to test it.

Creation method:

1. Create the table structure of the temporary data table in the form of a regular table, but add a SessionID <NUMBER> column to the primary key of each table to differentiate sessions. (Lob columns and primary foreign keys can be included)

2. Write a user logout trigger to delete all records inserted in the session when the user ends the session (SessionID is equal to the session ID ).

3. When writing data, the program writes the current session ID to the table.

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

Extended feature design:

1. You can create a view on the data table. The view filters records based on the SessionID of the current session.

2. The SessionID column in the data table can be implemented through Trigger to achieve transparency to the application layer.

3. Advanced users can access global data for more complex functions.

Advantages of extended temporary tables:

1. implements the same features as Oracle session-based temporary Oracle tables.

2. Supports lob data types such as SDO_GEOMETRY.

3. Supports primary and foreign key connections between tables, and the primary and foreign key connections are also session-based.

4. Advanced users can access global data for more complex functions