主页 / dba / 问题 / 30346
Accepted
sjk
Asked: 2012-12-14 02:02:43 +0800 CST

使用多个日期列进行分区修剪

  • 772

我在 Oracle 11g 数据库中有一个大表，其中包含几年的历史数据，所以我想按年份对其进行分区。问题是该表有多个日期列并且它们都用于查询，所以我不能只选择一个日期列并将其用作分区键。

大多数时间日期彼此接近，因此我为每一年创建了分区，加上一个“溢出”分区，其中包含跨越年份边界的行。这是一个简化的示例：

create table t (
  start_year int,
  end_year int,
  partition_year int as (case when start_year=end_year then start_year else 0 end),
  data blob 
)
partition by range(partition_year) (
  partition poverflow values less than (1000),
  partition p2000 values less than (2001),
  partition p2001 values less than (2002),
  partition p2002 values less than (2003),
  partition p2003 values less than (2004),
  partition p2004 values less than (2005)
);

这种方法的问题是 partition_year 必须在查询中显式引用，否则分区修剪（非常可取，因为表很大）不会生效。该表用于多个用户的即席聚合查询；我不能指望他们都记得这个逻辑。

这可以通过视图来解决

create or replace view v as
select *
from t
where partition_year=start_year 
  and partition_year=end_year 
  and partition_year>1000
union all
select *
from t partition (poverflow);

现在像这样的查询

select * from v where start_year >= 2003 and end_year <= 2004;

使用正确的分区（下面计划中的 5-6 + 1）：

---------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |  4030 |     2   (0)| 00:00:01 |       |       |
|   1 |  VIEW                      | V    |     1 |  4030 |     2   (0)| 00:00:01 |       |       |
|   2 |   UNION-ALL                |      |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR|      |     1 |  2041 |     2   (0)| 00:00:01 |     5 |     6 |
|*  4 |     TABLE ACCESS FULL      | T    |     1 |  2041 |     2   (0)| 00:00:01 |     5 |     6 |
|   5 |    PARTITION RANGE SINGLE  |      |     1 |  2041 |     2   (0)| 00:00:01 |     1 |     1 |
|*  6 |     TABLE ACCESS FULL      | T    |     1 |  2041 |     2   (0)| 00:00:01 |     1 |     1 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("START_YEAR">=2003 AND "END_YEAR"<=2004 AND "END_YEAR">=2003 AND 
              "START_YEAR"<=2004 AND "PARTITION_YEAR"<=2004 AND "PARTITION_YEAR"="START_YEAR" AND 
              "PARTITION_YEAR"="END_YEAR")
   6 - filter("START_YEAR">=2003 AND "END_YEAR"<=2004)

问题是，如果我用日期替换 int 类型，这将不再起作用。我试图从日期中提取年份组件并向视图添加相应的约束，但未修剪分区。迄今为止更改 partition_year 的类型也没有帮助。

有什么办法可以在一个表中有多个日期列并且仍然能够使用分区修剪？

oracle database-design

3 个回答

  1. 当函数应用于分区列时，Oracle 无法进行分区修剪。从文档：

    有几种情况优化器无法执行修剪。一个常见的原因是在分区列的顶部使用运算符时。这可以是显式运算符（例如，函数），甚至可以是 Oracle 引入的隐式运算符，作为执行语句所需的数据类型转换的一部分。

    您的视图必须应用某种形式的函数来确定开始和结束日期是否是同一年，所以我相信您对这种方法不走运。

    我们对类似问题的解决方案是在基表上创建物化视图，在物化视图上指定不同的分区键。

    我们已经对其进行了定制以匹配常见的基本查询，以便我们也获得查询重写的好处。您可能需要让用户直接使用 MV，以确保您根据需要进行分区修剪，而不是依赖查询重写。

    （更新以删除不正确的示例并添加有关将函数应用于分区列的信息）

    • 1

  2. 我已经用这些数据测试了 Chris 提供的解决方案：

    insert into t (start_year,end_year) values (date'2011-01-01',date'2011-01-01');
insert into t (start_year,end_year) values (date'2011-01-01',date'2011-01-02');

    如果我对视图运行查询：

    select * from v;

    我只拿回第一行。这是因为视图有一个相等谓词，但分区定义有 extract(year) 函数。

    如果我修改视图以包含提取功能：

    create or replace view v as
select *
from t
where extract(year from partition_year)=extract(year from start_year)
  and extract(year from partition_year)=extract(year from end_year)
  and partition_year>date'2000-01-01'
union all
select *
from t partition (poverflow);

    我得到了正确的结果，但分区修剪不再发生。

    • 0
  3. Best Answer

    我找到了部分解决方案

    通过将视图定义为

    create or replace view v as
select *
from t
where partition_date between start_date and end_date 
  and partition_date > date'1000-01-01'
union all
select *
from t partition (poverflow);

    以下查询正常工作，仅访问分区 1,4 和 5

    select * from v where start_date >= date'2002-01-01' and end_date <= date'2003-01-01';

    但是，查询

    select * from v where start_date = date'2002-01-01';

    扫描分区 1,4-6，而不是 1 和 4（使用 end_date 代替将访问分区 1-4）。在我们的例子中，这不是一个严重的限制，因为典型的查询只访问最近几年，针对过去特定日期和日期范围的查询很少见。

    这种方法的一个稍微不同的版本是将 partition_date 列定义为

    case when trunc(start_date,'YEAR')=trunc(end_date,'YEAR') then greatest(start_date,end_date) 
else to_date('01.01.0001') end

    并且认为

    create or replace view v as
select *
from t
where partition_date >= start_date and partition_date >= end_date
  and partition_date > date'1000-01-01'
union all
select *
from t partition (poverflow);

    这具有相似的性能，但 start_date 和 end_date 都会导致访问最近几年。如果像这样放宽要求（只允许修剪前几年），则实际上不再需要溢出分区，并且解决方案简化为：

    create table t (
  start_date date,
  end_date date,
  partition_date date as (greatest(start_date,end_date)),
  data blob
)
partition by range(partition_date) (
  partition p2000 values less than (date'2001-01-01'),
  partition p2001 values less than (date'2002-01-01'),
  partition p2002 values less than (date'2003-01-01'),
  partition p2003 values less than (date'2004-01-01'),
  partition p2004 values less than (date'2005-01-01')
);

create or replace view v as
select *
from t
where partition_date >= start_date and partition_date >= end_date;
    • 0

相关问题