Skip to content
≡

Administrator

Developer

Tools

External Tools

CONJOIN TABLE

CONJOIN TABLE#

Syntax#

conjoin_table ::=#

row_movement_clause ::=, tablespace_clause ::=, physical_attributes_clause ::=, logging_clause ::=, lob_column_properties ::=

conjoin_range_clause ::=#

range_table_to_partition_clause ::=#

conjoin_list_clause ::=#

list_table_to_partition_clause ::=#

Prerequisites#

It is necessary to be satisfied one or more of the following conditions in order to create table:

  • The SYS user
  • The user has the CREATE TABLE or CREATE ANY TABLE system privilege in own schema
  • The user has the CREATE ANY TABLE system privilege in another user's schema.

It is necessary to be satisfied one or more of the following conditions in order to drop table:

  • The SYS user
  • Table's owner
  • The user has the DROP ANY TABLE system privilege.

Description#

More than one non-partitioned tables are grouped to create a partitioned and its associated partitions. A new partitioned table is created in the user's default tablespace unless the tablespace option is specified. The data located in existing non-partitioned table is transferred to a partition belonging to a new partitioned table, and the non-partitioned tables are removed.

conjoin_range_clause#

This clause enables to create a range partitioned table with multiple non-partitioned tables through the range partitioning.

conjoin_list_clause#

This clause enables to create a list partitioned table and multiple partitions are created through list partitioning method.

range_table_to_partition#

This clause specifies the name of the nonpartitioned table that is the target of the range partitioned table, the name of the partition to be created, and the range of values for the partitioning key.

list_table_to_partition#

This clause specifies the name of the nonpartitioned table that is the target of the list partitioned table, the name of the partition to be created, and the set of partitioning key values to be the base.

Precautions#

  • Do not specify the user name in front of the target table and partitioned table which will be converting.
  • After the CONJOIN, data that does not match the key value of the partition may exist on the partition.
  • A metatable related to a newly created partitioned table is created, and all the metatable in relation to the target table converted into a partitioned table is deleted.
  • PMS, packages, and views that are related to the target table cannot be used.
  • The hash partitioned table is not supported.
  • The schema created in partitioned tables must be identical with that of the target table. The target table must be identical with all the elements, such as the number of columns, name, sequence and data type, and in row and compressed logging option, as well as constraints of CHECK and NOT NULL.
  • There must not be hidden columns, security columns, and compressed columns in the target table.
  • Triggers operating with events must not exist in the target table.

Example#

<Query> Convert table t1 and t2 into a list partitioned table t3.

iSQL> conjoin table t3
partition by list(i1)
(
  table t1 to partition p1 values (1,2,3,4,5),
  table t2 to partition p2 values default
)
tablespace tbs3;
Conjoin success.