Orclae temp table

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, that is, 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 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: syntactically, session-level temporary tables use on commit preserve rows, while transaction-level temporary tables use on commit Delete rows, the data in the temporary table at the session level is truncated only when the session ends. In the temporary table at the transaction level, whether it is commit, rollback, or session ends, the data in the temporary table is 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 (, '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 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, 'economical information', '123 ');
Insert into classes (class_id, class_name, class_memo) values (3, 'economical 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 classes, the classes table is empty, and you can query it when you select the SQL plus table that you first log on, 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, 'economical information', '123 ');

1 row inserted

SQL> insert into classes (class_id, class_name, class_memo) values (3, 'economical 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. in the ora initial parameter configuration file, modify compatible to compatible = "8.1.0", which is the same 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.

 

 

 

Create procedure pro
As
STR varchar2 (100 );
Begin
STR: = 'Create Global temporary table tablename (
Col1 varchar2 (10 ),
Col2 number
) On commit preserve rows ';
Execute immediate STR; -- use dynamic SQL statements to execute
End;
/

DDL statements cannot be directly used in Stored Procedures, so dynamic SQL statements can only be used for execution.

-- On commit Delete rows indicates that the temporary table is specified by a transaction. After each commit, Oracle truncates the table (delete all rows)
-- On commit preserve rows indicates that the temporary table is specified by the session. When the session is interrupted, Oracle truncates the table.