Policies for creating tables

Oracle tables consist of heap tables, external tables, index organized tables, temporary tables, object and object tables, partitioned tables, clustered tables, and hash tables.

Heap table

Heap tables are the most commonly used plain tables, where data is stored in no order and the first appropriate space found by Oracle is used when adding data.

Statement:

Create heap table
create tableTable name (field name1Whether the field type (length) is empty, the field name1Field type (length) nulltablespace TSDAT02                                         
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

Add primary key
alter tableThe name of the tableadd constraintThe main primary keyskey(field name);-- Add foreign keys
alter tableThe name of the tableadd constraintOutside the foreign keyskey(Field name)referencesAssociated table (field name);- increase the column
alter tableThe name of the tableaddField name type;- modify column
alter tableThe name of the tablemodifyField name type;Add comments to columns
comment on columnTable. Field nameIS 'comments';

-- Change the column name
alter tableThe name of the tablerenameCloumn old column namestoThe new column names;Alter table name
renameThe name of the tabletoThe new name of the table;-- Drop column names
alter tableThe name of the tabledrop columnThe field name.Drop table data
truncate tableThe name of the table.delete fromThe name of the table.- delete table
drop tableThe name of the table.Copy the code

The partition table

In large database applications, users need to deal with a large amount of data. Oracle provides partitioned tables to make read, write, and query operations faster. A partitioned table is a partition of a very large table into smaller pieces (partitions). In practice, operations on partitioned tables are performed on separate partitions, but to the user a partitioned table works like a table.

Oracle provides five partitioning methods for a table or index: range partitioning, hash partitioning, list partitioning, combined range partitioning, and combined range list partitioning. Each partition has its own characteristics.

Range partitioning

create table t47_transaction(
       id varchar2(20) primary key,
       item varchar2(300),
       dt date not null,
       state varchar2(20))
       partition by range (id) (
       partition part_01 values less than (10000) tablespace users.partition part_02 values less than (30000) tablespace sysaux.partition part_03 values less than (maxvalue) tablespace system
);
-- Divide the partition into 3 partitions according to the number. The first partition stores 10000 pieces of data, and the second partition stores 20000 pieces of data.
The remaining data will be stored on the third partition
Copy the code

Hash partitioning

Hash partitioning is a type of partitioning that distributes data evenly through a hash algorithm. These partitions can be made consistent by hashing partitions on I/O devices.

-- hashed by ID in the specified 3 tablespaces
create table t47_transaction(
       id varchar2(20) primary key,
       item varchar2(300),
       dt date not null,
       state varchar2(20))
       partition by hash (id) (
       partition part_01 tablespace users.partition part_02 tablespace sysaux.partition part_03 tablespace system
);
Copy the code

A list of partition

If the value of the partition is a non-numeric or date data type, and the value range of the partition column is only a collection of a few values, the table can be partitioned. You need to specify a list of values for each partition, and the rows of the partitioned column in the same list of values are stored in the same partition

Create table T47_TRANSACTION (id VARchar2 (20) primary key, item VARchar2 (300), dt date not null, state varchar2(20)) partition by list (state) ( partition part_01 values ('Beijing'.'tianjin') tablespace users,
       partition part_02 values ('zhoukou'.Zhengzhou ' ') tablespace sysaux
);
Copy the code

Combined range hash partitioning

After partitioning by range, it is sometimes necessary to distribute the data for each partition in several table Spaces of the hash to form a combined range hash partition.

Partition by time. Data in each partition can be 3 subpartitions. Create table T47_TRANSACTION (ID VARchar2 (20) primary key, item VARchar2 (300), DT date not NULL, state varchar2(20)) partition by range (dt) subpartition byhash (id)
       subpartitions 3 store in (users, sysaux, system) (
       partition part_01 values less than (date'2017-06-22'),
       partition part_02 values less than (date'2018-06-22'),
       partition part_03 values less than (maxvalue)
);
Copy the code

Group range list partitions

Combining range list partitioning combines range partitioning and list partitioning techniques, first with range partitioning and then with list partitioning techniques for each individual range partition. Unlike the combined range hash partition, the contents of each subpartition in the combined range list partition represent a logical subset of data, described by the appropriate range and list partition Settings.

Split table into two partitions Create table T47_TRANSACTION (ID VARchar2 (20) primary key, item VARchar2 (300), DT date not NULL, state varchar2(20)) partition by range (id) subpartition by list (state) ( partition part_01 values less than (10000) ( subpartition part_01_1 values ('Beijing'.'tianjin') tablespace users,
                 subpartition part_02_1 values ('zhoukou'.Zhengzhou ' ') tablespace sysaux),
       partition part_02 values less than (maxvalue) (
                 subpartition part_01_2 values ('Beijing'.'tianjin') tablespace users,
                 subpartition part_02_2 values ('zhoukou'.Zhengzhou ' ') tablespace sysaux)
);
Copy the code

Two more good partition posts:

Blog.csdn.net/tanzuai/art…

Blog.csdn.net/liang_henry…