Início / dba / Perguntas / 318325
Accepted
T.S.
Asked: 2022-10-18 15:16:34 +0800 CST

O Oracle garante a ordem final das linhas de saída quando é definida na visualização inline

  • 772

Esta consulta é baseada em 2 tabelas em que algumas linhas PerfData1possuem vários registros filho. As linhas não são duplicadas, mas alguns ids são. Eu preciso pegar apenas 1 dessas linhas e isso também é uma consulta de paginação, que precisa obter o número total ou itens existentes e um subconjunto final.

A consulta funciona bem. No entanto, não tenho certeza se a ordem definida na visualização embutida (inner ORDER BY) será mantida na saída final. Até agora, não se viu nenhum desvio. Mas lembro que no mundo SQL Server isso não é garantido, por isso preciso de 2 ORDER BYcláusulas.

    SELECT 
        All_Results_Table.*
    FROM
    (
        SELECT 
            Rownum_Table.*,
            COUNT(*) OVER() as sys___countAllFoundRecords,
            ROWNUM sys___outputRownum
        FROM 
        (
            SELECT 
                ROW_NUMBER() OVER(PARTITION BY PerfData1.id ORDER BY PerfData1.id) AS sys___multivalueRowSequence, -- This is OPTIONAL when we DON'T want dupe IDs
                ROW_NUMBER() OVER(order by 1) AS sys___innerRowNumber,
                PerfData1.id,
                PerfData1.c1 item,
                PerfData2.c1 subitem
            FROM 
                PerfData1 inner join 
                PerfData2 on PerfData1.id = PerfData2.fkId
            WHERE (1 = 1) 
            ORDER BY id, subitem desc --<<-- WILL THIS ORDER STAY in the FINAL OUTPUT 
        ) Rownum_Table
        WHERE sys___multivalueRowSequence = 1 -- This is OPTIONAL when we DON'T want dupe IDs
    ) All_Results_Table
    WHERE 
        sys___outputRownum BETWEEN 200 AND 230

A ordem definida no ORDER BYSQL acima será mantida na saída final?

oracle

3 respostas

  1. Best Answer

    Com praticamente qualquer implementação de SQL, não há garantia alguma na ordenação da saída final, a menos que você declare explicitamente o que deseja que essa ordem seja.

    Em alguns casos, um outro em particular pode ser muito provável, mas nunca é garantido. SQL é uma linguagem declarativa, qualquer coisa que você não declare, o banco de dados é livre para não realocar em execuções futuras. Você pode obter a mesma ordem em todos os testes concebíveis agora , mas mais tarde, quando os dados aumentarem e/ou alterarem o equilíbrio, ou alguém alterar as opções de indexação para as tabelas afetadas, ou uma ou mais das tabelas se tornarem visualizações, ou o banco de dados for atualizado para versão mais recente com um planejador de consulta massivamente twittado, a maneira como os dados são trabalhados pode mudar significativamente e essa ordem é perdida.

    Se você deseja garantir uma ordem específica fora da camada final de uma consulta complexa, ou uma consulta simples, você precisa declarar explicitamente que deseja essa ordem com uma ORDER BYcláusula nesse nível.

    tl; dr: não.

    Dos comentários:

    Neste caso, pode haver alguma dica que eu possa usar para dizer ao ORACLE para manter o pedido original? Porque você sabe, nessas consultas de várias camadas, acredito, Order by consome o desempenho

    ORDER BYquase sempre¹ tem algum custo de desempenho, mas se você precisa dos dados nessa ordem, então você precisa nessa ordem², então precisa pagar o preço. Como Paul diz, o preço pode ser praticamente nada: se os dados precisarem ser ordenados de qualquer maneira devido à maneira como o planejador de consultas decide que é o melhor método de obter o que você pediu, não há necessidade de reordenar, e o único custo é a fração de segundo que o planejador leva para perceber isso³.

    Para ordenar a saída, o ORDER BYdeve se aplicar à parte mais externa da consulta, nesse caso, movendo -se para depois da cláusula ORDER BY id, subitem descfinal . WHERENão há outra maneira de garantir o ordenamento final, qualquer outra coisa que pareça funcionar é contar com um comportamento indefinido que pode mudar a qualquer momento.

    Além disso: no que diz respeito a ter uma ORDER BYsubconsulta como essa; o planejador de consultas pode ignorá-lo completamente. Alguns bancos de dados, de fato, consideram sua existência um erro de sintaxe. Se você estiver confiando nele para dar uma ordem específica à saída ROW_NUMBER() OVER(order by 1), eu usaria ROW_NUMBER() OVER(order by id, subitem desc).

    [1] casos em que não inclui consultas simples que podem buscar/varrer um índice e não fazem referência a nada que não coveredseja includedpor esse índice, mas se você assumir que solicitar um pedido específico sempre custa, então você nunca estará errado em um caminho que leva a uma surpresa desagradável

    [2] embora haja momentos em que é benéfico deixar o banco de dados descartar as coisas em qualquer ordem antiga e classificar no aplicativo de chamada, para mover a carga de classificação para longe do banco de dados porque a camada do aplicativo geralmente é mais fácil de escalar

    [3] ou pode ter duas opções de plano que parecem ter um desempenho idêntico de outra forma, mas uma produzirá naturalmente uma classificação pelo que você deseja sem trabalho extra, de modo que escolhe esse plano em detrimento do outro.

    • 5

  2. Só é garantido que o resultado seja classificado se houver uma order bycláusula no final da consulta. Os manuais da Oracle são absolutamente claros sobre isso. Em "Oracle Database, SQL Language Reference, 12c Release 1 (12.1), E41329-25, July 2017" você pode ler :

    ordem_por_cláusula

    Use a cláusula ORDER BY para ordenar as linhas retornadas pela instrução. Sem uma order_by_clause, não há garantia de que a mesma consulta executada mais de uma vez recuperará linhas na mesma ordem.

    Para algumas palavras-chave é mencionado que elas não garantem a ordenação, por exemplo aqui

    group_by_clause

    A cláusula GROUP BY agrupa linhas, mas não garante a ordem do conjunto de resultados. Para ordenar os agrupamentos, use a cláusula ORDER BY.

    Você também mencionou em um comentário que encontrou uma nota semelhante para a palavra- UNIONchave. Acho que o motivo é que as implementações tradicionais desses recursos precisam classificar os dados e, portanto, o resultado já foi classificado. Muitas vezes os programadores confiavam nisso. Mas agora existem implementações que usam outro algoritmo e o resultado não é mais classificado.

    • 2

  3. Um ORDER BYem uma subseleção não será respeitado na saída final. O que te faz pensar o contrário?

    Agora, sendo o Oracle, pode levar você a acreditar que ele manterá a ordem, apenas para apunhalá-lo pelas costas, várias vezes, quando você menos esperar. Então, não.

    • 1

relate perguntas