Início / dba / Perguntas / 184149
Accepted
Vlad Mihalcea
Asked: 2017-08-24 01:11:22 +0800 CST

É REALMENTE possível que o pedido não seja garantido para esta tabela derivada redundante específica?

  • 772

Me deparei com essa pergunta em uma conversa no Twitter com Lukas Eder .

Embora o comportamento correto seja aplicar a cláusula ORDER BY na consulta mais externa, porque, aqui, não estamos usando DISTINCT, GROUP BY, JOIN ou qualquer outra cláusula WHERE na consulta mais externa, por que um RDBMS não passaria apenas a dados de entrada como foram classificados pela consulta interna?

SELECT * 
FROM (
    SELECT * FROM table ORDER BY time DESC
) AS t

Ao executar este exemplo no PostgreSQL, pelo menos, você obtém o mesmo Plano de Execução para a consulta interna e este exemplo de tabela derivada, bem como o mesmo conjunto de resultados.

Então, eu diria que o Planner simplesmente descartará a consulta mais externa porque é redundante ou simplesmente passará pelos resultados da tabela interna.

Alguém acha que pode não ser assim?

mysql postgresql

4 respostas

  1. Best Answer

    A maioria dos bancos de dados é bastante clara sobre o fato de que uma ORDER BYem uma subconsulta é:

    • Não permitido: Por exemplo, SQL Server, Sybase SQL Anywhere (a menos que complementado com TOPou OFFSET .. FETCH)
    • Sem sentido: Por exemplo, PostgreSQL, DB2 (novamente, a menos que complementado com OFFSET .. FETCHou LIMIT)

    Aqui está um exemplo do manual do DB2 LUW (ênfase minha)

    Uma cláusula ORDER BY em uma subseleção não afeta a ordem das linhas retornadas por uma consulta. Uma cláusula ORDER BY afeta apenas a ordem das linhas retornadas se for especificada na seleção completa mais externa.

    O texto é bastante explícito, assim como o do PostgreSQL :

    Se a classificação não for escolhida, as linhas serão retornadas em uma ordem não especificada. A ordem real nesse caso dependerá dos tipos de plano de varredura e junção e da ordem no disco, mas não deve ser confiável . Uma ordenação de saída específica só pode ser garantida se a etapa de classificação for escolhida explicitamente.

    A partir desta especificação, pode-se concluir que qualquer ordenação resultante da ORDER BYcláusula em uma tabela derivada é meramente acidental e pode coincidir com sua ordenação esperada (o que ocorre na maioria dos bancos de dados em seu exemplo trivial), mas seria imprudente confiar em isto.

    Nota lateral no DB2:

    Em particular, o DB2 possui um recurso menos conhecido chamadoORDER BY ORDER OF <table-designator> , que pode ser usado da seguinte forma:

    SELECT C1 FROM
   (SELECT C1 FROM T1
      UNION
    SELECT C1 FROM T2
    ORDER BY C1 ) AS UTABLE
ORDER BY ORDER OF UTABLE

    Neste caso particular, a ordenação da tabela derivada pode ser reutilizada explicitamente no SELECT mais externo

    Nota lateral sobre o Oracle:

    Durante anos, tem sido uma prática na Oracle implementar a OFFSETpaginação usando ROWNUM, que pode ser razoavelmente calculado apenas após ordenar uma tabela derivada:

    SELECT *
FROM (
  SELECT rownum AS rn, t.* -- ROWNUM here depends on the derived table's ordering
  FROM (
    SELECT * FROM table ORDER BY time DESC
  ) t
) t
WHERE rn BETWEEN 10 AND 20

    Pode-se razoavelmente esperar que, pelo menos na presença de ROWNUMuma consulta, as versões futuras do Oracle não quebrem esse comportamento para não quebrar praticamente todo o Oracle SQL legado por aí, que ainda não migrou para o muito mais desejável e sintaxe padrão SQL legível OFFSET .. FETCH:

    SELECT * FROM table ORDER BY time DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY
    • 20

  2. Sim. Sem uma ORDER BYcláusula, a ordem de saída é indefinida e o planejador de consulta está dentro de sua competência para assumir que você sabe e entende isso.

    Ele pode decidir que, como a consulta externa não especifica uma ordem, pode descartar a ordenação na consulta interna para evitar uma operação de classificação, especialmente se não houver nenhum índice clusterizado ou nenhum índice para dar suporte à ordenação. Se isso não acontecer agora , pode acontecer em versões futuras.

    Nunca confie em comportamento indefinido. Se você precisar de um pedido específico, coloque uma ORDER BYcláusula no local apropriado.

    • 12

  3. É o problema com comportamento indefinido - funciona para você, funciona para mim, reformata o HDD em prod;)

    Podemos dar um passo para trás e dizer que, em certo sentido, você está certo - não há nenhuma razão terrena para que qualquer RDBMS sensato reorganize as linhas na seleção interna. Mas não é garantido - o que significa que pode haver uma razão no futuro, e os fornecedores são livres para fazê-lo. O que significa que qualquer código que dependa desse comportamento está à mercê de uma alteração que um fornecedor poderia fazer e que não teria a obrigação de divulgar, pois não é uma alteração importante de um POV de API.

    • 6

  4. É REALMENTE possível que o pedido não seja garantido para esta tabela derivada redundante específica?

    A resposta para todas as versões atuais do Postgres (que você estava testando) é: Não . Para a consulta fornecida, a ordem de classificação é garantida.

    As pessoas do SQL Server ficarão desconfortáveis ​​com isso, pois a Microsoft nem permite ORDER BYsubconsultas. A ordem de classificação é garantida para essa consulta simples no Postgres. ORDER BYé aplicado na subconsulta e a consulta externa não faz nada que possa alterar a ordem.

    O manual ainda sugere isso no capítulo Funções Agregadas :

    Como alternativa, fornecer os valores de entrada de uma subconsulta classificada geralmente funcionará.

    Observe que isso só é verdade enquanto os níveis de consulta externos não adicionam operações que podem alterar a ordem. Portanto, é apenas "garantido" para o caso simples, e isso não é apoiado pelo padrão SQL. O Postgres é livre para reordenar se for oportuno para operações adicionais. Em caso de dúvida adicione outro ORDER BYao exterior SELECT. (Nesse caso, o ORDER BYruído interno seria redundante para essa consulta simples.)

    • 3

relate perguntas