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

Temporary tables, external tables, and partition tables in Oracle,
Temporary tables, external tables, and partition tables in OracleTemporary 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 dividedTransaction-level temporary tableAndSession-level temporary table.

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

PairTransaction-level temporary tableInsert a data entry 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.

PairTransaction-level temporary tableInsert a data entry:

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.

Oracle Database provides five partitioning methods for tables or indexes:Range partition, hash partition, list partition, combination range hash partition, and combination range list partition.

Range Partition

A range partition is used to partition the range of a value in a data table, for example, based on the value size. To create a range partition, use the partition by range clause.

If no partition name is specified, Oracle automatically names the partitions.

Example 4:

CREATE TABLE tbl_range_partition(       id NUMBER PRIMARY KEY,       name VARCHAR2(10),       subject VARCHAR2(8),       score NUMBER)PARTITION BY RANGE(score)(          PARTITION part1 VALUES LESS THAN(60) TABLESPACE learning,          PARTITION part2 VALUES LESS THAN(80) TABLESPACE learning,          PARTITION part3 VALUES LESS THAN(MAXVALUE) TABLESPACE learning);

Note: Only enterprise databases support partitioning.

Query by partition:

SELECT * FROM tbl_range_partition PARTITION(part1);

Hash Partition

HASH algorithms are used to evenly distribute data in a partition type. HASH partitioning is performed on an I/O device to ensure that the partition size is consistent. To create a hash partition, you must use the partition by hash clause.

Example 5:

CREATE TABLE tbl_hash_partition(       bid NUMBER(4),       bookname VARCHAR2(10),       bookprice NUMBER(4,2),       booktime DATE)PARTITION BY HASH(bid)(          PARTITION part1 TABLESPACE learning,          PARTITION part2 TABLESPACE learning);

Query by partition:

SELECT * FROM tbl_hash_partition PARTITION(part1);

List Partition

It is applicable to non-numeric or date data types of partition columns, and is used when the value range of partition columns is small. For example, a list partition can be applied if the number of subject columns in the tables Table is small. To create a list partition, use the partition by list clause.

When partitioning a list, you must specify a value list for each partition. the rows in which the values of the partition column are in the same list are stored in the same partition.

Example 5:

Create table tbl_list_partition (bid NUMBER (4), bookname VARCHAR2 (10), bookpress VARCHAR2 (30), booktime DATE) partition by list (bid) (PARTITION part1 VALUES ('a Publishing House ') TABLESPACE learning, PARTITION part2 VALUES (' B Publishing House ') TABLESPACE learning );

Query by partition:

SELECT * FROM tbl_list_partition PARTITION(part1);

Manage partition tables Add Partition

To add a partition for a partition, use alter table... Add partition statement. You can add partitions in the following situations:

• Add partitions to a range Partition Table

  There are two scenarios: Add a partition after the last partition and add a partition in the middle or beginning of the partition.

  Example 6:

  ALTER TABLE tbl_range_partition ADD PARTITION part3 VALUES LESS THAN(150);

  Example 7:

  ALTER TABLE tbl_range_partition SPLIT PARTITION part2 AT(70) INTO(PARTITION part6,PARTITION part7);

• Add partitions to a hash Partition Table

  You only need to use the alter table add partition statement. Oracle automatically balances the capacity between existing partitions and new partitions.

  Example 8:

  ALTER TABLE tbl_hash_partition ADD PARTITION part3;

• Add partitions to list partitions

  Add a new partition to the list partition table. You must use the VALUES clause to specify the value list for the partition table when creating the list partition.

  Example 9:

  ALTER TABLE tbl_list_partition ADD PARTITION part3 VALUES(default); 

  Merge partitions

  To merge partitions, use alter table... The merge partition statement. For example, merge the partitions of the previously created tbl_range_partition table part6 and part7, as shown below:

  Example 10:

  ALTER TABLE tbl_range_partition MERGE PARTITIONS part6,part7 INTO PARTITION part2;

  Delete Partition

  To delete a partition, use alter table... DROPPARTITION statement. For example, delete the part3 partition of the previously created tbl_range_partition Table, as follows:

  Example 11:

  ALTER TABLE tbl_range_partition DROP PARTITION part3;