prosource

Oracle에서 범위/간격 쿼리를 조정하는 방법은 무엇입니까?

probook 2023. 9. 25. 22:49
반응형

Oracle에서 범위/간격 쿼리를 조정하는 방법은 무엇입니까?

는 테이블 가 있습니다.A(COL1, COL2):

CREATE TABLE A (
  COL1 NUMBER(15) NOT NULL,
  COL2 NUMBER(15) NOT NULL,
  VAL1 ...,
  VAL2 ...
);
ALTER TABLE A ADD CONSTRAINT COL1_BEFORE_COL2 CHECK (COL1 <= COL2);

간격은 "독점"으로 보장됩니다. 즉, 절대로 겹치지 않습니다.즉, 이 쿼리는 행을 산출하지 않습니다.

SELECT *
FROM (
  SELECT
    LEAD(COL1, 1) OVER (ORDER BY COL1) NEXT,
    COL2
  FROM A
)
WHERE COL2 >= NEXT;

에 .(COL1, COL2) 제 은 다음과 같습니다 자, 제 질문은 다음과 같습니다.

SELECT /*+FIRST_ROWS(1)*/ *
FROM A
WHERE :some_value BETWEEN COL1 AND COL2
AND ROWNUM = 1

는 는() 합니다의 의 레코드에 이 우수합니다.A 의합니다.:some_value입니다라는 에서 매우 다의 1초에 대해서는 떨어집니다.:some_value액세스 술어의 선택성이 낮기 때문입니다.

실행 계획은 저에게 좋은 것 같습니다.하게 포괄하고 때문에 입니다를 됩니다.INDEX RANGE SCAN:

------------------------------------------------------
| Id  | Operation                    | Name | E-Rows |
------------------------------------------------------
|   0 | SELECT STATEMENT             |      |        |
|*  1 |  COUNT STOPKEY               |      |        |
|   2 |   TABLE ACCESS BY INDEX ROWID| A    |      1 |
|*  3 |    INDEX RANGE SCAN          | A_PK |        |
------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM=1)
   3 - access("VAL2">=:some_value AND "VAL1"<=:some_value)
       filter("VAL2">=:some_value)

3가 의 .:some_value값이 높을수록 인덱스에서 필터 작동 "인" kicks.

의 이 를 일반적으로 할 수 있는 ?:some_value 다시 할 수 있습니다 추가 정규화가 필요하다면 테이블을 완전히 재설계할 수 있습니다.

당신의 시도는 좋으나 몇 가지 중요한 문제를 놓쳤습니다.

천천히 시작해요.ㅇCOL1 전COL2거기에도 포함되어 있습니다.

데이터에 대한 제약(특히 중복되지 않음)으로 인해 실제로는 다음 행 앞에 있는 행을 원할 뿐입니다.COL1이다.<=어떤 가치가.. [----]로 .COL1

이것은 전형적인 Top-N 쿼리입니다.

select *
  FROM ( select *
          from A
         where col1 <= :some_value
         order by col1 desc
       )
 where rownum <= 1;

다음을 사용해야 합니다.ORDER BY확실한 정렬 명령을 받기 위해.WHERE됩니다 후에 됩니다.ORDER BY이제 top-n 필터를 외부 쿼리로 래핑해야 합니다.

됐어요. 요를 죠.COL2범위에 전혀 들어가지 않는 기록을 걸러내는 것입니다. 있는 :예를 들어 some_value가 5이고 다음 데이터가 있는 경우:

  COL1 | COL2
     1 |  2
     3 |  4   <-- you get this row 
     6 | 10

으로 입니다이면 이 행이 COL25가 되겠지만 안타깝게도 이 경우 쿼리의 올바른 결과는 [empty set]입니다.입니다를 걸러내야 COL2다음과 같이:

select *
  FROM ( select *
           FROM ( select *
                    from A
                   where col1 <= :some_value
                   order by col1 desc
                )
          where rownum <= 1
        )
  WHERE col2 >= :some_value;

귀하의 접근 방식에는 다음과 같은 몇 가지 문제가 있었습니다.

  • 된 ORDER BY와 관련하여 rownum필터!
  • (Top-N rownum필터) 너무 이릅니다.결과가 없으면 어떻게 하죠?끝까지 데이터베이스 읽기 인덱스,rownum(STOPKEY)는 절대로 시작하지 않습니다.
  • 옵티마이저 결함.위드 더between술어, 나의 11g 설치는 내림차순으로 인덱스를 읽는 아이디어가 나타나지 않아서, 그것은 실제로 처음부터 (0) 위쪽에서 그것을 발견할 때까지 읽고 있었습니다.COL2일치하는 값 --OR --COL1사정거리를 벗어나다

.

COL1 | COL2
   1 |  2   ^
   3 |  4   |      (2) go up until first match.
            +----- your intention was to start here
   6 | 10

실제로 일어난 일은 다음과 같습니다.

  COL1 | COL2
     1 |  2   +----- start at the beginning of the index
     3 |  4   |      Go down until first match.      
              V
     6 | 10

내 쿼리의 실행 계획을 보십시오.

------------------------------------------------------------------------------------------
| Id  | Operation                       | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |        |     1 |    26 |     4   (0)| 00:00:01 |
|*  1 |  VIEW                           |        |     1 |    26 |     4   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY                 |        |       |       |            |          |
|   3 |    VIEW                         |        |     2 |    52 |     4   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID | A      | 50000 |   585K|     4   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN DESCENDING| SIMPLE |     2 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

를 합니다.INDEX RANGE SCAN **DESCENDING**.

마지막으로, 나는 왜 그들을 포함하지 않았는가?COL2색인에? 한 단일 행 상단 n 쿼리입니다.최대 단일 테이블 액세스를 저장할 수 있습니다(위의 행 추정 결과에 관계없이!).대부분의 경우 행을 찾을 것으로 예상되는 경우 다른 열(아마도)은 테이블로 이동하여 아무것도 저장하지 않고 공간만 사용해야 합니다.COL2쿼리를 통해 아무것도 반환되지 않는 경우에만 성능이 향상됩니다.

관련:

범위가 교차하지 않기 때문에 col1을 기본 키로 정의하고 다음과 같이 쿼리를 실행할 수 있습니다.

SELECT *
  FROM    a
       JOIN
          (SELECT MAX (col1) AS col1
             FROM a
            WHERE col1 <= :somevalue) b
       ON a.col1 = b.col1;

범위 사이에 간격이 있는 경우 다음을 추가해야 합니다.

Where col2 >= :somevalue

맨 끝줄로

실행 계획:

SELECT STATEMENT  
 NESTED LOOPS  
  VIEW  
   SORT AGGREGATE 
    FIRST ROW  
     INDEX RANGE SCAN (MIN/MAX) PKU1
  TABLE ACCESS BY INDEX A
   INDEX UNIQUE SCAN PKU1

아마도 이 힙 테이블을 IOT 테이블로 변경하면 성능이 더 좋아질 것입니다.

이를 테스트하기 위해 샘플 데이터를 생성한 것은 아니지만, 한 번 시도해보시길 바랍니다.

ALTER TABLE A ADD COL3 NUMBER(15);

UPDATE A SET COL3 = COL2 - COL1;

COL3에 인덱스를 만듭니다.

SELECT /*+FIRST_ROWS(1)*/ *
FROM A
WHERE :some_value < COL3
AND ROWNUM = 1;

언급URL : https://stackoverflow.com/questions/22226978/how-to-tune-a-range-interval-query-in-oracle

반응형

'prosource' 카테고리의 다른 글

DLL의 내보낸 기능의 네임 망글링을 중지하려면 어떻게 해야 합니까?  (0) 2023.09.25
Android에 프로그래밍 방식으로 응용 프로그램 설치  (0) 2023.09.25
Oracle 11g 세션 시간 초과 설정  (0) 2023.09.25
Oracle 설명 계획을 통한 쿼리의 총 비용  (0) 2023.09.25
MySQL에서 쿼리가 인덱스를 사용하도록 강제하는 방법은 무엇입니까?  (0) 2023.09.20

'prosource'의 다른글

  • 현재글Oracle에서 범위/간격 쿼리를 조정하는 방법은 무엇입니까?

관련글

댓글

티스토리툴바