Temporary tables, external tables, and partition tables in Oracle

Temporary tables, external tables, and partition tables in Oracle
Temporary table

In Oracle, a temporary table is "static". It only needs to be created once like a common data table. Its structure is valid from creation to deletion. Compared with other types of tables, a temporary table allocates space only when you add data to the table, and the allocated space comes from the temporary tablespace. This avoids data contention with permanent objects for storage space.

The syntax for creating a temporary table is as follows:

CREATE GLOBAL TEMPORARY TABLE table_name( column_name data_type,[column_name data_type,...] )ON COMMIT DELETE|PRESERVE ROWS;

Because the data stored in the temporary table is valid only during the current transaction processing or session
Therefore, temporary tables are divided into transaction-level temporary tables and session-level temporary tables.

Transaction-level temporary table

To create a transaction-level temporary table, you must use the on commit delete rows clause. Records of the transaction-level temporary table are automatically deleted after each transaction COMMIT.

Example 1:

CREATE GLOBAL TEMPORARY TABLE tbl_user_transcation( ID NUMBER, uname VARCHAR2(10), usex VARCHAR2(2), ubirthday DATE ) ON COMMIT DELETE ROWS;

Session-level temporary table

To create a session-level temporary table, you must use the on commit preserve rows clause. Session-level temporary table records are automatically deleted after the user is disconnected from the server.

Example 2:

CREATE GLOBAL TEMPORARY TABLE tbl_user_session( ID NUMBER, uname VARCHAR2(10), usex VARCHAR2(2), ubirthday DATE ) ON COMMIT PRESERVE ROWS;

Operate temporary tables

Insert a data entry to the transaction-level temporary table but not a COMMIT transaction:

INSERT INTO tbl_user_transcation VALUES(1,'siege','M',TO_DATE('1991-02-28','YYYY-MM-DD')); SELECT * FROM tbl_user_transcation;

The query result is as follows:

1 siege M 28/02/1991

If COMMIT is performed, no data in the table indicates that Oracle has deleted the data.

Insert a data entry to the temporary transaction table:

INSERT INTO tbl_user_session VALUES(1,'siege','M',TO_DATE('1991-02-28','YYYY-MM-DD')); SELECT * FROM tbl_user_session; COMMIT;

At this time, even if a transaction is committed, data still exists in tbl_user_session.
In this case, when the session is closed (the database is disconnected), the database is connected, and no data is found during the query.

Note: by default, PL/SQL Developer is configured to open a window, that is, re-create a session. Therefore, pay attention to setting the shared session,

External table

External tables are read-only tables provided by Oracle that can read data stored in the file system of the operating system. The data in the External table is stored in the file system of the operating system and can only be read and cannot be modified.

Create external table

Log On As SYSDBA and grant related permissions to the user:

 GRANT CREATE ANY DIRECTORY TO siege;

Then log on to the Created directory as a user:

 CREATE DIRECTORY external_student AS 'D:\';

Finally, create an external table:

Example 3:

CREATE TABLE tbl_external_student( sid NUMBER , sname VARCHAR2(10), sclass VARCHAR2(3), ssubject VARCHAR2(12), sscore NUMBER ) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY external_student ACCESS PARAMETERS(FIELDS TERMINATED BY ',') LOCATION ('student.csv') )

Note: The student.csv file under the external drive is as follows:

10001,siege,304,physics,80

The query tbl_external_student is consistent with the preceding display.

Partition Table

In large database applications, the amount of data to be processed can even reach TB level. To speed up reading, writing, and querying, Oracle provides a Partitioning technology that allows you to apply the Partitioning technology when creating a table and store data in partitions.

Partitioning refers to separating tables or indexes into relatively small and manageable parts. The partitioned table is no different from the unpartitioned table when executing the DML statement.

To partition a table, you must specify the partition for each record in the table. The partition of a record is determined by the matching field of the record in the partition table. A partition field can be a combination of one or more fields in a table. It is determined when you create a partition table. When you insert, update, or delete a partition table, Oracle automatically selects a partition based on the value of the partition field.