SQL Server에서 빠르게 실행되는 Oracle에서 느리게 실행되는 SELECT 쿼리 최적화

SQL Server에서 빠르게 실행되는 Oracle에서 느리게 실행되는 SELECT 쿼리 최적화

Oracle에서 다음 SQL 문을 실행하려고 하는데 실행하는 데 시간이 오래 걸립니다.

SELECT orderID FROM tasks WHERE orderID NOT IN 
(SELECT DISTINCT orderID FROM tasks WHERE
 engineer1 IS NOT NULL AND engineer2 IS NOT NULL)

IN 절에 있는 부분만 실행하면 Oracle에서 매우 빠르게 실행됩니다.

SELECT DISTINCT orderID FROM tasks WHERE
engineer1 IS NOT NULL AND engineer2 IS NOT NULL

Oracle에서 전체 설명이 이렇게 오래 걸리는 이유는 무엇입니까?SQL Server에서는 전체 문이 빠르게 실행됩니다.

또는 더 단순한/다른/더 나은 SQL 문이 있습니까?

문제에 대한 추가 세부 정보:

• 각 주문은 많은 작업으로 이루어집니다.
• 각 주문이 할당되거나(하나 이상의 작업에 엔지니어1 및 엔지니어2가 설정됨) 주문이 할당되지 않을 수 있습니다(모든 작업에 엔지니어 필드의 null 값이 있음).
• 나는 모든 주문을 찾으려고 노력하고 있습니다.할당되지 않은 ID입니다.

혹시라도 차이가 날 경우를 대비해서 표에는 ~12만 행이 있고, 주문당 3개의 작업이 있으므로 ~40만 개의 주문이 있습니다.

답변에 대한 답변:

• SQL Server와 Oracle 모두에서 작동하는 SQL 문을 선호합니다.
• 작업에는 주문에 대한 인덱스만 있습니다.ID 및 태스크신분증.
• 나는 존재하지 않는 문장 버전을 시도했지만 취소하기 전에 3분 넘게 실행되었습니다.성명서의 JOIN 버전이 필요하신가요?
• 주문과 함께 "주문" 표도 있습니다.ID 열.하지만 저는 원래 SQL 문에 포함시키지 않음으로써 질문을 단순화하려고 했습니다.

원본 SQL 문에서 하위 쿼리는 정적이고 한 번만 실행해야 함에도 불구하고 SQL 문의 첫 번째 부분에 있는 각 행에 대해 매번 실행된다고 생각합니다.

실행 중

ANALYZE TABLE tasks COMPUTE STATISTICS;

원래 SQL 문을 훨씬 빠르게 실행할 수 있었습니다.

왜 이 작업을 수행해야 하는지, 언제 다시 실행해야 하는지 여전히 궁금하지만,

이 통계는 테이블의 행 수, 행의 평균 너비, 열당 최고 및 최저 값, 열당 고유 값 수, 인덱스의 클러스터링 팩터 등 다양한 실행 계획의 효율성을 결정하는 데 필요한 비용 기반 최적화 도구 정보를 제공합니다.

작은 데이터베이스에서 매일 밤 통계를 수집하고 그대로 두는 작업을 설정할 수 있습니다.사실, 이것은 10g 미만의 기본값입니다.대규모 구현의 경우 일반적으로 실행 계획의 안정성을 데이터 변경 방식과 비교하여 따져봐야 하는데, 이는 까다로운 균형입니다.

Oracle에는 실행 시 관련 통계를 확인하기 위해 테이블을 샘플링하는 데 사용되는 "동적 샘플링" 기능도 있습니다.데이터 웨어하우스에서는 샘플링의 오버헤드가 장기간 실행되는 쿼리의 잠재적인 성능 향상보다 훨씬 더 자주 사용됩니다.

관련 테이블을 분석하면 이러한 유형의 문제가 해결되는 경우가 많습니다(따라서 Oracle은 데이터 분포에 대해 더 잘 파악할 수 있습니다).

ANALYZE TABLE tasks COMPUTE STATISTICS;

"IN" - 절은 오라클에서 매우 느린 것으로 알려져 있습니다.실제로 Oracle의 내부 쿼리 최적화 프로그램은 "IN"이 양호한 문을 처리할 수 없습니다."EXIST"를 사용해 보십시오.

SELECT orderID FROM tasks WHERE orderID NOT EXISTS 
    (SELECT DISTINCT orderID FROM tasks WHERE
         engineer1 IS NOT NULL AND engineer2 IS NOT NULL)`print("code sample");`

주의:쿼리가 동일한 데이터 결과를 빌드하는지 확인하십시오.

에디스는 이렇게 말합니다. 웁스, 질문이 잘 형성되지 않았지만 일반적인 생각은 정확합니다.Oracle은 두 번째(내부) 쿼리에 대한 전체 테이블 검색을 수행하고 결과를 작성한 다음 첫 번째(외부) 쿼리와 비교해야 하므로 속도가 느려집니다.

SELECT orderID AS oid FROM tasks WHERE NOT EXISTS 
    (SELECT DISTINCT orderID AS oid2 FROM tasks WHERE
         engineer1 IS NOT NULL AND engineer2 IS NOT NULL and oid=oid2)

또는 비슷한 것;-)

대신 조인을 사용해 보겠습니다.

SELECT 
    t.orderID 
FROM 
    tasks  t
    LEFT JOIN tasks t1
        ON t.orderID =  t1.orderID
        AND t1.engineer1 IS NOT NULL 
        AND t1.engineer2 IS NOT NULL
WHERE
    t1.orderID IS NULL 

또한 원래 쿼리가 다음과 같이 지정된 경우에는 더 쉽게 이해할 수 있습니다.

SELECT orderID FROM orders WHERE orderID NOT IN 
(SELECT DISTINCT orderID FROM tasks WHERE
 engineer1 IS NOT NULL AND engineer2 IS NOT NULL)

(모든 주문이 나열된 주문 표가 있는 경우)

그런 다음 조인을 사용하여 다시 작성할 수 있습니다.

SELECT 
    o.orderID 
FROM 
    orders o
    LEFT JOIN tasks t
        ON o.orderID =  t.orderID
        AND t.engineer1 IS NOT NULL 
        AND t.engineer2 IS NOT NULL
WHERE
    t.orderID IS NULL 

TZQ에 동의합니다.TZIO, 당신의 질문을 이해하지 못합니다.

쿼리가 의미가 있다고 가정할 경우 일부에서 제안하거나 피하는 것처럼 EXITES를 사용해 보는 것이 좋습니다.IN이 항상 나쁜 것은 아니며 실제로 존재하는 것보다 더 나은 성능을 보여줄 수 있는 경우가 있을 수 있습니다.

질문 제목은 별로 도움이 되지 않습니다.이 쿼리를 한 Oracle 데이터베이스에 설정하여 느리게 실행하고 다른 데이터베이스에서 빠르게 실행할 수 있습니다.데이터베이스가 쿼리, 개체 통계, SYS 스키마 통계 및 매개 변수와 서버 성능을 해결하는 방법을 결정하는 많은 요인이 있습니다.SQL 서버 대Oracle이 문제가 아닙니다.

쿼리 조정 및 성능에 관심이 있고 검색할 구글 용어 중 일부를 더 알고 싶은 사람들을 위해 "오크 테이블 오라클"과 "오라클 조나단 루이스"가 있습니다.

몇 가지 질문:

• 작업에는 몇 개의 행이 있습니까?
• 어떤 인덱스가 정의되어 있습니까?
• 최근에 표를 분석했습니까?

동일한 쿼리를 작성하는 또 다른 방법은 다음과 같습니다.

select orderid from tasks
minus
select orderid from tasks
where engineer1 IS NOT NULL AND engineer2 IS NOT NULL

그러나 쿼리에 "주문" 테이블이 포함될 것으로 예상합니다.

select orderid from ORDERS
minus
select orderid from tasks
where engineer1 IS NOT NULL AND engineer2 IS NOT NULL

또는

select orderid from ORDERS
where orderid not in
( select orderid from tasks
  where engineer1 IS NOT NULL AND engineer2 IS NOT NULL
)

또는

select orderid from ORDERS
where not exists
( select null from tasks
  where tasks.orderid = orders.orderid
  and   engineer1 IS NOT NULL OR engineer2 IS NOT NULL
)

몇몇 사람들이 거의 정확한 SQL을 가지고 있지만 내부 쿼리와 외부 쿼리 간의 결합을 놓치고 있다고 생각합니다.
사용해 보십시오.

SELECT t1.orderID 
FROM   tasks t1
WHERE  NOT EXISTS
       (SELECT 1 
        FROM   tasks t2 
        WHERE  t2.orderID   = t1.orderID
        AND    t2.engineer1 IS NOT NULL 
        AND    t2.engineer2 IS NOT NULL)

"왜 이 작업을 수행해야 하는지, 그리고 언제 다시 실행해야 하는지 여전히 궁금하지만요."

이 통계는 테이블의 행 수, 행의 평균 너비, 열당 최고 및 최저 값, 열당 고유 값 수, 인덱스의 클러스터링 팩터 등 다양한 실행 계획의 효율성을 결정하는 데 필요한 비용 기반 최적화 도구 정보를 제공합니다.

작은 데이터베이스에서 매일 밤 통계를 수집하고 그대로 두는 작업을 설정할 수 있습니다.사실, 이것은 10g 미만의 기본값입니다.대규모 구현의 경우 일반적으로 실행 계획의 안정성을 데이터 변경 방식과 비교하여 따져봐야 하는데, 이는 까다로운 균형입니다.

Oracle에는 실행 시 관련 통계를 확인하기 위해 테이블을 샘플링하는 데 사용되는 "동적 샘플링" 기능도 있습니다.데이터 웨어하우스에서는 샘플링의 오버헤드가 장기간 실행되는 쿼리의 잠재적인 성능 향상보다 훨씬 더 자주 사용됩니다.

당신의 질문은 다음과 같지 않습니까?

SELECT orderID FROM tasks
WHERE engineer1 IS NOT NULL OR engineer2 IS NOT NULL

?

어때요?

SELECT DISTINCT orderID FROM tasks t1 WHERE NOT EXISTS (SELECT * FROM tasks t2 WHERE t2.orderID=t1.orderID AND (engineer1 IS NOT NULL OR engineer2 IS NOT NULL));

저는 최적화 전문가는 아니지만 Oracle 데이터베이스의 일부 인덱스도 간과했을 수 있습니다.

다른 옵션은 MINUS(MSSQL의 경우 제외)를 사용하는 것입니다.

SELECT orderID FROM tasks
MINUS
SELECT DISTINCT orderID FROM tasks WHERE engineer1 IS NOT NULL 
AND engineer2 IS NOT NULL

ORDERs 테이블을 만들려면 할당된 플래그를 추가하고 비트맵 색인을 만듭니다.또한 이 방법을 사용하면 비즈니스 로직을 수정하여 플래그를 계속 업데이트할 수 있지만 쿼리가 매우 빠르게 실행됩니다.응용프로그램에 대한 쿼리의 중요도에 따라 다릅니다.

답은 간단할수록 좋습니다.하위 쿼리, 조인, 구분 및 그룹화 기준은 필요하지 않습니다.

Oracle Optimizer는 MINUS 문을 잘 처리합니다.MINUS를 사용하여 쿼리를 다시 작성하면 쿼리가 매우 빠르게 실행될 가능성이 높습니다.

SELECT orderID FROM tasks
MINUS
SELECT DISTINCT orderID FROM tasks WHERE
 engineer1 IS NOT NULL AND engineer2 IS NOT NULL

표에서 "engineer1은 NULL이 아니며 engineer2는 NULL이 아닙니다"라는 조건을 충족하는 행의 비율은 얼마입니까?

이렇게 하면 인덱스를 사용하여 관련 주문 ID를 검색할 가치가 있는지 여부를 대략적으로 알 수 있습니다.

색인화되지 않은 사례를 매우 잘 처리하는 Oracle에서 쿼리를 작성하는 또 다른 방법은 다음과 같습니다.

select distinct orderid
from
(
select orderid,
       max(case when engineer1 is null and engineer2 is null then 0 else 1)
          over (partition by orderid)
          as max_null_finder
from   tasks
)
where max_null_finder = 0

새로운 테이크.

iff:

• COUNT() 함수는 NULL 값을 카운트하지 않습니다.

그리고.

• 당신은 주문을 원합니다.engineer1 또는 engineer2가 값으로 설정된 작업이 없는 모든 작업의 ID

그러면 이것은 당신이 원하는 것을 할 것입니다.

SELECT orderID
FROM tasks
GROUP BY orderID
HAVING COUNT(engineer1) = 0 AND COUNT(engineer2) = 0

테스트해 보세요.

I agree with ΤΖΩΤΖΙΟΥ and wearejimbo that your query should be...

SELECT DISTINCT orderID FROM Tasks 
WHERE Engineer1 IS NULL OR Engineer2 IS NULL;

SQL Server에 대해 잘 모르지만 null 행이 인덱스에 없기 때문에 이 쿼리는 인덱스를 사용할 수 없습니다.이에 대한 해결책은 null 값 행만 포함하는 함수 기반 인덱스를 만들 수 있는 방식으로 쿼리를 다시 작성하는 것입니다.이 작업은 NVL2로 수행할 수 있지만 SQL Server로 이동할 수 없습니다.

가장 좋은 대답은 기준을 충족하는 것이 아니라 해당 플랫폼에 가장 적합한 각 플랫폼에 대해 다른 설명을 작성하는 것이라고 생각합니다.

Engineer1 및 Engineer2 열에 인덱스가 없는 경우 SQL Server 및 Oracle에 있는 동일한 항목에 대해 항상 Table Scan을 생성합니다.

할당되지 않은 작업이 있는 주문만 필요한 경우 다음과 같은 작업이 두 플랫폼 모두에서 잘 작동하지만 쿼리 성능을 향상시키기 위해 작업 테이블에 인덱스를 추가하는 것도 고려해야 합니다.

SELECT DISTINCT orderID 
FROM tasks 
WHERE (engineer1 IS NULL OR engineer2 IS NULL)

다음은 고객이 원하는 것을 제공하는 대안적인 접근 방식입니다.

SELECT orderID
 FROM tasks
 GROUP BY orderID
 HAVING COUNT(engineer1) = 0 OR COUNT(engineer2) = 0

HAVING 조항에 "AND"를 원하는지 "OR"을 원하는지 잘 모르겠습니다.비즈니스 논리에 따르면 이 두 필드는 모두 채워지거나 둘 다 NULL이어야 합니다. 이 조건이 보장된다면 engineer1만 확인하는 것으로 조건을 줄일 수 있습니다.

원래 쿼리는 주문당 여러 행을 제공합니다.제 아이디는 하나밖에 안 주잖아요.당신은 주문만 받고 있기 때문에 괜찮을 것 같습니다.신분증.

하위 쿼리는 Oracle에서 "불량"합니다.일반적으로 조인을 사용하는 것이 좋습니다.

다음은 join: http://www.dba-oracle.com/sql/t_rewrite_subqueries_performance.htm 으로 하위 쿼리를 다시 작성하는 방법에 대한 기사입니다.

언급URL : https://stackoverflow.com/questions/120504/optimising-a-select-query-that-runs-slow-on-oracle-which-runs-quickly-on-sql-ser