در هر سیستم دیتابیسی، ساخت شناسه‌های یکتا یکی از پایه‌های معماری داده است.
در Oracle، ابزار این کار معمولاً Sequence است؛ اما زمانی که از دستورهای جمعی مثل Bulk Insert یا Parallel Insert استفاده می‌کنیم، گاهی مشاهده میشود که Sequence مقادیر تکراری یا نامرتب تولید میکند.
این اتفاق به ظاهر عجیب، علت‌های فنی عمیقی دارد که در این مقاله آموزش اوراکل در بخش آموزش Oracle SQL به زبان ساده اما تخصصی بررسی می‌کنیم.
اگر با Oracle 23ai کار کرده باشی، احتمالاً با این سناریو مواجه شدی: دیتابیس اصلی (CDB) به‌درستی باز میشود، اما یکی از PDBها در مرحله‌ی Mount گیر میکند!
پیشنهاد می کنم این مقاله زیر رو حتما مطالعه کنی.
در این مقاله شما می خوانید
🧩 ساختار داخلی Sequence در Oracle
🔹 چگونه NEXTVAL کار میکند؟
اوراکل برای افزایش سرعت، مقدارهای Sequence را در حافظه Cache نگه می‌دارد.
هنگام درخواست مقدار جدید با NEXTVAL، سیستم عدد بعدی را از Cache برمی‌دارد:
CREATE SEQUENCE seq_order_id
START WITH 1
INCREMENT BY 1
CACHE 20;
هر بار که کاربر NEXTVAL را می‌خواند، یکی از آن ۲۰ مقدار رزرو شده برمی‌گردد.
و چون Sequence مستقل از تراکنش است، حتی Rollback هم نمی‌تواند آن عدد را پس بگیرد.
🚧 علت‌های تکرار Sequence در Bulk Insert
🔹 ۱. اجرای موازی (Parallel Processing)
در عملیات‌های موازی مثل:
INSERT /*+ APPEND PARALLEL(4) */ INTO orders
SELECT seq_order_id.NEXTVAL, customer_id, amount
FROM temp_orders;
هر پردازش Slave مقدار خودش را از Sequence Cache می‌گیرد.
اگر فرآیند به هر دلیل متوقف یا دوباره آغاز شود، بعضی Cacheها دوباره ثبت میشوند و باعث تکرار یا جاافتادگی در مقادیر Sequence خواهند شد.
🔹 ۲. ریست Cache پس از Crash یا Rollback
در حالت Cache فعال، اگر دیتابیس یا Instance توسط Bulk operation دچار کرش شود، مقادیر رزروشده‌ای که هنوز در جدول درج نشده‌اند، ممکن است در بارگذاری بعدی دوباره تخصیص یابند.
راه‌حل:
ALTER SEQUENCE seq_order_id NOCACHE;
NOCACHE باعث کاهش سرعت کمی میشود ولی جلوی تکرار عدد پس از کرش را می‌گیرد.
🔹 ۳. Trigger و FORALL به صورت ترکیبی
وقتی Sequence در Trigger یا در حلقه FORALL فراخوانی شود، در برخی شرایط ممکن است در Compile-time، رفتار شبه موازی ایجاد شود.
CREATE OR REPLACE TRIGGER trg_orders_pk
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
:NEW.id := seq_order_id.NEXTVAL;
END;
و اگر همزمان عملیات درج انبوه بدون Trigger هم انجام شود، احتمال تکرار مقداری وجود دارد.
🧪 مثال عملی FORALL و جلوگیری از تکرار
روش صحیح:
DECLARE
TYPE t_order IS TABLE OF orders%ROWTYPE;
l_orders t_order := t_order();
BEGIN
FOR i IN 1..10 LOOP
l_orders.EXTEND;
l_orders(i).id := seq_order_id.NEXTVAL;
l_orders(i).customer_id := i;
l_orders(i).amount := i*1000;
END LOOP;
FORALL i IN INDICES OF l_orders
INSERT INTO orders VALUES l_orders(i);
END;
در این حالت، NEXTVAL یک بار در حلقه PL/SQL اجرا میشود و در FORALL فقط داده تزریق میشود، بنابراین مقدار Sequence تکراری نخواهد بود.
🧮 رفتار Sequence در Direct Path Load و sqlldr
در بارگذاری‌های مستقیم با INSERT /*+ APPEND */ یا ابزار SQL*Loader، هر پردازش موازی بخش خاصی از داده را بارگذاری میکند.
Oracle پیشنهاد میکند برای جلوگیری از تکرار:
ALTER SEQUENCE seq_order_id NOCACHE ORDER;
گزینهORDER باعث تضمین ترتیب و یکتا بودن در حالت موازی میشود.
🔧 راهکارهای حرفه‌ای DBA برای جلوگیری از تکرار
| سناریو | رفتار محتمل Sequence | راه‌حل پیشنهادی |
|---|---|---|
| Parallel Load | تکرار یا فاصله در عددها | استفاده از ORDER در Sequence |
| Crash یا Restart | اختصاص مجدد مقادیر Cache پس از راه‌اندازی مجدد | تنظیم Sequence روی NOCACHE |
| Trigger + Bulk ترکیبی | فراخوانی همزمان NEXTVAL در دو مسیر مختلف | تخصیص دستی NEXTVAL در PL/SQL (قبل از FORALL) |
| RAC یا Multi-Instance | کش مستقل در هر Instance و احتمال تکرار بین نودها | استفاده از ORDER یا ستون Identity Column در نسخه‌های ۱۲c+ |
🆕 قابلیتی جدید در اوراکل ۲۳: Identity Column در Oracle
از نسخه ۱۲c به بعد می‌توان بدون Sequence از ستون هویتی استفاده کرد:
CREATE TABLE orders (
id NUMBER GENERATED ALWAYS AS IDENTITY,
customer_id NUMBER,
amount NUMBER
);
این روش کاملاً Thread-safe و Transactional است و حتی در Parallel load ها هیچ‌گونه مقدار تکراری تولید نمی‌کند.
💡 نکته DBA-Level
در محیط‌هایی که سرعت بالاتر در Bulk لازم است ولی نظم هم اهمیت دارد، بهترین گزینه:
ALTER SEQUENCE seq_order_id CACHE 1000 ORDER;
✅ ترکیب سرعت و یکتایی
اما در محیط RAC (چند نود مستقل)، باید احتیاط کرد چون ORDER باعث serialization کوچک بین Instance ها میشود.
📊 بررسی صحت یکتا بودن Sequence در داده‌های درج شده
برای اطمینان از عدم تکرار:
SELECT id, COUNT(*)
FROM orders
GROUP BY id
HAVING COUNT(*) > ۱;
اگر هیچ ردیفی برنگردد، یعنی Sequence کاملاً یکتا عمل کرده است.
سوالات متداول درباره تکرار Sequence در Bulk در اوراکل
در Bulk یا Parallel Insert، چندین پروسه همزمان (Slave Process) از Sequence استفاده میکنند.
هر Session بخشی از کش Sequence را رزرو میسازد، و اگر Job قطع یا ریست شود، ممکن است همان مقادیر دوباره تخصیص یابد.
✅ برای جلوگیری: از گزینهی
ALTER SEQUENCE seq_name NOCACHE ORDER;
استفاده کن تا توالی کنترلشده و بدون تکرار شود.
بله، Sequence در Oracle طراحیشده برای یکتا بودن جهانی است، اما نه تضمین «پیوستگی عددی».
یعنی ممکن است عددها جا بیفتند ولی تکرار منطقی رخ نمیدهد، مگر در اجراهای parallel یا crashهای cache.
برای محیطهای حساس، از ORDER یا ستونهای Identity (از ۱۲c به بعد) استفاده شود.
در حلقه FORALL نباید مستقیماً NEXTVAL را در insert بنویسی، چون ممکن است plan cache تولید تکراری بسازد.
❗ راه درست:
مقدار NEXTVAL را درون حلقه PL/SQL محاسبه کن و در Collection ذخیره کن، سپس با FORALL درج کن.
مثال سریع:
l_tab(i).id := seq_order_id.NEXTVAL;
- CACHE: سرعت بالاتر دارد چون مقادیر در حافظه ذخیره میشوند؛ مناسب برای OLTP.
- NOCACHE: سرعت کمتر ولی ایمنتر در برابر تکرار یا از دست رفتن عدد پس از Crash.
در Bulk یا Parallel load‑های حساس پیشنهاد میشود از
ALTER SEQUENCE seq_name NOCACHE ORDER;
استفاده شود.
جمع‌بندی
🔹 تکرار Sequence در Bulk معمولاً ناشی از Cache، موازی‌سازی یا ناسازگاری در فراخوانی NEXTVAL است.
🔹 برای حل باثبات در پروژه‌های واقعی:
- در Bulk و Parallel: استفاده از
ORDERیاNOCACHE - در سناریوهای ترکیبی PL/SQL و Trigger: تخصیص دستی NEXTVAL
- برای نسل جدید اوراکل (۱۲c به بعد): استفاده از ستونهای Identity
📥 اگر سوالی داری در مورد تکرار Sequence در Bulk در اوراکل داری، در بخش کامنت‌ها بپرس.
دیدگاهتان را بنویسید