使用多個日期列進行分區修剪

我在 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 無法進行分區修剪。從文件：

有幾種情況優化器無法執行修剪。一個常見的原因是在分區列的頂部使用運算符時。這可以是顯式運算符（例如，函式），甚至可以是 Oracle 引入的隱式運算符，作為執行語句所需的數據類型轉換的一部分。

您的視圖必須應用某種形式的函式來確定開始和結束日期是否是同一年，所以我相信您對這種方法不走運。

我們對類似問題的解決方案是在基表上創建物化視圖，在物化視圖上指定不同的分區鍵。

我們已經對其進行了定制以匹配常見的基本查詢，以便我們也獲得查詢重寫的好處。您可能需要讓使用者直接使用 MV，以確保您根據需要進行分區修剪，而不是依賴查詢重寫。

（更新以刪除不正確的範例並添加有關將函式應用於分區列的資訊）

我已經用這些數據測試了 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);

我得到了正確的結果，但分區修剪不再發生。

引用自：https://dba.stackexchange.com/questions/30346