Início / dba / Perguntas / 149796
Accepted
Pierre-Alain Vigeant
Asked: 2016-09-16 07:27:56 +0800 CST

A consulta com ROWNUM retorna linhas diferentes a cada execução

  • 772

Eu tenho uma consulta que é usada para pesquisar uma lista de subdivisões em nossa rede. Quando a consulta é executada sem nenhum filtro, ela retorna uma quantidade diferente de linhas a cada execução

SELECT DISTINCT 
    t1.ZRESUBDIV,
    t1.SUBDIV_NAME
FROM M_STAGE_WP_PFUNCT_LOC t1
WHERE ROWNUM <= 200  -- normally a filter goes here
ORDER BY t1.SUBDIV_NAME

Execução 1

ZRESUBDIV SUBDIV_NAME                                                
------------ -------------------------------------- ----------------------
262 ALEXANDRIA                                                  
400 ALLANWATER                                                  
671 ASHCROFT                                                    
372 BALA                                                        
277 BEACHBURG                                                   
200 PONTE                                                      
374 CARAMAT                                                     
543 MONTANHA CENTRAL                                               
208 CHAPAIS                                                     
324 CHATHAM                                                     
340 CHICAGO                                                     
...

Execução 2

ZRESUBDIV SUBDIV_NAME                                                
------------ -------------------------------------- ----------------------
670 ALBREDA                                                     
262 ALEXANDRIA                                                  
400 ALLANWATER                                                  
671 ASHCROFT                                                    
372 BALA                                                        
277 BEACHBURG                                                   
604 BLACKFOOT                                                   
200 PONTE                                                      
607 CAMROSE                                                     
354 CASO                                                        
543 MONTANHA CENTRAL                                               
...

Também faltam dados: a subdivisão Baton Rouge nunca aparece, embora esteja nos dados.

Se eu remover o ROWNUM, todos os dados serão retornados corretamente. Preciso deixar ROWNUM, pois a consulta suporta uma cláusula WHERE com um filtro no nome da subdivisão.

Qual é a causa disso?

oracle oracle-12c

2 respostas

  1. Best Answer

    ROWNUMé avaliado antes ORDER BYde , não é assim que ROWNUMdeve ser usado. Para usar ROWNUMcorretamente, use uma consulta como esta:

    SELECT * FROM 
(
  SELECT DISTINCT 
      t1.ZRESUBDIV,
      t1.SUBDIV_NAME
  FROM M_STAGE_WP_PFUNCT_LOC t1
  ORDER BY t1.SUBDIV_NAME
)
WHERE ROWNUM <= 200;

    Mas isso é antigo. Como você está no 12c, use isto:

    SELECT DISTINCT 
    t1.ZRESUBDIV,
    t1.SUBDIV_NAME
FROM M_STAGE_WP_PFUNCT_LOC t1
ORDER BY t1.SUBDIV_NAME
FETCH FIRST 200 ROWS ONLY;
    • 10

  2. ROWNUMé a maneira errada de fazer esse tipo de consulta. Como @Kris Johnston explica em um comentário com o símile "carro e cavalo": A WHEREcláusula é executada antes do ORDER BY, e você não pode garantir a ordem dos resultados sem o ORDER BY, portanto, sim, resultados diferentes por execução seriam esperados.

    Você pode usar ROW_NUMBER()e uma subconsulta em vez disso:

    SELECT
    t.ZRESUBDIV,
    t.SUBDIV_NAME
FROM
  ( SELECT 
        t1.ZRESUBDIV,
        t1.SUBDIV_NAME,
        ROW_NUMBER() OVER (ORDER BY t1.SUBDIV_NAME) AS RN
    FROM M_STAGE_WP_PFUNCT_LOC t1
    WHERE --- your filter goes here but NOT any ROWNUM
    GROUP BY
        t1.ZRESUBDIV,
        t1.SUBDIV_NAME
  ) t
WHERE
    t.RN <= 200
ORDER BY
    t.SUBDIV_NAME ;

    A interna ORDER BYdefine quais linhas serão selecionadas (pois as 200 primeiras terão row_numbers de 1 a 200) e a externa ORDER BY, que é opcional, só importará para a ordem de apresentação.

    Observe que a ordenação não é total, ou seja, você pode ter linhas com o mesmo SUBDIV_NAME. Se você deseja que a ordem seja totalmente especificada, pode alterá-los ORDER BYpara ORDER BY SUBDIV_NAME, BYZRESUBDIV(por exemplo).

    As outras 2 perguntas na resposta de @Balazs Papp também são boas, se não melhores. O primeiro provavelmente é amplamente utilizado, pois funciona em versões mais antigas, antes de ROW_NUMBER()ser implementado. O segundo é certamente o mais conciso que você pode obter, desde que esteja na versão 12c.

    • 7

relate perguntas