Oracle PL/SQL - NO_DATA_Found 예외가 저장 프로시저 성능에 좋지 않습니까?

Oracle PL/SQL - NO_DATA_Found 예외가 저장 프로시저 성능에 좋지 않습니까?

저는 많은 컨디셔닝이 필요한 저장 프로시저를 작성하고 있습니다.예외가 성능을 해칠 수 있다는 C#.NET 코딩의 일반적인 지식으로 인해 PL/SQL에서도 예외를 사용하는 것을 항상 피했습니다.이 저장 프로시저에서 제 조건화는 주로 레코드의 존재 여부에 따라 이루어지며, 두 가지 방법 중 하나를 수행할 수 있습니다.

SELECT COUNT(*) INTO var WHERE condition;
IF var > 0 THEN
   SELECT NEEDED_FIELD INTO otherVar WHERE condition;
....

-또는 -

SELECT NEEDED_FIELD INTO var WHERE condition;
EXCEPTION
WHEN NO_DATA_FOUND
....

두 번째 사례는 제가 보기에 조금 더 우아해 보입니다. 첫 번째 사례의 조건 이후 첫 번째 문장에서 선택해야 했던 NEED_FIELD를 사용할 수 있기 때문입니다.코드를 줄입니다.그러나 COUNT(*)를 사용하여 저장 프로시저가 더 빨리 실행된다면 처리 속도를 보충하기 위해 조금 더 입력해도 괜찮습니다.

힌트?제가 또 다른 가능성을 놓쳤나요?

편집 이 모든 것이 이미 FOR 루프에 중첩되어 있다는 것을 언급했어야 합니다.FOR 루프에서 커서를 선택으로 선언할 수 없을 것 같아서 커서를 사용하는 것과 차이가 있는지 잘 모르겠습니다.

이 작업에는 명시적인 커서를 사용하지 않습니다.스티브 F.는 암시적 커서가 사용될 수 있을 때 더 이상 사람들에게 명시적 커서를 사용하라고 조언하지 않습니다.

이 방법count(*)안전하지 않습니다.다른 세션에서 조건을 충족한 행을 삭제할 경우, 다음 행을 삭제합니다.count(*)그리고 그와의 선 앞에.select ... into코드가 처리되지 않는 예외를 던집니다.

원래 게시물의 두 번째 버전은 이 문제가 없으며 일반적으로 선호됩니다.

에는 약간의 되지 않을 것이라고 에는 즉, 예외를데이오터가고경, 것이지않변 100%할 수 .count(*)하지만 반대하는 것을 추천합니다.

32비트 윈도우즈에서 Oracle 10.2.0.1에 대해 이러한 벤치마크를 실행했습니다.저는 경과된 시간만 보고 있습니다.더 자세한 정보(예: 래치 수 및 사용된 메모리)를 제공할 수 있는 다른 테스트 하니스가 있습니다.

SQL>create table t (NEEDED_FIELD number, COND number);

테이블이 생성되었습니다.

SQL>insert into t (NEEDED_FIELD, cond) values (1, 0);

하나의 행이 생성되었습니다.

declare
  otherVar  number;
  cnt number;
begin
  for i in 1 .. 50000 loop
     select count(*) into cnt from t where cond = 1;

     if (cnt = 1) then
       select NEEDED_FIELD INTO otherVar from t where cond = 1;
     else
       otherVar := 0;
     end if;
   end loop;
end;
/

PL/SQL 프로시저가 성공적으로 완료되었습니다.

경과시간: 00:00:02.70

declare
  otherVar  number;
begin
  for i in 1 .. 50000 loop
     begin
       select NEEDED_FIELD INTO otherVar from t where cond = 1;
     exception
       when no_data_found then
         otherVar := 0;
     end;
   end loop;
end;
/

PL/SQL 프로시저가 성공적으로 완료되었습니다.

경과 시간: 00:00:03.06

SELECT INTO는 단일 행이 반환된다고 가정하므로 다음 형식의 문을 사용할 수 있습니다.

SELECT MAX(column)
  INTO var
  FROM table
 WHERE conditions;

IF var IS NOT NULL
THEN ...

SELECT는 사용 가능한 경우 값을 제공하고 NO_DATA_FOUND 예외 대신 NULL 값을 제공합니다.결과 집합에 단일 행이 포함되어 있기 때문에 MAX()에 의해 도입되는 오버헤드는 최소에서 0이 됩니다.또한 커서 기반 솔루션에 비해 컴팩트하고 원래 게시물의 2단계 솔루션과 같은 동시성 문제에 취약하지 않다는 장점이 있습니다.

@Steve의 코드에 대한 대안.

DECLARE
  CURSOR foo_cur IS 
    SELECT NEEDED_FIELD WHERE condition ;
BEGIN
  FOR foo_rec IN foo_cur LOOP
     ...
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END ;

데이터가 없으면 루프가 실행되지 않습니다.루프 커서는 많은 하우스키핑을 피하는 데 도움이 됩니다.더욱 콤팩트한 솔루션:

DECLARE
BEGIN
  FOR foo_rec IN (SELECT NEEDED_FIELD WHERE condition) LOOP
     ...
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END ;

컴파일 시 완전한 선택 문을 알고 있는 경우 작동합니다.

@디쿠키

제가 지적하고 싶은 것은 당신이 다음과 같은 대사를 생략할 수 있다는 것입니다.

EXCEPTION  
  WHEN OTHERS THEN    
    RAISE;

예외 블록을 모두 해제해도 동일한 효과를 얻을 수 있으며, 예외에 대해 보고된 줄 번호는 예외 블록에서 다시 발생한 줄이 아니라 실제로 예외가 발생한 줄이 됩니다.

Stephen Darlington은 매우 좋은 지적을 하고 있습니다. 보다 현실적인 크기의 표를 사용하도록 벤치마크를 변경하면 다음을 사용하여 표를 10000행으로 작성할 수 있습니다.

begin 
  for i in 2 .. 10000 loop
    insert into t (NEEDED_FIELD, cond) values (i, 10);
  end loop;
end;

그런 다음 벤치마크를 다시 실행합니다. (적절한 시간을 얻기 위해 루프 카운트를 5000개로 줄여야 했습니다.)

declare
  otherVar  number;
  cnt number;
begin
  for i in 1 .. 5000 loop
     select count(*) into cnt from t where cond = 0;

     if (cnt = 1) then
       select NEEDED_FIELD INTO otherVar from t where cond = 0;
     else
       otherVar := 0;
     end if;
   end loop;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.34

declare
  otherVar  number;
begin
  for i in 1 .. 5000 loop
     begin
       select NEEDED_FIELD INTO otherVar from t where cond = 0;
     exception
       when no_data_found then
         otherVar := 0;
     end;
   end loop;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.10

예외가 있는 방법은 이제 두 배 이상 빠릅니다.따라서 거의 모든 경우 방법은 다음과 같습니다.

SELECT NEEDED_FIELD INTO var WHERE condition;
EXCEPTION
WHEN NO_DATA_FOUND....

가는 길입니다.정확한 결과를 제공하며 일반적으로 가장 빠릅니다.

중요한 경우 두 가지 옵션을 모두 벤치마킹해야 합니다.

그렇긴 하지만, 저는 항상 예외적인 방법을 사용했는데, 그 이유는 데이터베이스를 한 번만 치는 것이 낫기 때문입니다.

예, 커서를 사용하지 않습니다.

DECLARE
  CURSOR foo_cur IS 
    SELECT NEEDED_FIELD WHERE condition ;
BEGIN
  OPEN foo_cur;
  FETCH foo_cur INTO foo_rec;
  IF foo_cur%FOUND THEN
     ...
  END IF;
  CLOSE foo_cur;
EXCEPTION
  WHEN OTHERS THEN
    CLOSE foo_cur;
    RAISE;
END ;

분명히 이것은 더 많은 코드이지만 흐름 제어로 예외를 사용하지 않습니다. Steve Feuerstein의 PL/SQL 프로그래밍 책에서 제 PL/SQL의 대부분을 배웠기 때문에 저는 좋은 것이라고 생각합니다.

이게 더 빠른지 아닌지 저는 모르겠습니다 (요즘은 PL/SQL을 거의 하지 않습니다).

중첩된 커서 루프를 사용하는 것보다 테이블 사이에 외부 조인이 있는 하나의 커서 루프를 사용하는 것이 더 효율적입니다.

BEGIN
    FOR rec IN (SELECT a.needed_field,b.other_field
                  FROM table1 a
                  LEFT OUTER JOIN table2 b
                    ON a.needed_field = b.condition_field
                 WHERE a.column = ???)
    LOOP
       IF rec.other_field IS NOT NULL THEN
         -- whatever processing needs to be done to other_field
       END IF;
    END LOOP;
END;

루프에 사용할 때는 open을 사용할 필요가 없습니다.

declare
cursor cur_name is  select * from emp;
begin
for cur_rec in cur_name Loop
    dbms_output.put_line(cur_rec.ename);
end loop;
End ;

또는

declare
cursor cur_name is  select * from emp;
cur_rec emp%rowtype;
begin
Open cur_name;
Loop
Fetch cur_name into  Cur_rec;
   Exit when cur_name%notfound;
    dbms_output.put_line(cur_rec.ename);
end loop;
Close cur_name;
End ;

여기서 헛수고를 하고 있을지도 모르지만, 루프를 위해 커서를 벤치마크했고, no_data_found 메서드만큼 잘 수행했습니다.

declare
  otherVar  number;
begin
  for i in 1 .. 5000 loop
     begin
       for foo_rec in (select NEEDED_FIELD from t where cond = 0) loop
         otherVar := foo_rec.NEEDED_FIELD;
       end loop;
       otherVar := 0;
     end;
   end loop;
end;

PL/SQL 프로시저가 성공적으로 완료되었습니다.

경과: 00:00:0218

카운트(*)는 항상 실제 카운트 또는 0 - 0을 반환하므로 예외를 발생시키지 않습니다.저는 카운트가 필요합니다.

첫 번째 (훌륭한) 대답은 다음과 같습니다.

count()가 있는 메서드는 안전하지 않습니다.다른 세션에서 count(*)가 표시된 행 뒤와 select(선택)가 표시된 행 앞에 조건을 충족한 행을 삭제하는 경우...코드가 처리되지 않는 예외를 에 던집니다.

그렇지 않습니다. 주어진 논리적 작업 단위 내에서 Oracle은 완전히 일관됩니다.다른 사용자가 개수와 선택한 Oracle 간에 행 삭제를 커밋하더라도 활성 세션의 경우 로그에서 데이터를 가져옵니다.그렇지 않으면 "snapshot too old" 오류가 발생합니다.

언급URL : https://stackoverflow.com/questions/221909/oracle-pl-sql-are-no-data-found-exceptions-bad-for-stored-procedure-performanc