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 the tablespace for storing the temporary table (including the index of the temporary table, 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.
Create global temporary table mikemao on commit Delete rows
Select * From wms_chargeitem;
