Início / dba / Perguntas / 11366
Accepted
Bob Jansen
Asked: 2012-01-24 01:20:12 +0800 CST

Encontrar valores distintos com eficiência

  • 772

Eu tenho várias tabelas com a chave primária (mês, ano, número) e diferentes cardinalidades diferem um pouco. Para a tupla (mês, ano) a história não vai muito longe, isso provavelmente não crescerá além de 50 a muito longo prazo. Para cada tupla (mês, ano) não há mais de 2 milhões de números únicos. Quero saber quais combinações de mês e ano estão disponíveis. Eu faço isso usando esta consulta:

select month, year from table group by month, year

Isso retorna o resultado correto, mas não parece ser muito eficiente. Qual é uma maneira eficiente de obter esse resultado (utilizando o índice único)?

O consultor de ajuste sugere adicionar um índice mês-ano para esta consulta, mas isso parece um desperdício porque um índice maior já está disponível.

oracle

2 respostas

  1. Best Answer

    Você pode usar uma variação da seguinte técnica - que força varreduras repetidas de alcance 'MIN/MAX':

    Suposições

    1. Você pode produzir uma lista de todas as combinações possíveis de ano/mês
    2. numbernão é nulo (o que não pode ser como está no PK, mas mencionei porque há uma maneira de contornar se nulos forem permitidos)

    banco de ensaio:

    create table foo(month, year, num, primary key(month, year, num)) as
with m as ( select extract(month from d) as month, extract(year from d) as year
            from (select add_months(sysdate,1-level) as d from dual connect by level<50) )
select month, year, num
from m cross join 
     (select level as num from dual connect by level<100000 order by dbms_random.random());

    consulta normal:

    select distinct month, year from foo;
--gets=11656

    técnica min/max:

    with m as ( select extract(month from d) as month, extract(year from d) as year
            from (select add_months(sysdate,1-level) as d from dual connect by level<50) )
select month, year, decode(( select min(num)
                             from foo
                             where month=m.month and year=m.year )
                           ,null, 'N', 'Y') as has_data_yn
from m;
--gets=294

    Algumas explicações em resposta aos comentários:

    Em cada caso (o testbed e a consulta min/max), a cláusula de fatoração da subconsulta apenas gerou uma lista de tuplas (ano, mês):

    with m as ( select extract(month from d) as month, extract(year from d) as year
            from (select add_months(sysdate,1-level) as d from dual connect by level<50) )
select * from m;
/*
MONTH                  YEAR                   
---------------------- ---------------------- 
1                      2012                   
12                     2011                   
11                     2011                   
10                     2011           
...
...
*/

    Então a técnica usa uma subconsulta na selectcláusula para verificar se alguma linha está presente para o (mês, ano) - esta subconsulta necessariamente deve produzir apenas no máximo 1 linha:

    select min(num)
from foo
where month=m.month and year=m.year;

    Isso é muito rápido porque faz uso da natureza ordenada do PK - no entanto, ele precisa ser executado uma vez para cada mês - se houver milhões de linhas para cada mês, isso faz sentido, mas não se houver poucas o suficiente para caber um pequeno número de blocos.

    • 5

  2. Aqui está uma solução usando a mesma técnica de Jack Douglas (+1). Ele produz um número idêntico de resultados consistentes usando seu testbed, mas se é mais fácil de entender ou não, depende do observador.

    SELECT extract(month from d) m, extract(year from d) y 
FROM (SELECT add_months(sysdate,1-level) d FROM dual CONNECT BY level < 50)
WHERE EXISTS (
 SELECT 1 FROM foo WHERE month=extract(month from d) AND year=extract(year from d)
);

    Esta opção usa o select from dual para direcionar a consulta e o select from foo apenas para decidir quais datas manter.

    A mesma consulta também poderia ser escrita assim:

    SELECT * FROM (
   SELECT extract(month from add_months(sysdate,1-level)) m 
        , extract(year from add_months(sysdate,1-level)) y 
      FROM dual CONNECT BY level < 50)
WHERE EXISTS (
   SELECT 1 FROM foo WHERE month=m AND year=y
);
    • 3

relate perguntas