Oracle temporary table optimization query speed Abstract)

Oracle temporary table optimization query speed -- Aspx. CN technical data center operating system program design graphics image media animation office software database certification training
Web Development Network Principles network management network setup mechanical electronic Routing Technology

Current location: Aspx. cn Technical Information Center homepage> database> Oracle> experience exchange> Article content

Optimize Query speed for temporary Oracle tables

 

1. Preface

Currently, all applications that use ORACLE as the database support platform have a large amount of data, that is, the data volume of tables 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 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 ).
2. Create a temporary table
Create an oracle temporary table. There are two types of temporary tables: Session-level temporary tables and 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 (data is cleared. Create global temporary table table_name (col1
Type1, col2 type2. ..) on commit preserve rows; for 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) A transaction-level temporary table is a transaction-related temporary table. When a transaction is committed or rolled back, the data in the 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 ). Create a temporary transaction table
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) differences between the two types of temporary tables: in syntax, session-level temporary tables use on commit preserve rows, while transaction-level temporary tables use on commit
Delete
In rows usage, data in the session-level temporary table is truncated only when the session ends, and the transaction-level temporary table is whether it is commit, rollback, or session ends, data in the temporary table will be truncated.
3. Example:
1) At the session level (after the session is closed, the data will be lost. When the commit is used, the data will still be rolled back. When the rollback is used, the data will also be rolled back ):
Insert into student (stu_id, class_id, stu_name, stu_memo)
Values (, 'zhang san', 'fujian ');
Insert into student (stu_id, class_id, stu_name, stu_memo)
Values (2, 1, 'Liu dehua', '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 Zhang San Fujian
2 1 Andy Lau Fuzhou
3 2 s.h.e Xiamen
4 2 Zhang huimei Xiamen
SQL> commit;
Commit complete
SQL> select * from student;
Stu_id class_id stu_name stu_memo
----------------------
--------------------------------------------------------------------------------
1 Zhang San Fujian
2 1 Andy Lau Fuzhou
3 2 s.h.e Xiamen
4 2 Zhang huimei Xiamen
SQL> insert into student (stu_id, class_id, stu_name, stu_memo)
Values (, 'zhang huimei ', 'xiamen ');
1 row inserted
SQL> select * from student;
Stu_id class_id stu_name stu_memo
----------------------
--------------------------------------------------------------------------------
1 Zhang San Fujian
2 1 Andy Lau Fuzhou
3 2 s.h.e Xiamen
4 2 Zhang huimei Xiamen
4 2 Zhang huimei Xiamen
SQL> rollback;
Rollback complete
SQL> select * from student;
Stu_id class_id stu_name stu_memo
----------------------
--------------------------------------------------------------------------------
1 Zhang San Fujian
2 1 Andy Lau Fuzhou
3 2 s.h.e Xiamen
4 2 Zhang huimei Xiamen
SQL>
2) Transaction Level (delete data after commit): This example uses the following data:
Insert into classes (class_id, class_name, class_memo)
Values (1, 'computer ', '123 ');
Insert into classes (class_id, class_name, class_memo)
Values (2, 'economic information', '123 ');
Insert into classes (class_id, class_name, class_memo)
Values (3, 'economic information', '123 ');

Insert the above three records in a session (such as sqlplus login), and then log in with another session (use sqlplus again). When you select
* From
The classes table is empty when you log on to sqlplus for the first time, at this time, you can perform the update and delete operations on the three records you just inserted before the commit or rollback operation. When you perform the commit or rollback operation, at this time, because your table is a temporary transaction-level table, you will not be able to see the data during the data insertion session. At this time, the data has been truncated.
The running result is as follows:
SQL> insert into classes (class_id, class_name, class_memo)
Values (1, 'computer ', '123 ');
1 row inserted
SQL> insert into classes (class_id, class_name, class_memo)
Values (2, 'economic information', '123 ');
1 row inserted
SQL> insert into classes (class_id, class_name, class_memo)
Values (3, 'economic information', '123 ');
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>
Insert it again and then roll back.
SQL> rollback;
Rollback complete
SQL> select * from classes;
Class_id class_name class_memo
------------------
--------------------------------------------------------------------------------
SQL>

4. Application of temporary tables
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.
5. Notes:
1) The indexes of temporary tables and the modification and deletion of tables are consistent with those of normal tables.

2) Oracle temporary tables are only supported by Oracle8i. If your Oracle version is relatively low, you may not be able to use them, if your Oracle version is 8i, you also need to put init <oracle_sid> in the $ ORACLE_HOME/admin/$ {oracle_sid}/pfile directory. modify the "compatible" of the ora initial parameter configuration file to "compatible ".
= "8.1.0", which is configured on my server. You can also change it to compatible = "8.1.6"

The above are some of the methods I used to optimize large tables, with remarkable results.
Close Window]

A total of 252 people have been visiting from the beginning, and 74 people are currently online at the same time.