표에서 MIN과 MAX를 모두 선택하는 속도가 예상보다 느립니다.

표에서 MIN과 MAX를 모두 선택하는 속도가 예상보다 느립니다.

이 .MYTABLE 열이 있는SDATE테이블의 기본 키이며 고유 인덱스가 있습니다.

이 쿼리를 실행할 때:

SELECT MIN(SDATE) FROM MYTABLE

그것은 즉시 답을 줍니다.다음과 같은 경우에도 마찬가지입니다.

SELECT MAX(SDATE) FROM MYTABLE

하지만 두 가지를 함께 조회하면,

SELECT MIN(SDATE), MAX(SDATE) FROM MYTABLE

실행하는 데 훨씬 더 많은 시간이 걸립니다.계획을 분석해보니 최소 또는 최대 중 하나가 쿼리되면 INDEX FULL SCAN(MIN/MAX)을 사용하지만 둘 다 동시에 쿼리되면 FULL TABLE SCAN을 실행합니다.

왜요?

테스트 데이터:

전11g

create table MYTABLE
(
  SDATE  DATE not null,
  CELL   VARCHAR2(10),
  data NUMBER
)
tablespace CHIPS
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

alter table MYTABLE
  add constraint PK_SDATE primary key (SDATE)
  using index 
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

로드 테이블:

declare 
  i integer;
begin
  for i in 0 .. 100000 loop
     insert into MYTABLE(sdate, cell, data)
     values(sysdate - i/24, 'T' || i, i);     
     commit;
  end loop;
end;

통계 수집:

begin
  dbms_stats.gather_table_stats(tabname => 'MYTABLE', ownname => 'SYS');
end;

계획 1:

계획 2:

전체 인덱스 검색은 인덱스의 한 쪽만 방문할 수 있습니다.할 때

SELECT MIN(SDATE), MAX(SDATE) FROM MYTABLE

당신은 2면 방문을 요청하고 있습니다.따라서 최소 및 최대 열 값을 모두 사용하려면 인덱스 전체 검색을 사용할 수 없습니다.

자세한 분석은 여기에서 확인할 수 있습니다.

의 명설계다다니. 단일.MIN또는MAX생산할 것입니다.INDEX FULL SCAN (MIN/MAX)반면에 두 사람이 있을 때 당신은 하나를 얻을 것입니다.INDEX FULL SCAN또는 a.

차이점을 이해하려면 다음에 대한 설명을 찾아야 합니다.

전체 인덱스 검색에서 데이터베이스는 전체 인덱스를 순서대로 읽습니다.

즉, 인덱스가 다음에 있는 경우VARCHAR2필드에서 Oracle은 예를 들어 문자 "A"로 시작하는 모든 항목을 포함하고 마지막 항목("A"에서 "Z")까지 모든 항목을 알파벳 순으로 블록별로 읽습니다.항목이 이진 트리 인덱스로 정렬되기 때문에 Oracle은 이러한 방식으로 처리할 수 있습니다.

을 볼 때.INDEX FULL SCAN (MIN/MAX)에서, 에, 에 첫 후 수 입니다.MIN 당신이 에 ,MAXOracle은 동일한 액세스 경로를 사용할 수 있지만 이번에는 마지막 항목에서 시작하여 "Z"에서 "A"로 역방향으로 읽습니다.

로써재현▁a, a.FULL INDEX SCAN는 한 방향(전진 또는 후진)만 있고 양쪽 끝에서 동시에 시작할 수 없기 때문에 최소값과 최대값을 모두 입력하면 액세스 방법의 효율성이 떨어집니다.

다른 답변에서 제안한 것처럼 쿼리에 중요한 효율성이 필요한 경우 두 개의 고유한 쿼리에서 최소값과 최대값을 검색하여 자체 최적화를 실행할 수 있습니다.

하나의 쿼리에서 인덱스의 양쪽 가장자리를 모두 선택하지 마십시오. 다음과 같은 다른 방법으로 쿼리에 액세스합니다.

select max_date, min_date
from (select max(sdate) max_date from mytable),
       (select min(sdate) min_date from mytable)

최적화 도구는 중첩 루프(이 경우 두 번)에서 INDEX_FULL_SCAN(MIN/MAX)의 인덱스에 액세스합니다.

저는 11.2에서 같은 행동을 보이지 않는다고 말해야 합니다.

다음과 같이 테스트 케이스를 설정하고 빈센트의 코멘트에 따라 10k에서 1m 행으로 업데이트하면,

set linesize 130
set pagesize 0
create table mytable ( sdate date );

Table created.

insert into mytable
 select sysdate - level
   from dual
connect by level <= 1000000;
commit;

1000000 rows created.


Commit complete.

alter table mytable add constraint pk_mytable primary key ( sdate ) using index;

Table altered.

begin
dbms_stats.gather_table_stats( user, 'MYTABLE' 
                             , estimate_percent => 100
                             , cascade => true
                               );
end;
/

PL/SQL procedure successfully completed.

그런 다음 쿼리를 실행하면 거의 동일한 모양의 설명 계획을 볼 수 있습니다(다양한 유형의 INDEX FULL SCAN).

explain plan for select min(sdate) from mytable;

Explained.

select * from table(dbms_xplan.display);
Plan hash value: 3877058912

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |     1 |     8 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE        |        |     1 |     8 |        |      |
|   2 |   INDEX FULL SCAN (MIN/MAX)| PK_MYTABLE |     1 |     8 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

9 rows selected.

explain plan for select min(sdate), max(sdate) from mytable;

Explained.

select * from table(dbms_xplan.display);
Plan hash value: 3812733167

-------------------------------------------------------------------------------
| Id  | Operation    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     1 |     8 |   252   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE  |        |     1 |     8 |        |          |
|   2 |   INDEX FULL SCAN| PK_MYTABLE |  1000K|  7812K|   252   (0)| 00:00:04 |
-------------------------------------------------------------------------------

9 rows selected.

나의 이전 답변에서 인용하자면:

쿼리가 인덱스를 사용하지 않는 가장 일반적인 이유는 다음과 같습니다.

1. 전체 테이블 스캔을 수행하는 것이 더 빠릅니다.
2. 빈약한 통계.

질문에 게시하지 않은 내용이 없는 한, 이 표에 있는 통계를 수집하지 않았거나, 충분히 높은 추정 비율로 수집하지 않았거나, 사용한 적이 있으며, 이는 비용 기반 최적화 도구와 달리 도움이 되지 않습니다.

다음에 대한 설명서에서 인용하기analyze:

대부분의 통계 수집을 위해 DBMS_STATS 패키지를 사용하면 통계를 병렬로 수집하고, 분할된 개체에 대한 글로벌 통계를 수집하며, 다른 방법으로 통계 수집을 미세 조정할 수 있습니다.DBMS_STATS 패키지에 대한 자세한 내용은 Oracle Database PL/SQL 패키지 및 유형 참조를 참조하십시오.

비용 기반 최적화 도구와 관련이 없는 통계 수집에는 DBMS_STATS 대신 ANALYGE 문을 사용합니다.

언급URL : https://stackoverflow.com/questions/12565790/selecting-both-min-and-max-from-the-table-is-slower-than-expected