Alternativa para group by (dúvida)

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Ricardo Carmo
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 9
Registrado em: Seg, 25 Fev 2008 3:56 pm
Localização: Maceió-AL

Dada uma tabela, eu desejo obter o valor do campo_A do registro que contenha o maior valor do campo_B (que pode ser um ou mais de um).
Então, eu escrevi o seguinte código:

Selecionar tudo

select campo_A from
(select campo_A from tabela order by campo_B desc)
where rownum=1;
Uma alternativa seria:

Selecionar tudo

select campo_A from tabela 
where campo_B=(select max(campo_B) from tabela)
Mas eu vi que a primeira consulta é mais otimizada. Pergunta:
Isso pode falhar ou meu raciocínio está correto?
rogenaro
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Sex, 30 Mar 2007 7:26 pm
Localização: Londrina - PR
Rafael O. Genaro

Isto depende de alguns fatores...

Na primeira consulta, você está acessando toda a sua tabela apenas uma vez, ordenando os resultados e retornando o primeiro registro.

Na segunda cosulta, você está lendo a mesma tabela duas vezes. Uma para buscar a maior data da tabela, e outra para buscar os registros que possuam esta data.


O fator principal, em condições normais, é:

* Existe um índice em sua tabela sobre o campo_b?

Caso não exista:
- A primeira query terá o trabalho de ler a tabela inteira, ordená-la, e retornar o primeiro registro que encontrar. Neste caso, é o melhor que se pode fazer...

- Já a segunda query irá realizar um full scan na tabela para encontrar o maior valor do campo_b. Em seguida, será realizado um novo full scan nesta mesma tabela, para encontrar os registros que possuam o campo_b igual ao valor encontrado (como o campo_b não está indexado, este segundo full scan irá ocorrer sem dúvidas).


Agora, caso exista um índice sobre o campo_b (e que ele esteja sendo usado pelo otimizador, claro):
- A primeira query irá continuar realizando o mesmo processo. Pode ser que no lugar de um full table scan o otimizador opte por realizar um index full scan sobre o índice (isto dependeria do otimizador, das estatísticas de usa tabela, e as colunas retornadas pela query interna também devem fazer parte do índice - eu já li a respeito disso várias vezes, mas sinceramente não me lembro até hoje de ter visto um plano de execução seguindo este caminho...), embora ambos podem ser bem demorados, especialmente em uma tabela com muitos registros (não nos esquecendo do custo para ordenar os registros, após recuperá-los).

- A segunda query, no entanto, se sairia muito melhor nestas condições. Neste caso, a consulta provavelmente iria realizar um index full scan (min/max) sobre o índice, para recuperar o maior valor contido. Uma vez obtido este valor, o índice é acessado novamente para que os registros contendo este valor possam ser retornados.

Isto, é claro, assumindo que o campo_b não pode possuir valores repetidos, ou que o campo possua poucas repetições.

Caso o campo_b possa assumir poucos valores distintos, e com muitas repetições (digamos que seu campo_b seja o campo 'ESTADO' de sua tabela de clientes com alguns milhões de registros), um índice sobre o campo poderia prejudicar muito a performance se comparado com os resultados obtidos pela mesma consulta na primiera situação.


Enfim:

Depende, e muito... (agora no fim do texto percebi que escrevi e escreve não ajudei em nada.. :P)
Cada caso é um caso, e infelizemente não existe uma regra simples a seguir.

Uma dica é sempre analizar o plano de execução gerado pelas suas queries (para isto, um bom conhecimento das tabelas sendo analisadas é fundamental), comparando-o com o plano gerado por outras formas de escrever a mesma consulta, ate achar a consulta "ideal" para o seu caso.
ruevers
Rank: DBA Sênior
Rank: DBA Sênior
Mensagens: 324
Registrado em: Sex, 02 Jun 2006 1:48 pm
Localização: sp
Contato:

Rafael

tu é dos meus
não importa a gambiarra
o que importa no final é o plano de execuçao....certo...
e as regras basicas de sempre
maior pro menor
indices...
e fazer join sempre, evitar ao maximo outras merdas como outher join
Ricardo Carmo
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 9
Registrado em: Seg, 25 Fev 2008 3:56 pm
Localização: Maceió-AL

Rafael, muito obrigado por suas observações, você ajudou sim, inclusive a me fazer enxergar sobre os benefícios do índice sobre o campo_b na segunda consulta. Por outro lado, eu me expressei mal na pergunta, pois o que eu realmente gostaria de saber é:

Na primeira consulta, eu realmente tenho a garantia de obter o registro que contém o maior valor no campo_b (já que a consulta interna está ordernada de forma descendente)?

Ou será que o rownum=1 pode me retornar qualquer um dos registros retornados pela consulta interna?
rogenaro
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Sex, 30 Mar 2007 7:26 pm
Localização: Londrina - PR
Rafael O. Genaro

Ops.., posso ter lido errado a pergunta =P

Na primeira consulta, como você colocou a condição where rownum = 1 em uma query externa àquela que faz a ordenação, sempre você terá como retorno um único registro, que possui o maior valor cadastrado no campo_b, mesmo que existam dois ou mais registros com este maior valor na tabela.
Em caso de registros com valores duplicados, qualquer um pode ser retornado pela query... depende a ordem que o banco recuperou os registros, se não me engano.

Já na segunda consulta, você sempre terá como resposta todos os registros com o maior valor de campo_b na tabela. Caso o campo esteja duplicado na tabela, ambos os registros seriam retornados.

Caso o campo_b seja seja a chave da tabela (sem mais nenhum outro campo fazendo parte da chave) ou se houver uma constraint única sobre ele, a segunda consulta sempre irá retornar um único registro e é equivalente à primieira (caso contrário, não se pode garantir nada...)
Responder
  • Informação