Oracle PL/SQL - Исключены ли NO_DATA_FOUND Исключения для производительности хранимой процедуры?

Я пишу хранимую процедуру, в которой должно быть много условий. С учетом общих знаний из С#.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
....

Второй случай кажется мне более изящным, потому что тогда я могу использовать NEEDED_FIELD, который мне пришлось бы выбрать в первом утверждении после условия в первом случае. Меньше кода. Но если хранимая процедура будет работать быстрее с помощью COUNT (*), то я не против печатать немного больше, чтобы вычислить скорость обработки.

Любые подсказки? Упускаю ли я еще одну возможность?

ИЗМЕНИТЬ Я должен был упомянуть, что все это уже вложен в FOR LOOP. Не уверен, что это имеет значение с использованием курсора, так как я не думаю, что могу DECLARE курсора в качестве выбора в FOR LOOP.

Ответы

Ответ 1

Я бы не использовал явный курсор для этого. Стив Ф. больше не советует людям использовать явные курсоры при использовании неявного курсора.

Метод с count(*) небезопасен. Если другой сеанс удаляет строку, которая удовлетворяет условию после строки с count(*), а перед строкой с select ... into код генерирует исключение, которое не будет обработано.

Вторая версия исходного сообщения не имеет этой проблемы, и обычно это предпочтительнее.

Тем не менее, с использованием исключения есть небольшие накладные расходы, и если вы на 100% уверены, что данные не изменятся, вы можете использовать count(*), но я рекомендую против него.

Я провел эти тесты на Oracle 10.2.0.1 на 32-битной Windows. Я смотрю только на прошедшее время. Существуют другие тестовые жгуты, которые могут дать более подробную информацию (например, количество защелок и память).

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

Создана таблица.

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

Создана 1 строка.

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

Ответ 2

Так как SELECT INTO предполагает, что будет возвращена одна строка, вы можете использовать оператор формы:

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

IF var IS NOT NULL
THEN ...

SELECT предоставит вам значение, если оно доступно, и значение NULL вместо исключения NO_DATA_FOUND. Накладные расходы, введенные MAX(), будут минимальными к нулю, поскольку набор результатов содержит одну строку. Он также имеет то преимущество, что он компактен относительно решения на основе курсора и не уязвим для проблем concurrency, таких как двухэтапное решение в исходном сообщении.

Ответ 3

Альтернатива коду @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 ;

Цикл не выполняется, если нет данных. Петли курсора FOR - это путь, который помогает избежать многих домашних хозяйств. Еще более компактное решение:

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

Что работает, если вы знаете полный оператор select во время компиляции.

Ответ 4

@DCookie

Я просто хочу указать, что вы можете оставить строки, которые говорят

EXCEPTION  
  WHEN OTHERS THEN    
    RAISE;

Вы получите тот же эффект, если вы оставите все блоки исключений вместе, а номер строки, сообщаемой для исключения, будет линией, в которой действительно выбрано исключение, а не строкой в ​​блоке исключений, где она была повторно -raised.

Ответ 5

Стивен Дарлингтон делает очень хорошую оценку, и вы можете видеть, что если вы измените мой тест, чтобы использовать таблицу с более реалистичным размером, если я заполню таблицу до 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....

- это путь. Он даст правильные результаты и, как правило, самый быстрый.

Ответ 6

Если это важно, вам действительно нужно проверить оба варианта!

Сказав это, я всегда использовал метод исключения, рассуждая, что лучше всего ударить только по базе данных.

Ответ 7

Да, вам не хватает курсоров

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 ;

По правде говоря, это больше кода, но он не использует EXCEPTION как управление потоком, который, узнав большую часть моего PL/SQL из книги программирования PL/SQL от Steve Feuerstein, считаю, что это хорошая вещь.

Является ли это быстрее или нет, я не знаю (сейчас я очень мало PL/SQL).

Ответ 8

Вместо того, чтобы иметь вложенные курсорные петли, более эффективным подходом было бы использовать один цикл курсора с внешним соединением между таблицами.

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;

Ответ 9

Может быть, здесь можно убить мертвую лошадь, но я поставил курсор на цикл, и это произошло так же, как и метод 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: 02.18

Ответ 10

вам не нужно открывать, когда вы используете для циклов.

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 ;

Ответ 11

Счетчик (*) никогда не будет увеличивать исключение, потому что он всегда возвращает фактический счетчик или 0 - ноль, несмотря ни на что. Я бы использовал count.

Ответ 12

Объявлен первый (отличный) ответ -

Метод с count() небезопасен. Если другой сеанс удаляет строку, которая удовлетворяет условию после строки с счетчиком (*), а перед строкой с выбором... в код будет генерировать исключение, которое не будет обработано.

Не так. В рамках данной логической единицы работы Oracle полностью согласуется. Даже если кто-то совершает удаление строки между счетчиком и выбранным Oracle, для активного сеанса получает данные из журналов. Если это невозможно, вы получите сообщение об ошибке "Снимки слишком старые".