Início / dba / Perguntas / 30346
Accepted
sjk
Asked: 2012-12-14 02:02:43 +0800 CST

Remoção de partição com várias colunas de data

  • 772

Eu tenho uma grande tabela no banco de dados Oracle 11g que contém dados históricos de vários anos, então gostaria de particioná-la por ano. O problema é que a tabela tem várias colunas de data e todas são usadas em consultas, então não posso simplesmente escolher uma coluna de data e usá-la como chave de partição.

A maioria das datas de tempo estão próximas umas das outras, então criei partições para cada ano, além de uma partição "overflow" que contém as linhas que cruzam o limite do ano. Aqui está um exemplo simplificado:

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)
);

O problema com essa abordagem é que partition_year deve ser referenciado explicitamente em consultas ou a remoção de partição (altamente desejável porque a tabela é grande) não terá efeito. Essa tabela é usada para consultas agregadas ad hoc por vários usuários; Não posso esperar que todos se lembrem dessa lógica.

Isso pode ser resolvido com uma visão

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);

Agora consultas como esta

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

Use as partições corretas (5-6 + 1 no plano abaixo):

---------------------------------------------------------------------------------------------------
| 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)

O problema é que se eu substituir tipos int por datas, isso não funcionará mais. Tentei extrair o componente do ano das datas e adicionar restrições correspondentes à exibição, mas as partições não foram removidas. Alterar o tipo de partition_year até a data também não ajudou.

Existe alguma maneira de eu ter várias colunas de data em uma tabela e ainda poder usar a remoção de partição?

oracle database-design

3 respostas

  1. O Oracle não consegue fazer remoção de partição quando uma função é aplicada à coluna particionada. Dos documentos :

    Existem vários casos em que o otimizador não pode realizar a remoção. Um motivo comum é quando um operador é usado no topo de uma coluna de particionamento. Pode ser um operador explícito (por exemplo, uma função) ou mesmo um operador implícito introduzido pelo Oracle como parte da conversão de tipo de dados necessária para executar a instrução.

    Sua visão precisa aplicar algum tipo de função para datas de início e término para descobrir se elas são do mesmo ano ou não, então acredito que você está sem sorte com essa abordagem.

    Nossa solução para um problema semelhante foi criar visualizações materializadas na tabela base, especificando diferentes chaves de partição nas visualizações materializadas.

    Adaptamos o nosso para corresponder a consultas de base comuns, para que também obtenhamos benefícios de reescrita de consulta. Pode ser necessário fazer com que os usuários usem as MVs diretamente para garantir que a limpeza da partição funcione conforme necessário, em vez de depender da regravação da consulta.

    (Atualizado para remover o exemplo incorreto e adicionar informações sobre a aplicação de funções às colunas de partição)

    • 1

  2. Eu testei a solução fornecida por Chris com esses dados:

    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');

    Se eu executar uma consulta na exibição:

    select * from v;

    Eu só recebo a primeira linha de volta. Isso ocorre porque a exibição tem um predicado de igualdade, mas a definição da partição tem a função extract(year).

    Se eu modificar a exibição para incluir funções de extração:

    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);

    Obtenho resultados corretos, mas a remoção de partição não acontece mais.

    • 0
  3. Best Answer

    Eu encontrei uma solução parcial

    Definindo view como

    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);

    A consulta a seguir funciona corretamente, acessando apenas as partições 1,4 e 5

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

    No entanto, consulte

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

    Varre as partições 1,4-6, em vez de 1 e 4 (usando end_date, em vez disso, acessaria as partições 1-4). Em nosso caso, essa não é uma limitação crítica, pois as consultas típicas acessam apenas os anos mais recentes, as consultas em datas específicas e intervalos de datas no passado são raras.

    Uma versão ligeiramente diferente dessa abordagem seria definir a coluna partition_date como

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

    E a vista como

    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);

    Isso tem desempenho semelhante, mas start_date e end_date fazem com que os anos mais recentes sejam acessados. Se os requisitos forem relaxados assim (a remoção apenas dos anos anteriores é permitida), a partição de estouro não será mais necessária e a solução será simplificada para:

    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

relate perguntas