表设计

总体上讲，良好的表设计需要遵循以下原则：

  • 减少需要扫描的数据量。通过分区表的剪枝机制可以大幅减少数据的扫描量。
  • 尽量减少随机I/O。通过聚簇可以实现热数据的连续存储，将随机I/O转换为连续I/O，从而减少扫描的I/O代价。

选择分区方案

当表中的数据量很大时，应当对表进行分区，一般需要遵循以下原则：

  • 使用具有明显区间性的字段进行分区，比如日期、区域等字段上建立分区。
  • 分区名称应当体现分区的数据特征。例如，关键字+区间特征。
  • 将分区上边界的分区值定义为MAXVALUE，以防可能出现的数据溢出。

表 1 表的分区方式及使用场景

分区方式 描述
Range 表数据通过范围进行分区。
Interval 表数据通过范围进行分区，超出范围的会自动根据间隔创建新的分区。
List 表数据通过指定列按照具体值进行分区。
Hash 表数据通过Hash散列方式进行分区。

典型的分区表定义如下：

``` 1. --创建Range分区表 2. CREATE TABLE staffS_p1 3. ( 4. staff_ID NUMBER(6) not null, 5. FIRST_NAME VARCHAR2(20), 6. LAST_NAME VARCHAR2(25), 7. EMAIL VARCHAR2(25), 8. PHONE_NUMBER VARCHAR2(20), 9. HIRE_DATE DATE, 10. employment_ID VARCHAR2(10), 11. SALARY NUMBER(8,2), 12. COMMISSION_PCT NUMBER(4,2), 13. MANAGER_ID NUMBER(6), 14. section_ID NUMBER(4) 15. ) 16. PARTITION BY RANGE (HIRE_DATE) 17. ( 18. PARTITION HIRE_19950501 VALUES LESS THAN ('1995-05-01 00:00:00'), 19. PARTITION HIRE_19950502 VALUES LESS THAN ('1995-05-02 00:00:00'), 20. PARTITION HIRE_maxvalue VALUES LESS THAN (MAXVALUE) 21. );

  1. --创建Interval分区表，初始两个分区，插入分区范围外的数据会自动新增分区
  2. CREATE TABLE sales
  3. (prod_id NUMBER(6),
  4. cust_id NUMBER,
  5. time_id DATE,
  6. channel_id CHAR(1),
  7. promo_id NUMBER(6),
  8. quantity_sold NUMBER(3),
  9. amount_sold NUMBER(10,2)
  10. )
  11. PARTITION BY RANGE (time_id)
  12. INTERVAL('1 day')
  13. ( PARTITION p1 VALUES LESS THAN ('2019-02-01 00:00:00'),
  14. PARTITION p2 VALUES LESS THAN ('2019-02-02 00:00:00')

  15. );

  16. --创建List分区表

  17. CREATE TABLE test_list (col1 int, col2 int)
  18. partition by list(col1)
  19. (
  20. partition p1 values (2000),
  21. partition p2 values (3000),
  22. partition p3 values (4000),
  23. partition p4 values (5000)

  24. );

  25. --创建Hash分区表

  26. CREATE TABLE test_hash (col1 int, col2 int)
  27. partition by hash(col1)
  28. (
  29. partition p1,
  30. partition p2
  31. ); ```

更多的表分区语法信息请参见CREATE TABLE PARTITION。