Copy a table to another table, view, and temporary table

Copy a table to another table, view, and temporary table. create a new_table table with the same structure as an old_table table without the old_table record. create table new_table as select * from old_table where 1 = 0; create a table new_table with the same structure as the old_table table (with records of old_table) create table new_table as select * from old_table; www.2cto.com copy one table to another table insert into new_table select * from old_table; create or replace view ** _ view as select * from ** table; drop view ** _ view; create temporary table create global temporary Experience of table tablename on commit preserve rows as select * from others_table www.2cto.com Oracle temporary table practical usage experience the temporary table can only be valid in the current connection; temporary tables do not create indexes, therefore, when the data volume is large or multiple queries are performed, it is not recommended to use it. When the data processing is complex, the table is faster, and the view is faster; when only querying data, it is recommended to use a cursor; 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 ". Www.2cto.com my understanding of temporary tables: Create a table in Oracle. This table is not used for any other functions and is mainly used for some special functions of your 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 ). Create an Oracle temporary table for creating temporary tables. There are two types of temporary tables: Session-level temporary tables for Oracle temporary table transaction-level temporary tables. 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_Name2. (Col1 Type1, Col2 Type2 ...) on Commit Preserve Rows; example: 1. create global temporary table Student2. (Stu_id Number (5), 3. class_id Number (5), 4. stu_Name Varchar2 (8), 5. stu_Memo varchar2 (200) on Commit Preserve Rows; www.2cto.com 2) Transaction-level temporary tables are transaction-related. When a transaction is committed or rolled back, the data in the temporary Oracle table will be truncated on its own, and the other contents will be consistent with the temporary table at the SESSION level (including when the SESSION is exited, the temporary table at the transaction level will also be automatically truncated ). How to create a temporary transaction table: 1. create Global Temporary Table Table_Name2. (Col1 Type1, Col2 Type2 ...) on Commit Delete Rows; example: 1. create global temporary table Classes2. (Class_id Number (5), 3. class_Name Varchar2 (8), 4. class_Memo varchar2 (200) on Commit delete Rows;
3) differences between two types of temporary tables: on commit preserve rows for session-level temporary tables, and on commit delete rows for transactions, session-level temporary table data is truncated only when the session ends. In a transaction-level temporary table, whether it is commit, rollback, or session ends, data in the Oracle temporary table will be truncated www.2cto.com 4) when to use temporary table 1) When an SQL statement is associated with two or more tables and associated with some small tables. You can split a large table and store a small result set in the temporary table. 2) some temporary data may need to be stored during program execution, this data needs to be used throughout the program session. 3. example: 4. temporary table deficiency 1) does not support lob objects, which may be based on the running efficiency of the designer. However, when this function is required in practical applications, the temporary table cannot be used. 2) The primary and foreign key relationships are 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, however, the method found on the internet is as follows: 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, when the user ends the session, all records inserted by the session are deleted (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 Design for enhanced functions of www.2cto.com: 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 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