Create table for exchange

Patent metadata

Field	Value
Publication number	US-10657116-B2
Application number	US-201615228831-A
Country	US
Kind code	B2
Filing date	Aug 4, 2016
Priority date	Oct 19, 2015
Publication date	May 19, 2020
Grant date	May 19, 2020

Abstract

Techniques are described herein for creating a new database table for an exchange operation with a table partition of another table. A database management system (DBMS) is configured to receive a query such as “CREATE TABLE new_table FOR EXCHANGE FROM partition_P1”. The DBMS then identifies properties of the source partition (e.g., partition P1) and creates a new database table based on the properties identified. Some of these properties (e.g., hidden columns, data modeling frameworks, and table statistics) may not otherwise be directly accessible by the external entity that sent the query. The newly created table then has all of the necessary properties to be part of the other table if swapped with the source partition in an exchange operation.

First claim

What is claimed is:

1. A computer-implemented method comprising:
receiving a DDL statement referring to a first database table and specifying to create a table for an exchange with at least a portion of the first database table;
in response to receiving said DDL statement, creating a second database table having one or more physical properties of said first database table;
wherein said one or more physical properties define data organization within data blocks storing said first database table on persistent storage;
wherein said one or more physical properties include at least one of the following particular physical properties:
a physical column order between columns of said first database table within said data blocks of said first database table, and
a position within said data blocks for a column truncated from said first database table.

2. The method of claim 1 , further comprising:
prior to receiving the DDL statement, causing a first column in the first database table to be invisible; and
in response to receiving said DDL statement, creating the second database table with a second column that is invisible based on the first column being invisible.

3. The method of claim 1 , further comprising:
prior to receiving the DDL statement, causing a first column in the first database table to be hidden; and
creating the second database table with a second column that is hidden based on the first column being hidden.

4. The method of claim 1 , further comprising:
prior to receiving the DDL statement, causing a first column in the first database table to be virtual; and
in response to receiving said DDL statement, creating the second database table with a second column that is virtual based on the first column being virtual.

5. The method of claim 1 , further comprising:
prior to receiving the DDL statement, causing a first column in the first database table to be encrypted; and
in response to receiving said DDL statement, creating the second database table with a second column that is encrypted based on the first column being encrypted.

6. The method of claim 1 , wherein at least one physical property of the first database table is different than a corresponding logical property of the first database table; wherein a logical property is a table property as exposed to an entity external to a relational database management system that accesses the first database table.

7. The method of claim 1 , further comprising:
prior to receiving the DDL statement, changing a logical column order in the first database table without changing the physical column order; and
in response to receiving said DDL statement, creating the second database table with the same logical column order and physical column order as the first database table.

8. The method of claim 1 , further comprising:
prior to receiving the DDL statement, changing how many rows are stored in a given data block without changing a particular extra-logical property indicating a number of rows in the given data block; and
in response to receiving said DDL statement, creating the second database table with one or more of the same extra-logical properties as the first database table including the particular extra-logical property indicating the number of rows in the given data block.

9. The method of claim 1 , wherein the DDL statement refers to a partition with sub-partitions; and wherein the method further comprises: creating the second database table with one or more of the same extra-logical properties as the first database table including a partition criterion of the sub-partitions.

10. The method of claim 1 , further comprising:
determining physical column properties of the one or more physical properties of said first database table.

11. One or more non-transitory computer-readable media storing a set of instructions, wherein the set of instructions include instructions which, when executed by one or more hardware processors, cause:
receiving a DDL statement referring to a first database table and specifying to create a table for an exchange with at least a portion of the first database table;
in response to receiving said DDL statement, creating a second database table having one or more physical properties of said first database table;
wherein said one or more physical properties define data organization within data blocks storing said first database table on persistent storage;
wherein said one or more physical properties include at least one of the following particular physical properties:
a physical column order between columns of said first database table within said data blocks of said first database table, and
a position within said data blocks for a column truncated from said first database table.

12. The one or more non-transitory computer-readable media of claim 11 , wherein the DDL statement refers to a partition with sub-partitions, and the physical properties additionally include a partition criterion of the sub-partitions.

13. The one or more non-transitory computer-readable media of claim 11 , wherein the set of instructions further comprises instructions which, when executed by the one or more hardware processors, cause:
prior to receiving the DDL statement, causing a first column in the first database table to be invisible; and
in response to receiving said DDL statement, creating the second database table with a second column that is invisible based on the first column being invisible.

14. The one or more non-transitory computer-readable media of claim 11 wherein the set of instructions further comprises instructions which, when executed by the one or more hardware processors, cause:
a first column in the first database table to be hidden; and
creating the second database table with a second column that is hidden based on the first column being hidden.

15. The one or more non-transitory computer-readable media of claim 11 , wherein the set of instructions further comprises instructions which, when executed by the one or more hardware processors, cause:
prior to receiving the DDL statement, causing a first column in the first database table to be virtual; and
in response to receiving said DDL statement, creating the second database table with a second column that is virtual based on the first column being virtual.

16. The one or more non-transitory computer-readable media of claim 11 , wherein the set of instructions further comprises instructions which, when executed by the one or more hardware processors, cause:
prior to receiving the DDL statement, causing a first column in the first database table to be encrypted; and
in response to receiving said DDL statement, creating the second database table with a second column that is encrypted based on the first column being encrypted.

17. A computing device comprising:
one or more processors; and
volatile memory with instructions to execute a database server instance that is configured to:
receive a DDL statement referring to a first database table and specifying to create a table for an exchange with at least a portion of the first database table;
in response to receiving said DDL statement, create a second database table having one or more physical properties of said first database table;
wherein said one or more physical properties define data organization within data blocks storing said first database table on persistent storage;
wherein said one or more physical properties include at least one of the following particular physical properties:
a physical column order between columns of said first dat

Assignees

• Oracle Int Corp

Inventors

• Baer Hermann
• Kumar Ramesh
• Raghavan Ananth

Classifications

• G06F16/24554

  Unary operations; Data partitioning operations · CPC title

• G06F16/2282 Primary

  Tablespace storage structures; Management thereof · CPC title