Oracle standby table

1. Preface
Currently, all applications that use Oracle for data processing are supported by Alibaba Cloud. Most of the systems with larger data volumes than other systems are supported, that is, the data volume of a table is generally larger than a hundred bytes. However, partition creation in Oracle is a non-optimal option, but when you find that your applications use multi-region table-related statements, and most of these tables are larger than zookeeper, the result set obtained after a certain region or a certain region table is closed is very small and the result set is obtained by checking the result set. very fast, at this time, I will explain how to create a "time table" in Oracle ".
My understanding of temporary tables: create a temporary table in Oracle. This table is not used for other functions and is mainly used only for some special functions of the component system, when you run out, the data in the table is useless. When the Oracle primary table is created, it basically does not occupy the table space. If you do not specify the primary table (including the index of the primary table) when the primary table is empty, when you insert data to an ORACLE system, the data volume of the table is stored in the temporary table space (TEMP) of the ORACLE system ).

2. Create a temporary table
When creating an Oracle temporary table, there can be two types of temporary tables: the temporary table and the actual time table of the transaction.

1) when you call the dynamic table, because the data in the current table is related to your previous statement, when the previous SESSION does not exit, the number of data records in the table still exists during the period, and when you exit the previous SESSION, when there is no data in the table, at this time, if you log on to another SESSION, you will not be able to see the data in the table when the other SESSION is inserted into the current SESSION. That is, the data records inserted by two different sessions are irrelevant. When a SESSION exits, the data volume in the table is truncated (truncate table, that is, the data volume is cleared.
When you call optimize, the Table creation method is: 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) When a transaction is executed, the table is related to the transaction. when the transaction is submitted or the transaction is returned, when the SESSION is closed, the data volume in the table is automatically intercepted. When other content is consistent with the response time table (including when the SESSION is exited, when an event occurs, the table is automatically intercepted ). 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) region of the two non-conforming time tables: The partition method, when a transaction occurs, the table uses on commit preserve rows, while the transaction uses on commit delete rows;
In usage, the data in the table will be intercepted only when the session ends, in addition, whether the table is commit, rollback, or the end of a transaction, the data volume in the table will be truncated at the specified time.

3. Example:
1) when the Session is closed, the data volume does not exist. When the Commit is used, the data volume is still there, when Rollback is used, the data volume is also returned ):
Insert into student (stu_id, class_id, stu_name, stu_memo) values (, 'fujian 3 ');
Insert into student (stu_id, class_id, stu_name, stu_memo) values (2, 1, 'mongode', 'fuzhou ');
Insert into student (stu_id, class_id, stu_name, stu_memo) values (3, 2,'s. H.E ', 'example secret ');
SQL> select * from student;

STU_ID CLASS_ID STU_NAME STU_MEMO
------------------------------------------------------------------------------------------------------
1. Fujian
2 1 Jude-based Fuzhou
3 2 S.H.E zookeeper
4 2 Hui Mei Yu

SQL> commit;

Commit complete

SQL> select * from student;

STU_ID CLASS_ID STU_NAME STU_MEMO
------------------------------------------------------------------------------------------------------
1. Fujian
2 1 Jude-based Fuzhou
3 2 S.H.E zookeeper
4 2 Hui Mei Yu

SQL> insert into student (stu_id, class_id, stu_name, stu_memo) values (, ' ',' ');

1 row inserted

SQL> select * from student;

STU_ID CLASS_ID STU_NAME STU_MEMO
------------------------------------------------------------------------------------------------------
1. Fujian
2 1 Jude-based Fuzhou
3 2 S.H.E zookeeper
4 2 Hui Mei Yu
4 2 Hui Mei Yu

SQL> rollback;

Rollback complete

SQL> select * from student;

STU_ID CLASS_ID STU_NAME STU_MEMO
------------------------------------------------------------------------------------------------------
1. Fujian
2 1 Jude-based Fuzhou
3 2 S.H.E zookeeper
4 2 Hui Mei Yu

SQL>
2) Division of data after a transaction Commit: This example uses the following data records:
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 on to another SESSION (with SQLPLUS again, when you select * from classes;, the classes table is empty, and you can find the rows when you select in SQLPLUS for the first time, at this time, you can perform the update and delete operations on the three records that can be inserted before the commit or rollback operation, when you perform commit or rollback, because your table is a complex table, the data cannot be viewed in the inserted session, and the data volume is intercepted.
The 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>
Re-insert the record 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 related to a table of 2 or above, it is related to some small tables. You can split a large table and obtain a result set smaller than the limit value.
2) The Program program may need to store some historical data during the process, and these data needs to be used in the speech process of the whole program.
5. Notes:
1) the indexes of the table at the specified time and the modification and division of the table are consistent with those of the normal table.
2) Oracle's temporary table is a feature supported by Oracle8i. If your Oracle version is lower than the latest version, it may be useless, if your Oracle version is 8i, you also need to put init <ORACLE_SID> under $ ORACLE_HOME/admin/$ {ORACLE_SID}/pfile. the compatible in the ora initial data configuration file is changed to compatible = "8.1.0", which is the same as that in my server. You can also change it to compatible = "8.1.6"

The above are some of the methods I used when making large tables more efficient.