1. Foreword Currently all applications using Oracle as a database support platform, most data volumes are larger, that is, the amount of data of the table is in a million-level data volume. Of course, create partitions in Oracle is a good choice, but when you find that your app is related to multiple tables, and most of these tables are large, and you find some of them when you are associated. The result set obtained after a few tables is very small and the query gets the speed of this result set is very fast, then this time I consider creating a "temporary table" in Oracle. My understanding of the temporary table: Create a table in Oracle, this table is not used for other features, mainly used for some unique features of their own software system, and when you are running out, the data is not used. Oracle's temporary table creation, basically does not take place, if you do not specify a temporary table (including the index of the temporary table), the data you inserted into the temporary table is stored in the temporary table space in the Oracle system. TEMP). 2. Creating a temporary table Create an Oracle Temporary Table that there are two types of temporary tables: a temporary table of session levels and transaction-level temporary tables. 1) Interim tables of the session level Because the data in this temporary table is related to your current session, when you currently send it, the data in the temporary table exists, and when you quit the current session The data in the temporary table is all, Of course, if you log in with another session, you can't see the data in the temporary table in another session. That is, the data inserted by two different sessions is inconsistent with each other. The data in the temporary table is trunched after a session exit (Truncate Table, which is emptied). Session level temporary table creation method: Create Global Temporary Table Table_Name (Col1 Type1, Col2 Type2 ...) On Commit Preserve Rows; Example create global temporary table Student (Stu_id Number (5), Class_id Number (5), Stu_Name Varchar2 ( 8), STU_MEMO VARCHAR2 (200)) on commit preserve rows; 2) Transaction-level temporary table refers to this temporary table related to transactions, when transaction submission or transaction rollback, the data in the temporary table is truncated, Other contents and interim tables of session levels (including exiting session, transaction-level temporary tables are automatically truncated). The method of creating temporary transaction-level table: Create Global Temporary Table Table_Name (Col1 Type1, Col2 Type2 ...) On Commit Delete Rows; Example: create global temporary table Classes (Class_id Number (5), Class_Name Varchar2 (8), Class_Memo varchar2 (200)) On Commit Delete Rows; 3), two types of temporary tables: grammar When the data in the session ends the temporary table is truncated, and the transaction-level temporary table, regardless of the CommT, Rollback or the end of the session, the data in the temporary table will be truncated.
3, example: 1), session level (session is closed), when commits, the data is still, when Rollback is time, the data is also rolled back): INSERT INTO Student (stu_id, class_id, stu_name , STU_MEMO) VALUES (1, 1, 'Zhang 3', 'Fujian'); Insert Into Student (stu_id, class_id, stu_name, stu_memo) Values (2, 1, 'Andy Lau),' Fuzhou '; Insert Into Student Stu_id, class_id, stu_name, stu_memo) Values (3, 2, 'She', 'Xiamen'); SQL> SELECT *WM Student; STU_ID Class_ID Stu_name Stu_Memo -------------- ------------------------------------------------- ------------------------------------- 1 1 3 三 福建 2 1 Liu Dehua Fuzhou 3 2 Shemate 4 2 惠 妹 SQL> Commit; commit complete sql> select * from student; stu_id class_id stu_name stu_memo ------ --------------------- -------------------------------------------------- ----------------------- 1 1 三 福建 2 1 Liu Dehua Fuzhou 3 2 Shemen 4 2 Zhang Huimei Xiamen SQL> Insert Into Student (stu_id, class_id, STU_NAME, STU_MEMO) VALUES (4, 2, 'Zhang Huimei', 'Xiamen'); 1 ROW Inserted SQL> SELECT * From student; stu_id class_id stu_name stu_memo ----------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- --------- 1 1 张 福建 2 1 Liu Dehua Fuzhou 3 2 Shemate 4 2 Zhang Huimei Xiamen 4 2 Zhang Huimei Xiamen SQL> Rollback; Rollback Complete SQL> SELECT * from stay_ID Class_ID stu_name stu_memo --- ---------- ------------------------------------- ------------------------------------------------- 1 1 Sanfujian 2 1 Liu Dehua Fuzhou 3 2 She Xiamen 4 2 Zhang Huimei Xiamen SQL> 2), transaction class (
After commit, delete data): This example will use the following data: INSERT INTO CLASSES (Class_ID, Class_Name, Class_Memo) Values (1, 'Computer', '9608'); Insert Into Classes (Class_ID, Class_name, Class_Memo) VALUES 2, 'Economic Information', '9602'); Insert Into Classes (Class_ID, Class_name, Class_Memo) VALUES (3, 'Accident', '9603'); in one session (such as SQLPLUS login) inserts 3 records above 3 records , Then log in with another session (once again logged over again), when you select * from classes;, the classes table is empty, and you can query when you log in to SQLUS in SQLUS, this You can do Update, DELETE, etc. before you don't have commit or rollback, you can do Update, Delete, etc. The session also couldn't see the data. At this time, the data has been truncated.
The results are as follows: SQL> INSERT INTO CLASSES (Class_ID, Class_name, Class_Memo) VALUES (1, 'Computer', '9608'); 1 ROW INSERTED SQL> Insert Into Classes (Class_ID, Class_name, Class_Memo) Values (2, ') Information ',' 9602 '); 1 ROW INSERTED SQL> INSERT INTO CLASSES (Class_ID, Class_Name, Class_Memo) Values (3,' Economic Information ',' 9603 '); 1 ROW INSERTED SQL> Update Classes Set Class_Memo =' Where Class_id = 3; 1 ROW Updated SQL> Select * from class; class_id class_name class_memo -------- ----------------------- -------------------------------------------------- --------------- 1 Computer 9608 2 Economic Information 9602 3 Economic Information SQL> Delete from Classes Where Class_ID = 3; 1 Row Deleted SQL> SELECT * from Classes; Class_ID Class_name Class_Memo - ------ ------------------------------------------ ---------------------------------------------- 1 Computer 9608 2 Economic Information 9602SQL> Commit; Commit Complete Sql> Select * from Class; Class_ID Class_name Class_Memo ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ------------------------------ SQL> Repeat once again, then rollback. SQL> ROLLBACK; ROLLBACK Complete SQL> Select * from classes; class_id class_name class_memo -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- -------------- SQL> 4 Application 1), when a SQL statement is associated with tables, and associated with some small tables. The large table can be used to split a large table and a smaller result set is stored in a temporary table. 2) The program execution may need to store some temporary data, which require the use of the entire program. 5. Precautions: 1) The index of the temporary table and the modification of the table, delete, etc., and normal tables are consistent.
