时间戳

首页 > 解决方案 > 如何为多列创建sql分区？

oracle - 如何为多列创建sql分区？

问题描述

我尝试为表创建分区。我想为 2 个不同的列创建分区。可能吗？

所以这有效：

  ALTER TABLE MY)TABLE MODIFY
    PARTITION BY RANGE(SETTLEMENT_DATE) INTERVAL(NUMTODSINTERVAL(1,'day'))
( partition MY_PARTITION values less than (to_date('2019-06-01', 'yyyy-mm-dd')));

这是我想做的，但它不起作用：

  ALTER TABLE MY_TABLE MODIFY
    PARTITION BY RANGE(FILE_SUBMISSION_DATE_TIME) INTERVAL(NUMTODSINTERVAL(1,'day'))
( partition MY_PARTITION values less than (to_date('2016-06-01', 'yyyy-mm-dd'))),
    PARTITION BY RANGE(FILE_ACK_DATE_TIME) INTERVAL(NUMTODSINTERVAL(1,'day'))
( partition MY_PARTITION_2 values less than (to_date('2016-06-01', 'yyyy-mm-dd')));

标签： oracle

解决方案

您可以通过两种方式对两列进行分区：

  • 多列分区
  • 复合分区（一个分区列，一个子分区列）

多列分区

这使用第一列来选择在哪个分区中放置一行。第二列仅在存在平局时进行评估。这可能导致行位于您没想到的分区中：

create table t (
  c1 int, c2 int, c3 int
) partition by range ( c1, c2 ) (
  partition p0 values less than ( 1, 1 ),
  partition p1 values less than ( 2, 2 ),
  partition p2 values less than ( 3, 3 )
);

insert into t values ( 0, 0,  1 );
insert into t values ( 0, 99, 2 );
insert into t values ( 1, 1,  3 );

select * from t partition ( p0 );

C1    C2     C3   
    0     0     1 
    0    99     2

过滤列表中的第二列将最小化分区修剪：

alter session set statistics_level = all;
set serveroutput off

select * from t 
where  c2 = 0;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST +PARTITION'));

----------------------------------------------------------------------------------------    
| Id  | Operation                    | Name | Starts | E-Rows | Pstart| Pstop | A-Rows |    
----------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT             |      |      1 |        |       |       |      1 |    
|   1 |  PARTITION RANGE MULTI-COLUMN|      |      1 |      1 |KEY(MC)|KEY(MC)|      1 |    
|*  2 |   TABLE ACCESS FULL          | T    |      3 |      1 |KEY(MC)|KEY(MC)|      1 |    
----------------------------------------------------------------------------------------

（注意Starts = 3for TABLE ACCESS FULL；这意味着它读取所有分区）

复合分区

这是“两级”分区。数据库首先拆分顶级分区上的行。然后在第二层进一步细分。

此列与多列之间的一个关键区别是您可以为每个列使用不同的分区方法。例如范围哈希、哈希列表等。

create table t (
  c1 int, c2 int, c3 int
) partition by range ( c1 ) 
  subpartition by range ( c2 )  
  subpartition template (
    subpartition s0 values less than ( 1 ),
    subpartition s100 values less than ( 101 )
  ) (
  partition p0 values less than ( 1 ),
  partition p1 values less than ( 2 ),
  partition p2 values less than ( 3 )
);

insert into t values ( 0, 0,  1 );
insert into t values ( 0, 99, 2 );
insert into t values ( 1, 1,  3 );

select * from t partition ( p0 );

C1     C2     C3   
    0     0     1 
    0    99     2 

select * from t subpartition ( p0_s0 );

C1     C2     C3   
    0     0     1

同样，对子分区列的过滤会进行最小的分区修剪。数据库将首先扫描所有顶级分区。然后只修剪其中的子分区：

select * from t 
where  c2 = 0;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST +PARTITION'));

-----------------------------------------------------------------------------------    
| Id  | Operation               | Name | Starts | E-Rows | Pstart| Pstop | A-Rows |    
-----------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT        |      |      1 |        |       |       |      1 |    
|   1 |  PARTITION RANGE ALL    |      |      1 |      1 |     1 |     3 |      1 |    
|   2 |   PARTITION RANGE SINGLE|      |      3 |      1 |     1 |     1 |      1 |    
|*  3 |    TABLE ACCESS FULL    | T    |      3 |      1 |       |       |      1 |    
-----------------------------------------------------------------------------------

注意PARTITION RANGE SINGLE操作——这意味着数据库只访问了其中一个子分区。

根据您的分区方案和值分布，这可能会导致读取的行数显着减少。您需要尝试各种分区方法和边界，看看这是否对您有帮助。

推荐阅读