SQL 성능: DISTINCT 대 GROUP BY 선택

SQL 성능: DISTINCT 대 GROUP BY 선택

약간 느린 속도로 실행되고 있는 기존 Oracle 데이터베이스 기반 애플리케이션에 대한 쿼리 시간을 개선하려고 노력했습니다.응용 프로그램은 아래 쿼리와 같이 실행하는 데 한 시간 이상 걸릴 수 있는 몇 가지 큰 쿼리를 실행합니다. 교체DISTINCTGROUP BY아래 쿼리의 절은 실행 시간을 100분에서 10초로 단축했습니다.제가 알기로는SELECT DISTINCT그리고.GROUP BY거의 같은 방식으로 작동합니다.실행 시간 사이에 왜 그렇게 큰 차이가 나는 겁니까?백엔드에서 쿼리가 실행되는 방식의 차이점은 무엇입니까?지금까지 어떤 상황이 발생한 적이 있습니까?SELECT DISTINCT더 빨리 달립니까?

에서 ": 다쿼리에서음참고"입니다.WHERE TASK_INVENTORY_STEP.STEP_TYPE = 'TYPE A'결과를 필터링할 수 있는 여러 가지 방법 중 하나일 뿐입니다.이 예제는 열이 포함되지 않은 모든 테이블을 결합하는 이유를 보여주기 위해 제공되었습니다.SELECT 가능한 모든 의 약 됩니다.

을 사용한 »DISTINCT:

SELECT DISTINCT 
    ITEMS.ITEM_ID,
    ITEMS.ITEM_CODE,
    ITEMS.ITEMTYPE,
    ITEM_TRANSACTIONS.STATUS,
    (SELECT COUNT(PKID) 
        FROM ITEM_PARENTS 
        WHERE PARENT_ITEM_ID = ITEMS.ITEM_ID
        ) AS CHILD_COUNT
FROM
    ITEMS
    INNER JOIN ITEM_TRANSACTIONS 
        ON ITEMS.ITEM_ID = ITEM_TRANSACTIONS.ITEM_ID 
        AND ITEM_TRANSACTIONS.FLAG = 1
    LEFT OUTER JOIN ITEM_METADATA 
        ON ITEMS.ITEM_ID = ITEM_METADATA.ITEM_ID
    LEFT OUTER JOIN JOB_INVENTORY 
        ON ITEMS.ITEM_ID = JOB_INVENTORY.ITEM_ID     
    LEFT OUTER JOIN JOB_TASK_INVENTORY 
        ON JOB_INVENTORY.JOB_ITEM_ID = JOB_TASK_INVENTORY.JOB_ITEM_ID
    LEFT OUTER JOIN JOB_TASKS 
        ON JOB_TASK_INVENTORY.TASKID = JOB_TASKS.TASKID                              
    LEFT OUTER JOIN JOBS 
        ON JOB_TASKS.JOB_ID = JOBS.JOB_ID
    LEFT OUTER JOIN TASK_INVENTORY_STEP 
        ON JOB_INVENTORY.JOB_ITEM_ID = TASK_INVENTORY_STEP.JOB_ITEM_ID 
    LEFT OUTER JOIN TASK_STEP_INFORMATION 
        ON TASK_INVENTORY_STEP.JOB_ITEM_ID = TASK_STEP_INFORMATION.JOB_ITEM_ID
WHERE 
    TASK_INVENTORY_STEP.STEP_TYPE = 'TYPE A'
ORDER BY 
    ITEMS.ITEM_CODE

을 사용한 »GROUP BY:

SELECT
    ITEMS.ITEM_ID,
    ITEMS.ITEM_CODE,
    ITEMS.ITEMTYPE,
    ITEM_TRANSACTIONS.STATUS,
    (SELECT COUNT(PKID) 
        FROM ITEM_PARENTS 
        WHERE PARENT_ITEM_ID = ITEMS.ITEM_ID
        ) AS CHILD_COUNT
FROM
    ITEMS
    INNER JOIN ITEM_TRANSACTIONS 
        ON ITEMS.ITEM_ID = ITEM_TRANSACTIONS.ITEM_ID 
        AND ITEM_TRANSACTIONS.FLAG = 1
    LEFT OUTER JOIN ITEM_METADATA 
        ON ITEMS.ITEM_ID = ITEM_METADATA.ITEM_ID
    LEFT OUTER JOIN JOB_INVENTORY 
        ON ITEMS.ITEM_ID = JOB_INVENTORY.ITEM_ID     
    LEFT OUTER JOIN JOB_TASK_INVENTORY 
        ON JOB_INVENTORY.JOB_ITEM_ID = JOB_TASK_INVENTORY.JOB_ITEM_ID
    LEFT OUTER JOIN JOB_TASKS 
        ON JOB_TASK_INVENTORY.TASKID = JOB_TASKS.TASKID                              
    LEFT OUTER JOIN JOBS 
        ON JOB_TASKS.JOB_ID = JOBS.JOB_ID
    LEFT OUTER JOIN TASK_INVENTORY_STEP 
        ON JOB_INVENTORY.JOB_ITEM_ID = TASK_INVENTORY_STEP.JOB_ITEM_ID 
    LEFT OUTER JOIN TASK_STEP_INFORMATION 
        ON TASK_INVENTORY_STEP.JOB_ITEM_ID = TASK_STEP_INFORMATION.JOB_ITEM_ID
WHERE 
    TASK_INVENTORY_STEP.STEP_TYPE = 'TYPE A'
GROUP BY
    ITEMS.ITEM_ID,
    ITEMS.ITEM_CODE,
    ITEMS.ITEMTYPE,
    ITEM_TRANSACTIONS.STATUS
ORDER BY 
    ITEMS.ITEM_CODE

은 Oracle을 쿼리 계획입니다.DISTINCT:

은 Oracle을 쿼리 계획입니다.GROUP BY:

는 아마 능차는아하쿼실때것입다니의 입니다.SELECT절나는 그것이 구별하기 전에 모든 행에 대해 이 쿼리를 다시 실행하고 있습니다.를 위해group by그룹 다음에 한 번 실행됩니다.

대신 조인으로 대체해 보십시오.

select . . .,
       parentcnt
from . . . left outer join
      (SELECT PARENT_ITEM_ID, COUNT(PKID) as parentcnt
       FROM ITEM_PARENTS 
      ) p
      on items.item_id = p.parent_item_id

그 나는꽤니다합신확▁sure▁that라고 꽤 확신합니다.GROUP BY그리고.DISTINCT실행 계획이 거의 같습니다.

(설명 계획이 없기 때문에) 추측해야 하기 때문에 여기서 차이점은 인라인 하위 쿼리가 다음 이후에 실행되는 IMO입니다.GROUP BY하지만 그 전에DISTINCT.

따라서 쿼리가 1M개의 행을 반환하고 1k개의 행으로 집계되는 경우:

• 그GROUP BY은 1000번 실행했을 것입니다.
• 에 면에그 반그.DISTINCT쿼리는 하위 쿼리를 1000000번 실행했을 것입니다.

tkprof 설명 계획은 이 가설을 입증하는 데 도움이 될 것입니다.

---

이 문제를 수 합니다. " /▁from▁▁is▁a▁rows▁have▁want▁all▁obviously▁i▁query▁you것▁item중▁find생▁toitem주것,이▁the목하다고이▁thising▁and▁is▁reader▁written▁the는하:▁that요▁optimizer을▁way 당신은 분명히 다음을 포함하는 item/item_transactions의 모든 행을 찾고자 합니다.TASK_INVENTORY_STEP.STEP_TYPE" ATYPE A"을▁of▁"다▁with"의 으로.

IMO 당신의 질문은 더 나은 계획을 가지고 있고 다음과 같이 적으면 더 쉽게 읽을 수 있을 것입니다.

SELECT ITEMS.ITEM_ID,
       ITEMS.ITEM_CODE,
       ITEMS.ITEMTYPE,
       ITEM_TRANSACTIONS.STATUS,
       (SELECT COUNT(PKID) 
          FROM ITEM_PARENTS 
         WHERE PARENT_ITEM_ID = ITEMS.ITEM_ID) AS CHILD_COUNT
  FROM ITEMS
  JOIN ITEM_TRANSACTIONS 
    ON ITEMS.ITEM_ID = ITEM_TRANSACTIONS.ITEM_ID 
   AND ITEM_TRANSACTIONS.FLAG = 1
 WHERE EXISTS (SELECT NULL
                 FROM JOB_INVENTORY   
                 JOIN TASK_INVENTORY_STEP 
                   ON JOB_INVENTORY.JOB_ITEM_ID=TASK_INVENTORY_STEP.JOB_ITEM_ID
                WHERE TASK_INVENTORY_STEP.STEP_TYPE = 'TYPE A'
                  AND ITEMS.ITEM_ID = JOB_INVENTORY.ITEM_ID)

대부분의 경우 DISTINCT는 쿼리가 제대로 작성되지 않았다는 신호일 수 있습니다(좋은 쿼리는 중복 항목을 반환하지 않아야 하기 때문).

또한 4개의 테이블은 원래 선택한 항목에서 사용되지 않습니다.

첫 번째로 주의해야 할 것은 의 사용입니다.Distinct코드 냄새를 나타냅니다. 안티바이러스라고도 합니다.일반적으로 중복 데이터를 생성하는 조인 또는 추가 조인이 누락되었음을 의미합니다.위의 당신의 질문을 보면, 저는 그 이유라고 추측합니다.group by) 은 ( 쿼리를 보지 않고) 의 입니다.group by반환되는 레코드 수를 줄입니다.에 반에면.distinct결과 집합을 날려버리고 행별 비교를 수행하는 중입니다.

접근 방식 업데이트

죄송해요, 좀 더 분명히 말했어야 했어요.사용자가 시스템에서 특정 작업을 수행할 때 레코드가 생성되므로 일정이 없습니다.사용자는 하루 또는 시간당 수백 개의 레코드를 생성할 수 있습니다.중요한 것은 사용자가 검색을 실행할 때마다 최신 레코드를 반환해야 한다는 것입니다. 따라서 구체화된 보기가 여기서 작동하는지 의심스럽습니다. 특히 쿼리를 채우는 데 시간이 오래 걸릴 경우에는 더욱 그렇습니다.

저는 이것이 구체화된 견해를 사용하는 정확한 이유라고 믿습니다.그래서 그 과정은 이런 식으로 진행될 것입니다.사용자가 시스템에서 임의의 작업을 수행한 후에는 "새로운" 데이터에만 관심을 갖는다는 것을 알고 있기 때문에 장시간 실행되는 쿼리를 구체화된 보기를 구축하는 요소로 간주합니다.따라서 백엔드에서 지속적으로 새로 고칠 수 있는 이러한 기본적인 구체화된 뷰에 대해 쿼리를 수행하는 것입니다. 관련 지속성 전략이 구체화된 뷰를 방해해서는 안 됩니다(한 번에 수백 개의 레코드를 지속해도 아무것도 손상되지 않습니다).이를 통해 Oracle은 읽기 잠금 기능을 이용할 수 있습니다(데이터를 읽는 소스 수는 상관없고 작성자만 신경씁니다).최악의 경우 사용자는 마이크로초 동안 데이터를 "도둑질"할 수 있기 때문에 이것이 월가의 금융 거래 시스템이나 원자로 시스템이 아닌 한 이러한 "블립"은 심지어 가장 예리한 사용자도 알아채지 못할 것입니다.

이 방법에 대한 코드 예제:

create materialized view dept_mv FOR UPDATE as select * from dept; 

새로 고침을 호출하지 않는 한 지속적인 데이터가 손실되지 않습니다.구체화된 견해를 언제 다시 "기준선"으로 설정할지 결정하는 것은 귀하에게 달려 있습니다(아마도 자정?).

GROUP BY를 사용하여 각 그룹에 집계 연산자를 적용하고 중복만 제거해야 하는 경우 DISTINCT를 적용해야 합니다.

저는 그 공연이 똑같다고 생각합니다.

당신의 경우 GROUP BY를 사용해야 할 것 같습니다.

언급URL : https://stackoverflow.com/questions/13956768/sql-performance-select-distinct-versus-group-by