[sql] Oracle ORDER BY 및 ROWNUM을 올바르게 사용하는 방법은 무엇입니까?

Program / 글쓴이

저장 프로 시저를 SQL Server에서 Oracle로 변환하여 제품과 호환되도록하는 데 어려움을 겪고 있습니다.

타임 스탬프를 기반으로 일부 테이블의 최신 레코드를 반환하는 쿼리가 있습니다.

SQL 서버 :

SELECT TOP 1 *
FROM RACEWAY_INPUT_LABO
ORDER BY t_stamp DESC

=> 가장 최근 기록을 반환합니다.

그러나 Oracle :

SELECT *
FROM raceway_input_labo
WHERE  rownum <= 1
ORDER BY t_stamp DESC

=> ORDER BY문에 상관없이 가장 오래된 레코드를 반환합니다 (아마도 인덱스에 따라 다름) !

내 요구 사항에 맞게 Oracle 쿼리를 다음과 같이 캡슐화했습니다.

SELECT *
FROM
    (SELECT *
     FROM raceway_input_labo
     ORDER BY t_stamp DESC)
WHERE  rownum <= 1

그리고 그것은 작동합니다. 그러나 그것은 나에게 끔찍한 해킹처럼 들리는데, 특히 관련 테이블에 많은 레코드가있는 경우 더욱 그렇습니다.

이것을 달성하는 가장 좋은 방법은 무엇입니까?

답변

where문이 실행됩니다 전에order by . 따라서 원하는 쿼리는 ” 첫 번째 행을 가져 와서 t_stamp desc 로 정렬 “이라고 말합니다. 그리고 그것은 당신이 의도 한 것이 아닙니다.

하위 쿼리 방법은 Oracle에서이를 수행하는 데 적합한 방법입니다.

두 서버 모두에서 작동하는 버전을 원하는 경우 다음을 사용할 수 있습니다.

select ril.*
from (select ril.*, row_number() over (order by t_stamp desc) as seqnum
      from raceway_input_labo ril
     ) ril
where seqnum = 1

바깥 쪽 *은 마지막 열에서 “1”을 반환합니다. 이를 방지하려면 열을 개별적으로 나열해야합니다.

답변

ROW_NUMBER()대신 사용하십시오 . ROWNUM의사 열이며 ROW_NUMBER()함수입니다. 그들 사이의 차이점에 대해 읽고 아래 쿼리의 출력 차이를 볼 수 있습니다.

SELECT * FROM (SELECT rownum, deptno, ename
           FROM scott.emp
        ORDER BY deptno
       )
 WHERE rownum <= 3
 /

ROWNUM    DEPTNO    ENAME
---------------------------
 7        10    CLARK
 14       10    MILLER
 9        10    KING


 SELECT * FROM
 (
  SELECT deptno, ename
       , ROW_NUMBER() OVER (ORDER BY deptno) rno
  FROM scott.emp
 ORDER BY deptno
 )
WHERE rno <= 3
/

DEPTNO    ENAME    RNO
-------------------------
10    CLARK        1
10    MILLER       2
10    KING         3

답변

이 사용 사례에서 제안 할 대안은 MAX (t_stamp)를 사용하여 최신 행을 가져 오는 것입니다. 

select t.* from raceway_input_labo t
where t.t_stamp = (select max(t_stamp) from raceway_input_labo)
limit 1

내 코딩 패턴 선호도 (아마도)-신뢰할 수 있고, 일반적으로 정렬 된 목록에서 첫 번째 행을 선택하는 것보다 더 나은 성능을 발휘합니다. 또한 의도가 더 명시 적으로 가독성이 높습니다.
도움이 되었기를 바랍니다 …

SQLer

답변

위의 주석에서 이와 관련된 몇 가지 디자인 문제를 문서화했습니다. 짧은 이야기, Oracle에서는 큰 테이블 및 / 또는 동일한 열 이름을 가진 테이블이있을 때 결과를 수동으로 제한해야합니다 (그리고 모두 명시 적으로 입력하고 이름을 바꾸고 싶지는 않음). 쉬운 해결책은 중단 점을 파악하고 쿼리에서이를 제한하는 것입니다. 또는 충돌하는 열 이름 제약 조건이없는 경우 내부 쿼리에서이 작업을 수행 할 수도 있습니다. 예

WHERE m_api_log.created_date BETWEEN TO_DATE('10/23/2015 05:00', 'MM/DD/YYYY HH24:MI')
                                 AND TO_DATE('10/30/2015 23:59', 'MM/DD/YYYY HH24:MI')

결과가 크게 줄어들 것입니다. 그런 다음 ORDER BY 또는 외부 쿼리를 수행하여 행을 제한 할 수 있습니다.

또한 TOAD에는 행을 제한하는 기능이 있다고 생각합니다. 그러나 그것이 Oracle의 실제 쿼리 내에서 제한되는지 확실하지 않습니다. 확실하지 않다.

답변