Oracle temporary table optimization query speed

oracle| temporary table | speed | optimization
1. Preface
At present, all use Oracle as database support platform application, most of the data volume of the system, that is, table data volume is generally in millions above data volume. Of course it's a good choice to create partitions in Oracle, but when you find that your application has more than one table associated, and most of these tables are large, and when you're connected, you find that one or several tables are associated with a very small result set and the query gets the result set very quickly, So this time I consider creating a "temp table" in Oracle.
My understanding of the temporary table: Create a table in Oracle that doesn't need any other functionality, is used primarily for some specific features of its own software system, and is useless when you run out of data in a table. When Oracle's temporary tables are created, they do not occupy the table space, and if you do not specify the empty table (including the index of the temporary table), the data you insert into the temporary table is stored in the temporary tablespace of the Oracle System (TEMP).
2, the creation of temporary tables
To create an Oracle temporary table, you can have two types of temporary tables: temporary tables at the session level and temporary tables at the transaction level.
1 session-level temporary tables because the data in this temporary table is related to your current session, when your current session does not exit, the data in the temp table still exists, and when you exit the current session, the data in the temp table is all gone. Of course, at this time, if you log in with another session, you cannot see the data inserted into the temporary table in another session. 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, the data 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) on Commit Preserve Rows;
2 A transaction-level temporary table means that the temporary table is related to the transaction, when a transaction commits or a transaction is rolled back, the data in the temporary table is truncated to itself, and the other content is consistent with the temporary table at the session level (including the temporary table at the transaction level is automatically truncated when the sessions are exited). Creation of a transaction-level temporary table: Create Global temporary table table_name (Col1 type1,col2 Type2 ...) On Commit Delete Rows, for example: Create global temporary table Classes (class_id number (5), class_name Varchar2 (8), Class_memo VARCHAR2 () on Commit delete Rows;
3, the difference between two types of temporary tables: syntactically, the session-level temporary table takes an on commit preserve rows and the transaction level uses on commit delete rows; The session level is truncated only when data in the temporary table of the session ends. The data in the temporary table is truncated, regardless of the commit, rollback, or session end of the transaction-level temporary table.
3. Example:
1. Session level (the data will be gone after it is closed), when the data is still in the commit, when the rollback is the same as the data is rolled back:
INSERT into student (Stu_id,class_id,stu_name,stu_memo) VALUES (1, 1, ' John ', ' 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, ' S.H.E ', ' Xiamen ');
sql> Select *from Student;

stu_id class_id Stu_name Stu_memo
------ -------- -------- --------------------------------------------------------------------------------
1 1 Zhang three Fujian
2 1 Andy Lau Fuzhou
3 2 S.H.E Xiamen
4 2 Sister Xiamen

Sql> commit;

Commit Complete

Sql> select * from student;

stu_id class_id Stu_name Stu_memo
------ -------- -------- --------------------------------------------------------------------------------
1 1 Zhang three Fujian
2 1 Andy Lau Fuzhou
3 2 S.H.E Xiamen
4 2 Sister Xiamen

Sql>insert into student (Stu_id,class_id,stu_name,stu_memo) VALUES (4,2, ' Sherry ', ' Xiamen ');

1 row inserted

Sql> select * from student;

stu_id class_id Stu_name Stu_memo
------ -------- -------- --------------------------------------------------------------------------------
1 1 Zhang three Fujian
2 1 Andy Lau Fuzhou
3 2 S.H.E Xiamen
4 2 Sister Xiamen
4 2 Sister Xiamen

sql> rollback;

Rollback Complete

Sql> select * from student;

stu_id class_id Stu_name Stu_memo
------ -------- -------- --------------------------------------------------------------------------------
1 1 Zhang three Fujian
2 1 Andy Lau Fuzhou
3 2 S.H.E Xiamen
4 2 Sister Xiamen

Sql>
2, transaction level (delete data after commit): 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 the above 3 records, and then another session (with Sqlplus login again) landing, when you select * from classes, classes table is empty, The first time you log in the Sqlplus Select can be queried, this time you do not commit or rollback before you can be inserted in the 3 records of the update, delete and other operations, When you make a commit or a rollback, this time because your table is a transaction-level temporary table, then the data inserted in the session can not see the data, this time the data has been truncated.
The results of the operation 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 deleted

Sql> select * from classes;

class_id class_name Class_memo
-------- ---------- --------------------------------------------------------------------------------
1 Computer 9608
2 Economic Information 9602
Sql> commit;

Commit Complete

Sql> Select *from classes;

class_id class_name Class_memo
-------- ---------- --------------------------------------------------------------------------------

Sql>
Repeat the insertion once more and then rollback.
Sql> Rollback;

Rollback Complete

Sql> select * from classes;

class_id class_name Class_memo
-------- ---------- --------------------------------------------------------------------------------

Sql>

4, the application of temporary table
1, when the table associated with a SQL statement is 2 and above, and is associated with some small tables. A large table can be split and a smaller set of results is stored in a temporary table.
2, the program execution process may need to store some temporary data, this data in the whole process of the session need to use and so on.
5, Attention matters:
1, the index of the temporary table as well as the changes to the table, delete and so on and the normal table is consistent.
2, Oracle's temporary table is oracle8i to support the functional features, if your Oracle version is lower, then there may be no way to use, if your Oracle version is 8i, you still need to $oracle_home/admin/${ The compatible of the Init<oracle_sid>.ora initial parameter configuration file under the Oracle_sid}/pfile directory is modified to compatible = "8.1.0", which is the configuration of my server. Of course, can also be modified to compatible = "8.1.6"

The above is I in the big table to optimize the time to adopt some means, the effect is remarkable.