Как сбросить sequence oracle
Обновлено: 30.06.2024
Сиквенс – это структура для генерации уникальных целочисленных значений. Только одна сессия может запросит следующее значение и таким образом увеличить счётчик. Поэтому все сгенерированные значения будут уникальными.
Сиквенс это бесценный инструмент для генерации значений первичного ключа. Многие приложения нуждаются в автоматически сгенерированных значениях первичного ключа. Например номерпокупателя и номер заказа: бизнес-аналитики могут решить что каждый заказ должен иметь уникальный номер, которые последовательно увеличивается. В других приложениях вы можете не иметь явных бизнес требований к ключам, но они понядобятся для организации ссылочной целостности. Например в учёте телефонных звонков: с точки зрения бизнес идентификатором является телефонный номер (строка) и звонком будет значение телефона и время начала звонка. Эти типы данных очень сложные для использования их как первичных ключей для больших объёмов которые обязательно будут в системе учёта звонков. Для записи этой информации гораздо легче использовать простые численные столбцы для определения первичных и внешних ключей. Значения этих столбцов могут основываться на сиквенсах.
Мехнизм сиквенсов не зависит от таблиц, механизма блокировок и транзакций. Это значит что сиквенс может генерировать тысячи уникальных значений в минуту – гораздо быстрее чем методы выборки данных, обновления и подтверждения изменений.
На рисунке 7-6 показано как две сессий выбирают значения из сиквенса SEQ1. Обратите внимание что каждый запрос SEQ1.NEXTVAL генерирует уникальный номер. Значение создаётся по порядку в зависимости от времени обращения, и значение увеличивается глобально а не для одной сессии.
Создание сиквенсов
Полный синтаксис для создания сиквенса
CREATE SEQUENCE [schema.]sequencename
[INCREMENT BY number]
[START WITH number]
[MAXVALUE number | NOMAXVALUE]
[MINVALUE number | NOMINVALUE]
[CACHE number | NOCACHE]
Создание сиквенса может быть очень простым. Например сиквенс использованный на рисунке 7-6 был создан с помощью команды
create sequence seq1;
Список доступных параметров
Директива CYCLE используется очень редко так как позволяет генерировать дубликаты. Если сиквенс используется для генерации значений первичного ключа, CYCLE имеет смысл только есть функция в БД которая удаляет старые записи быстрее чем сиквенс генерирует новые.
Кеширование значений критично для производительности. Выборка из сиквенса может осуществляться только одной сессией в момент времени. Механизм генерации значений очент эффективный: он гораздо быстрее чем блокировка строки, обновление строки или управление транзакцией. Но даже несмотря на это, выборка из сиквенса может быть точкой конкуренции сессий. Директива CACHE позволяет Oracle генерировать номера блоками. Пред-сгенерированные значения выбираются быстрее чем генерация по запросу.
The default number of values to cache is only 20. Experience shows that this is usually not enough. If your application selects from the sequence 10 times a second, then set the cache value to 50 thousand. Don’t be shy about this
Использование сиквенсов
Для использования сиквенса сессия может запросить следующее значения используя псевдо-столбец NEXTVAL, который заставляет сиквенс увеличить значение, или запросить последнее (текущее) значение для текущей сессии используя псевдостолбец CURRVAL. Значение NEXTVAL будет глобально уникальным: каждая сессия которая запрашивает это значение будет получать разный, увеличенный результат для каждого запроса. Значение CURRVAL будет постоянным для каждой сессии пока не будет новый запрос к NEXTVAL. Нет возможности узнать какое последнее значение было сегенрировано сиквенсом: вы можете выбрать только следующее значение вызвав NEXTVAL, и узнать последнее использованное значение для вашей сессии используя CURRVAL. Но вы не можете узнать последнее сгенерированное значение.
The CURRVAL of a sequence is the last value issued to the current session, not necessarily the last value issued. You cannot select the CURRVAL until after selecting the NEXTVAL.
Типичным примером использования сиквенса является генерация значений первичного ключа. Следующий пример использует сиквенс ORDER_SEQ для генерации уникальных значений номера заказа и сиквенс LINE_SEQ для генерации уникального значения строки заказа. Вначале создаётся сиквенс (один раз)
create sequence order_seq start with 10;
create sequence line_seq start with 10;
Затем вставка заказа и пунктов заказа в одной транзакции
insert into orders (order_id,order_date,customer_id)
insert into order_items (order_id,order_item_id,product_id)
insert into order_items (order_id,order_item_id,product_id)
Первая команда INSERT создает заказ с уникальным номером из сиквенса ORDER_SEQ для покупателя с номером 1000. Затем вторая и третья команды INSERT добавляют два элемента заказа используя текущее значение сиквенса ORDER_SEQ как значение для внешнего ключа соединяющего элементы заказа с заказом и следующее значение сиквенса LINE_SEQ для генерации уникального идентификатора каждого элемента. И наконец транзакция подтверждается.
Сиквенс не привязан к какой-то таблице. В предыдущем примере можно использовать один сиквенс для генерации значений для первичны ключей таблицы заказов и таблицы элементов заказа.
COMMIT не обязателен для подвтерждения увеличения счетчика: увеличение счётчика происходи сразу и навсегда и становится видимым для всех в момент увеличения. Нельзя отменить увеличение счётчика. Сиквенс обновляется вне зависимости от механизма управления транзакциями. Поэтому всегда будут пропавшие номера. Разрывы могут быть большими если БД перезапускается и CACHE директива использовалась для счётчика. Все номера которые были сгенерированы и не выбирались будут потеряны в момент выключения базы данных. После следующего запуска текущее значение будет последнее сгенерированное, а не последнее использованное. Таким образом для значения по умолчанию 20, каждый перезапуск приводит к потере 20 номеров.
Если бизнес-аналитики решили что не может быть разрыва в последовательности номеров, тогда можно генерировать уникальный номер по другому. Для предыдущего примера заказов текущий номер заказа можно хранить в таблице с начальным значением в 10
create table current_on(order_number number);
insert into current_on values(10);
Тогда код для создания заказа станет следующим
update current_on set order_number=order_number + 1;
insert into orders (order_number,order_date,customer_number)
Это будет работать с точки зрения генерации уникального номера заказа, и так как увеличение номера заказа происходит внутри транзакции то увеличение можно отменить в случае небходимости: тогда не будет разрывов в последовательности, до тех пор пока заказ не будет сознательно удалён. Но это гораздо менее эффективно чем использование сиквенсов, так как код будет слабо производителен в многопользовательской среде. Если много сессий попробуют заблокировать и увеличить значение в строке содержащей текущий номер заказа, то всё приложение будет подвисать посклько будет ждать своей очереди.
После создания сиквенса он может быть изменена. Синтаксис команды следующий
ALTER SEQUENCE sequencename
[INCREMENT BY number]
[START WITH number]
[MAXVALUE number | NOMAXVALUE]
[MINVALUE number | NOMINVALUE]
[CACHE number | NOCACHE]
Команда ALTER такая же как команда CREATE за одним исключением: нельзя установить начальное значение. Если вы хотите обновить начальное значение – то единственный способ это удалить сиквенс и создать новый. Для изменения значения CACHE для увеличения производительности можно выполнить следующую команду
В Oracle/PLSQL, вы можете создать автонумерацию с помощью последовательности. Последовательность является объектом Oracle, который используется для генерации последовательности чисел. Это может быть полезно, когда вам нужно создать уникальный номер в качестве первичного ключа.
CREATE SEQUENCE
Синтаксис
CREATE SEQUENCE sequence_nameMINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;
sequence_name имя последовательности, которую вы хотите создать.
Пример
Этот код создаст объект последовательность под названием supplier_seq. Первый номер последовательности 1, каждый последующий номер будет увеличиваться на 1 (т.е.. 2,3,4, . ). Это будет кэшировать до 20 значений для производительности.
Если вы опустите параметр MAXVALUE , ваша последовательность по умолчанию до:
Таким образом, вы можете упростить CREATE SEQUENCE. Написав следующее:
Теперь, когда вы создали объект последовательности для автонумерации поля счетчика, мы рассмотрим, как получить значение из этого объекта последовательности. Чтобы получить следующее значение, вам нужно использовать NEXTVAL .
Например:
Это позволит извлечь следующее значение из последовательности supplier_seq . Предложение NEXTVAL нужно использовать в SQL запросе. Например:
Этот isert запрос будет вставлять новую запись в таблицу suppliers (поставщики). Полю Supplier_id будет присвоен следующий номер из последовательности supplier_seq . Поле supplier_name будет иметь значение 'Kraft Foods'.
DROP SEQUENCE
После того как вы создали последовательность в Oracle, вам можете понадобиться удалить её из базы данных.
Синтаксис:
sequence_name имя последовательности, которую вы хотите удалить.
Пример
Рассмотрим на примере, как удалить последовательность в Oracle.
Этот пример удалит последовательность supplier_seq .
ЧАСТО ЗАДАВАЕМЫЕ ВОПРОСЫ
Вопрос: При создании последовательности, что означают опции cache и nocache ? Например, можно создать последовательность с опцией cache 20 следующим образом:
Или вы могли бы создать такую же последовательность, но с опцией nocache :
Ответ: Что касается последовательности, опция cache определяет, сколько значений последовательности будут сохранены в памяти для быстрого доступа.
Недостатком создания последовательности с cache, что если происходит отказ системы, все кэшированные значения последовательности, которые не были использованы, будут утеряны. Это приведет к разрывам в значениях, назначенной последовательности. Когда в система восстановится, Oracle будет кэшировать новые номера, с того места, где была прервана последовательность, игнорируя утерянные значения последовательности.
Примечание: Для восстановления утраченных значений последовательности, вы всегда можете выполнить команду ALTER SEQUENCE для сброса счетчика на правильное значение.
nocache означает, что ни одно из значений последовательности не хранятся в памяти. Эта опция может понизить производительность, однако, вы не должны столкнуться с разрывами в значениях, назначенной последовательности.
Вопрос: Как установить значение lastvalue в последовательность Oracle?
Ответ: Вы можете изменить lastvalue для последовательности Oracle, выполнив команду ALTER в последовательности.
Например, если последнее значение используемой последовательности Oracle был 100, и вы хотите, чтобы следующее значение было 225. Вы должны выполнить следующие команды.
В PostgreSQL я могу сделать что-то вроде этого:
Есть ли эквивалент Oracle?
ВНИМАНИЕ: весь приведенный ниже код действителен только для последовательностей, которые изначально были созданы с «приращением на 1». Если исходная последовательность была создана с приращением! = 1; после применения любой из описанных выше процедур приращение изменится на 1! Правильное значение приращения для использования можно получить из представления user_sequences.
Взгляните на "Сброс последовательности" здесь.
Отбросьте и воссоздайте последовательность
Ответы 18
Настоящий перезапуск невозможен Насколько мне известно. (Пожалуйста, поправьте меня, если я ошибаюсь!).
Однако, если вы хотите установить его на 0, вы можете просто удалить и воссоздать его.
Если вы хотите установить для него определенное значение, вы можете установить для INCREMENT отрицательное значение и получить следующее значение.
То есть, если ваша последовательность равна 500, вы можете установить ее на 100 с помощью
Просто примечание для людей в PLSQL. Не забудьте добавить «предел 1;» или "rownum = 1" к оператору select, в противном случае вы можете запустить nextVal пару раз с увеличением на -400 сколько угодно раз.
Последовательность ошибок .NEXTVAL меньше MINVALUE и не может быть создана, если INCREMENT BY - <<big_number>>
Изменение значения INCREMENT последовательности, его увеличение и последующее изменение довольно безболезненно, плюс у вас есть дополнительное преимущество, заключающееся в том, что вам не нужно повторно устанавливать все права доступа, как если бы вы отбросили / воссоздали последовательность.
Вот хорошая процедура сброса любой последовательности на 0 от гуру Oracle Том Кайт. Большое обсуждение плюсов и минусов также по ссылкам ниже.
@Dougman: привет, я новичок . почему в предыдущем ответе вы упоминаете в последнем предложении вместо того, чтобы немедленно выполнить 'select' || p_seq_name || '.nextval INTO l_val from dual';
@matra Я не вижу сценария, в котором нужно было бы сбросить последовательность и находиться в параллельной среде с другими пользователями той же последовательности.
Зачем нужно выбирать последовательность, почему бы просто не сделать последнюю строчку 'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
Этот хранимая процедура перезапускает мою последовательность:
+1 - Вы также можете параметризовать его, чтобы передать имя последовательности.
1) Предположим, вы создали ПОСЛЕДОВАТЕЛЬНОСТЬ, как показано ниже:
2) Теперь вы получаете значения из ПОСЛЕДОВАТЕЛЬНОСТИ. Допустим, я получал четыре раза, как показано ниже.
3) После выполнения вышеупомянутых четырех команд значение ПОСЛЕДОВАТЕЛЬНОСТИ будет 4. Теперь предположим, что я снова сбросил значение ПОСЛЕДОВАТЕЛЬНОСТИ на 1. Следуйте следующим шагам. Выполните все шаги в том же порядке, как показано ниже:
- ALTER SEQUENCE TESTSEQ INCREMENT BY -3;
- SELECT TESTSEQ.NEXTVAL FROM dual
- ALTER SEQUENCE TESTSEQ INCREMENT BY 1;
- SELECT TESTSEQ.NEXTVAL FROM dual
- отбросить последовательность
- воссоздать это
Просто имейте в виду, что отбрасывание сделает недействительными все объекты, которые зависят от этой последовательности, и их придется перекомпилировать.
Вам также придется повторно предоставить любые гранты, которые были предоставлены для выбора из последовательности.
Следующий скрипт устанавливает желаемое значение для последовательности:
Учитывая только что созданную последовательность с именем PCS_PROJ_KEY_SEQ и таблицу PCS_PROJ:
Вы забыли минус в своем первом операторе DDL (также есть дополнительное ключевое слово END ).
Мой подход - крохотное расширение Пример Дугмана.
Передайте начальное значение в качестве параметра. Почему? Мне нравится называть вещь, сбрасывающую последовательность обратно в максимальный идентификатор, используемый в какой-либо таблице. В итоге я вызываю этот процесс из другого скрипта, который выполняет несколько вызовов для целой группы последовательностей, сбрасывая nextval обратно на некоторый уровень, который достаточно высок, чтобы не вызывать нарушений первичного ключа, когда я использую значение последовательности для уникального идентификатора.
Он также учитывает предыдущий minvalue. Фактически, это может быть подтолкнуть следующее значение еще выше, если желаемое p_val или существующее минимальное значение выше, чем текущее или вычисленное следующее значение.
Эта процедура полезна сама по себе, но теперь давайте добавим еще одну, которая вызывает ее и задает все программно с соглашением об именах последовательностей и ищет максимальное значение, используемое в существующей таблице / поле .
Теперь готовим на газе!
Приведенная выше процедура проверяет максимальное значение поля в таблице, строит имя последовательности из пары таблица / поле и вызывает "Reset_Sequence" с этим максимальным значением.
Далее идет последний кусок этой головоломки и глазурь на торте .
В моей реальной базе данных есть около сотни других последовательностей, сбрасываемых с помощью этого механизма, поэтому в этой процедуре, описанной выше, есть еще 97 вызовов Reset_Sequence_to_Data.
Любить это? Ненавижу это? В разных?
Я люблю это. I would add a variable to get and save the increment by value from the user_sequences table. (It might not be 1). Note: might need to use the all_sequences table instead. In this case you might want to pass in the sequence_owner as well.Не могу проголосовать за вас достаточно. Это довольно распространенная проблема, когда вы имеете дело с миграцией данных, и это лучший подход AFAIK, если вы застряли с последовательностями.
Проголосовали за, потому что это отличный подход. Единственным недостатком является то, что это может привести к непредсказуемому поведению в системе RAC, где l_current может иметь одно из различных значений, в зависимости от того, на каком узле запускается сценарий; повторный запуск скрипта может привести к другим результатам. Я обнаружил, что если я запускал его несколько раз, он в конечном итоге останавливался на определенном значении.
Есть еще один способ сбросить последовательность в Oracle: установить свойства maxvalue и cycle . Когда nextval последовательности попадает в maxvalue , если свойство cycle установлено, тогда она снова начнется с minvalue последовательности.
Преимущество этого метода по сравнению с установкой отрицательного значения increment by заключается в том, что последовательность может продолжать использоваться во время выполнения процесса сброса, что снижает вероятность того, что вам понадобится какая-либо форма простоя для выполнения сброса.
Значение maxvalue должно быть больше текущего nextval , поэтому приведенная ниже процедура включает необязательный параметр, разрешающий буфер в случае повторного доступа к последовательности между выбором nextval в процедуре и установкой свойства cycle .
Процедура в существующем виде по-прежнему допускает возможность того, что другой сеанс получит значение 0, что может или не может быть проблемой для вас. Если это так, вы всегда можете:
- Установите minvalue 1 в первую очередь
- Исключить вторую выборку nextval
- Переместите оператор, чтобы установить свойство nocycle в другую процедуру, которая будет запущена позже (при условии, что вы хотите это сделать).
Эта функция была официально добавлена в 18c, но неофициально доступна с 12.1.
Возможно, безопасно использовать эту недокументированную функцию в 12.1. Несмотря на то, что синтаксис нет включен в официальная документация, он генерируется пакетом Oracle DBMS_METADATA_DIFF. Я использовал его несколько раз в производственных системах. Однако я создал запрос службы Oracle, и они подтвердили, что это не ошибка документации, функция действительно не поддерживается.
В 18c эта функция не отображается в синтаксисе языка SQL, но включена в Руководство администратора базы данных.
Привет, @Jon, я знаю о недокументированной функции, однако я не знал, что она видна в сгенерированном скрипте из DBMS_METADATA_DIFF. Не могли бы вы сообщить мне, как вы сгенерировали сценарий, какую процедуру и т. д.? Я бы тоже попробовал это проверить.
Для генерации уникальных значений ключа в Oracle используется объект - последовательность (sequence). Однако, на использование последовательности наложен ряд ограничений, например - запрет на использование в подзапросах. В данной заметке мы рассмотрим как обойти это ограничение и как произвести его оптимизацию.
Иногда, когда совсем этого не ждешь, появляется ошибка ORA-02287 - использование sequence там, где этого делать нельзя. Если посмотреть что скаано по этому повода на OraDoc, видим:
Restrictions on Sequence Values You cannot use CURRVAL and NEXTVAL in the following constructs:A subquery in a DELETE , SELECT , or UPDATE statement
A SELECT statement with a GROUP BY clause or ORDER BY clause
A SELECT statement that is combined with another SELECT statement with the UNION , INTERSECT , or MINUS set operator
The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statementПричем, самое неприятное из перечисленного - невозможность использования последовательности в подзапросах. Например:
INSERT ALL WHEN
MOD(id, 2 ) = 0 THEN INTO t1(id, VALUE)
VALUES
(id, VALUE)
WHEN MOD (id, 2 ) = 1 THEN INTO t2(id, VALUE)
VALUES
(id, VALUE)
SELECT sq$t_pk.NEXTVAL id, VALUE FROM TABLE (tbl_values);
где tbl_values - переменная типа коллекции, содержащей поле value. Т.е. в данном случае, пример иллюстрирует ситуацию, когда необходимо генерируемое уникальное значение ключа вставлять в одну таблицу и в другую. Условие разделение записи между двумя таблицами - в данном случае четное значение в одну таблицу, нечетное в другую. Решить данную проблему можно использовав функцию вместо последовательности, которая уже в свою очередь обращается к последовательности. Ora Doc это не запрещает. function get_sq_value return number
is
Result number ;
begin
select sq$t_pk.nextval into Result from dual;
return Result;
end ; WHEN MOD (id, 2 ) = 1 THEN INTO t2(id, VALUE)
VALUES
(id, VALUE)
SELECT get_sq_value id, VALUE FROM TABLE (tbl_values); Данный вариант хорош, когда записей в таблице источнике (будь то коллекция tbl_values или другая таблица) немного. Однако если их много, скажем больше 1000, то в данном случае внутренний запрос: будет вызывать переключение контекста, что приведет к повышенной нагрузке на сервер и дополнительными, совершенно не нужными затратами на выполнение. Можно размышлять следующим образом - что такое переключение контекста? Это когда PL/SQL движок останавливает свое выполнение и отдает команду SQL движку. Получается нужно избавиться от команды SELECT в самой функции. Совсем избавиться от нее, понятно, не получиться (надо же как то достать nextval), но можно свести к минимуму. Как? Выполним select. nextval один раз.
-- необходимые объекты
create table t1(id number primary key , value varchar2 ( 1000 ));
create table t2(id number primary key , value varchar2 ( 1000 ));
create sequence SQ$T_PK minvalue 1 maxvalue 999999999999999999999999999 start with 1 increment by 1 cache 100 order ;
-- спека пакета
create or replace package pkg_test_insert is
function get_sq_value return number ;
function get_sq_value(i_count in number ) return number ;
procedure insert_batch(tbl_values in tbl_t_type);
procedure insert_batch_cache_sq(tbl_values in tbl_t_type);
end pkg_test_insert;
/
-- тело пакета
create or replace package body pkg_test_insert is
-- тип для хранения идентификаторов.
type sq_values is table of number index by binary_integer ;
-- коллекция заданного типа.
g_sq_values sq_values;
g_pointer number := 1 ;
function get_sq_value(i_count in number ) return number
is
begin
if g_pointer = 1 then
select sq$t_pk. nextval
bulk collect into g_sq_values
from ( select rownum as l_date
from ( select 'x' as con from dual ) q
connect by q.con = q.con
) ql
where rownum <= i_count;
g_pointer := g_pointer + 1 ;
return g_sq_values(g_pointer - 1 )
else
g_pointer := g_pointer + 1 ;
return g_sq_values(g_pointer - 1 );
end if ;
-- получение идентификатора (контекст)
function get_sq_value return number
is
Result number ;
begin
select sq$t_pk. nextval into Result from dual ;
return Result ;
end ;
-- вставка записей (переключение контекста)
procedure insert_batch(tbl_values in tbl_t_type)
is
begin
INSERT ALL WHEN
MOD (id, 2 ) = 0 THEN INTO t1(id, VALUE )
VALUES
(id, VALUE )
WHEN MOD (id, 2 ) = 1 THEN INTO t2(id, VALUE )
VALUES
(id, VALUE )
SELECT get_sq_value id, VALUE FROM TABLE (tbl_values);
end ;
-- вставка записей (переключение контекста - один раз)
procedure insert_batch_cache_sq(tbl_values in tbl_t_type)
is
l_number number ;
begin
g_pointer := 1 ;
l_number := tbl_values. count ;
INSERT ALL WHEN
MOD (id, 2 ) = 0 THEN INTO t1(id, VALUE )
VALUES
(id, VALUE )
WHEN MOD (id, 2 ) = 1 THEN INTO t2(id, VALUE )
VALUES
(id, VALUE )
SELECT get_sq_value(l_number) id, VALUE FROM TABLE (tbl_values);
end ;
end pkg_test_insert;
/
- Вставка записей с использованием метода с большим переключением контекста ( insert_batch). На каждую запись будет вызывать внутрений select для получения nextval.
- Вставка записей с использованием метода с одним переключением контекста ( insert_batch_cache_sq).
pkg_test_insert.insert_batch(tbl_values => tbl_values);
pkg_test_insert.insert_batch_cache_sq(tbl_values => tbl_values);
runStats_pkg.rs_stop(p_difference_threshold => 0 ,p_output => 'WORKLOAD' );
commit ;
end ;
Для 1000 записей в tbl_values:
Run1 ran in 11 hsecs
Run2 ran in 4 hsecs
run 1 ran in 275% of the time
Name Run1 Run2 Diff
STAT. Elapsed Time 16 10 -6
STAT. CPU used by this session 18 9 -9
STAT. recursive cpu usage 18 9 -9
STAT. session logical reads 138 88 -50
STAT. redo size 16,172 9,652 -6,520
STAT. session pga memory 0 65,536 65,536
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
19,565 9,805 -9,760 199.54%
Для 50000 записей в tbl_values:
Run1 ran in 580 hsecsRun2 ran in 187 hsecs
run 1 ran in 310.16% of the time
Name Run1 Run2 Diff
STAT. CPU used by this session 584 194 -390
STAT. recursive cpu usage 584 194 -390
STAT. Elapsed Time 586 193 -393
STAT. session logical reads 5,063 2,550 -2,513
STAT. redo size 654,652 328,812 -325,840
STAT. session uga memory 0 3,597,440 3,597,440
STAT. session pga memory 0 3,670,016 3,670,016
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
1,266,940 482,741 -784,199 262.45% Из результатов видно, что второй вариант легче (используется меньше процессорных ресурсов, выполняется быстрее в 2-4 раза), да и защелок аж в 2-3 раза меньше первого варианта. Однако метрики использования памяти во втором варианте больше, причем они повышаются с увеличением числа входных строк. Что вполне объяснимо, с учетом того, что используем коллекцию, которая храниться в uga memory, которая в свою очередь (dedicated server) храниться в pga memory. Сделать так, чтобы рост числа памяти был закономерен и всегда контролировался - это уже дело техники. Можно например в функции генерации идентификаторов: function get_sq_value(i_count in number ) return number генерировать идентификаторы фиксированной пачкой, тогда рост использования памяти uga будет всегда фиксирован и равен числу идентификаторов, которые генерируются за один раз. В примере, в качестве источника строк используется коллекция (например, внешнее приложение передает в БД Oracle набор строк, через коллекцию). Когда же источником является другая таблица, то выгоднее использовать аналитику для подсчета количества обрабатываемых строк, чем выполнять count(1) по таблице - будет один проход по таблице. Создадим другую функцию, где с помощю аналитики выполним подсчет строк (при этом, будем так же выполнять select из коллекции - для нас не важно из чего на данном этапе выполнять выборку):
procedure insert_batch_cache_sq_over(tbl_values in tbl_t_type)
is
begin
INSERT ALL WHEN
MOD (id, 2 ) = 0 THEN INTO t1(id, VALUE )
VALUES
(id, VALUE )
WHEN MOD (id, 2 ) = 1 THEN INTO t2(id, VALUE )
VALUES
(id, VALUE )
SELECT get_sq_value(cnt) id, VALUE
FROM
( select count ( 1 ) over() cnt, value from TABLE (tbl_values) );
end ;
А теперь, произведем сравнение работы новой функции и функции insert_batch_cache_sq примерно таким образом:
\Untitled.html
declare
tbl_values tbl_t_type := tbl_t_type();
l_cnt_values number := 50000 ;
begin
for xxx in 1. .l_cnt_values loop
tbl_values(xxx) := t_type( to_char (xxx));
end loop ;
runStats_pkg.rs_start;
pkg_test_insert.insert_batch_cache_sq(tbl_values => tbl_values);
pkg_test_insert.insert_batch_cache_sq_over(tbl_values => tbl_values);
runStats_pkg.rs_stop(p_difference_threshold => 0 ,p_output => 'WORKLOAD' );
commit ;
end ;
Читайте также: