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 Zhang Huimei Xiamen
SQL> commit;
Commit completion
SQL> SELECT *.
STU_ID class_id stu_name stu_memo ------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ------ 1 1 Sanfujian 2 1 Liu Dehua Fuzhou 3 2 Shemate 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 *.
STU_ID class_id stu_name stu_memo ------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ------ 1 1 三 福建 2 1 Liu Dehua Fuzhou 3 2 She Xiamen 4 2 Zhang Huimei Xiamen 4 2 Zhang Huimei Xiamen
SQL> ROLLBACK;
Rollback Complete
SQL> SELECT *.
STU_ID class_id stu_name stu_memo ------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- ------ 1 1 Sanfujian 2 1 Liu Dehua Fuzhou 3 2 Shemate 4 2 Zhang Huimei Xiamen
SQL> 2), transaction grade (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,' Economic Information ',' 9603 '); in a session (such as Sqlplus login) Insert 3 records above, then log in with another session (once again logged over again), when you select * from classes; time, the classes table is empty, and you will be on the SQLUS that log in for the first time When SELECT, you can check it, this time you don't have a commission or rollback, you can perform Update, DELETE and other operations on the 3 records you just inserted. When you do commit or rollback, this time because your table is a transaction level. The temporary table, then the session inserted into the data can also see the data, 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, 'Economic 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 Classes;
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 deleded
SQL> SELECT * from Classes;
Class_id class_name class_memo -------- ------------------------------------- -------------------------------------------------- - 1 Computer 9608 2 Economic Information 9602SQL> Commit;
Commit completion
SQL> Select * from classes; 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, temporary table application 1), when a SQL statement is associated with 2 or more, and is 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. 2) Oracle's temporary table is the functional feature of Oracle8i, if your Oracle version is relatively low, then there may be no way to use, if your Oracle version is 8i, you still need to put $ Oracle_home / admin / $ {Oracle_sid} / pfile directory Under the initial configuration file compatible modified to compatible = "8.1.0", this is such a manager on the server. Of course, it can also be modified to compatible = "8.1.6", which is some means I use when optimizing the big table, is significant. Supplement: 1. When the temporary table is ON Commit Preserve Rows (session stage), in the current session, the table is time to DROP, Oracle Tips: Drop Table Student * Error At line 1: ORA-14452: Attempt to create, Alter or Drop An Index on Temporary Table Already in Temporary Table Already In Temporary Table Already IN TEMPORARY TABLE ALREADY IN TEMPORARY TABLE ALREADY IN Uses If you want to delete this table, the method has two: one is to exit this session, then log in again, then execute the drop table command; the other is in the current session Truncate, that is, analog to exit session, Oracle has to operate (truncated data), and then execute the DROP TABLE. 2. All temporary data will be stored under the current user's temporary table space (the table space can be performed when each user is created). 3. Creating a temporary episode can also specify a temporary table space.
