Table Design

Comply with the following principles to properly design a table:

• [Notice] Reduce the amount of data to be scanned. You can use the pruning mechanism of a partitioned table.
• [Notice] Minimize random I/Os. By clustering or local clustering, you can sequentially store hot data, converting random I/O to sequential I/O to reduce the cost of I/O scanning.

Selecting a Storage Mode

[Proposal] Selecting a storage model is the first step in defining a table. The storage model mainly depends on the customer's service type. For details, see Table 1.

Table 1 Table storage modes and scenarios

Selecting a Partitioning Mode

If a table contains a large amount of data, partition the table based on the following rules:

• [Proposal] Create partitions on columns that indicate certain ranges, such as dates and regions.
• [Proposal] A partition name should show the data characteristics of a partition. For example, its format can be Keyword+Range characteristics.
• [Proposal] Set the upper limit of a partition to MAXVALUE to prevent data overflow.

The example of a partitioned table definition is as follows:

CREATE TABLE staffS_p1
(
  staff_ID       NUMBER(6) not null,
  FIRST_NAME     VARCHAR2(20),
  LAST_NAME      VARCHAR2(25),
  EMAIL          VARCHAR2(25),
  PHONE_NUMBER   VARCHAR2(20),
  HIRE_DATE      DATE,
  employment_ID  VARCHAR2(10),
  SALARY         NUMBER(8,2),
  COMMISSION_PCT NUMBER(4,2),
  MANAGER_ID     NUMBER(6),
  section_ID     NUMBER(4)
)
PARTITION BY RANGE (HIRE_DATE)
( 
   PARTITION HIRE_19950501 VALUES LESS THAN ('1995-05-01 00:00:00'),
   PARTITION HIRE_19950502 VALUES LESS THAN ('1995-05-02 00:00:00'),
   PARTITION HIRE_maxvalue VALUES LESS THAN (MAXVALUE)
);