วิธีทำเลขรันอัตโนมัติที่ไม่ต้องเขียน trigger ด้วยการใช้ Identity Column

เวลาสอนเรื่องการออกแบบตาราง นักเรียนที่เคยใช้ MySQL มาก่อน มักจะถามว่า

"อาจารย์ครับ MySQL มี AUTO_INCREMENT แล้ว Oracle ทำยังไง"

เมื่อผมตอบว่า "ในออราเคิลไม่มี datatype auto increment ครับ ต้องใช้ database object ชื่อว่า sequence มาช่วยใส่เลขให้"

ตั้งแต่ Oracle 12c เป็นต้นมา งานง่ายขึ้นเยอะครับ

สถานการณ์สมมติ

เราออกแบบตารางเก็บข้อมูลพนักงาน ทุกแถวต้องมีตัวระบุที่ไม่ซ้ำกับใคร เอาไว้ให้ตารางอื่นอ้างอิงถึง เราเรียกคอลัมน์นั้นว่า primary key

ทีนี้เลขจะมาจากไหน ถ้าให้คนกรอกเอง เดี๋ยวก็ซ้ำ เดี๋ยวก็ลืม สิ่งที่เราอยากได้คือให้ฐานข้อมูลแจกเลขให้เอง แถวแรกได้ 1 แถวต่อไปได้ 2 ไล่ไปเรื่อย ๆ

วิธีเดิม ต้องประกอบเอง 2 ชิ้น

ก่อน 12c เราต้องสร้าง sequence ก่อน มันคือเครื่องแจกเลขของ Oracle เรียกทีก็คายเลขถัดไปให้ที ทำงานแยกจากตาราง ไม่รู้จักตารางเราด้วยซ้ำ

พอมี sequence แล้วก็ต้องหาทางเอาเลขนั้นยัดเข้าตารางตอน insert ตรงนี้ต้องพึ่ง trigger คือโค้ดที่ตั้งไว้ให้ทำงานเองเมื่อเกิดเหตุการณ์บางอย่าง ในที่นี้คือทุกครั้งที่มีแถวใหม่เข้ามา

CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER emp_bir
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  :NEW.emp_id := emp_seq.NEXTVAL;
END;
/

ใช้ได้ครับ ไม่ผิดอะไร แต่ลองคิดตามว่าถ้าระบบมี 50 ตาราง เราจะมี sequence 50 ตัวกับ trigger อีก 50 ตัวกระจายอยู่ทั่วฐานข้อมูล วันไหนย้ายระบบหรือต้องไล่หาว่าเลขมาจากไหน ก็ต้องตามเก็บให้ครบ

วิธีใหม่ ประกาศตอนสร้างตารางเลย

ตั้งแต่ 12c เราสามารถเขียนตอน CREATE TABLE ได้เลยว่าคอลัมน์นี้ให้ระบบแจกเลขให้

CREATE TABLE employees (
  emp_id   NUMBER GENERATED ALWAYS AS IDENTITY,
  emp_name VARCHAR2(100)
);

INSERT INTO employees (emp_name) VALUES ('สมชาย');
INSERT INTO employees (emp_name) VALUES ('สมหญิง');

SELECT * FROM employees;

--   EMP_ID  EMP_NAME
--        1  สมชาย
--        2  สมหญิง

สังเกตว่าตอน insert เราไม่ได้ใส่ emp_id เลย แต่มันมีเลขให้เรียบร้อย

เบื้องหลัง Oracle ยังใช้ sequence เหมือนเดิมนะครับ เพียงแต่มันสร้างให้เราอัตโนมัติ (ชื่อขึ้นต้นด้วย ISEQ$$_) แล้วผูกติดกับคอลัมน์นั้นให้เสร็จสรรพ ข้อดีคือพอเราลบตารางทิ้ง sequence ตัวนั้นก็หายตามไปด้วย ไม่มีของเก่าค้างเป็นขยะในระบบ

เลือกโหมดให้ตรงกับงาน

Identity column มีสามแบบ ต่างกันตรงยอมให้เราใส่เลขเองได้แค่ไหน

GENERATED ALWAYS ระบบแจกเลขให้เสมอ ห้ามใส่เอง ถ้าฝืนจะโดนปฏิเสธทันที เหมาะกับ primary key ในระบบใหม่ที่ไม่อยากให้ใครมายุ่ง

GENERATED BY DEFAULT ไม่ใส่ระบบแจกให้ ใส่มาเองระบบก็ใช้ค่าที่เราให้ เหมาะกับตอนย้ายข้อมูลเก่าเข้ามา เพราะข้อมูลชุดเก่ามีเลขของมันติดมาอยู่แล้ว

GENERATED BY DEFAULT ON NULL เหมือนแบบที่สอง แต่ใจดีกว่าอีกนิด ถ้าโปรแกรมส่ง NULL เข้ามา ระบบจะแจกเลขให้แทนที่จะฟ้อง error เหมาะกับแอปเก่าที่แก้โค้ดไม่ได้แล้ว

ปรับรายละเอียดเพิ่มได้เหมือน sequence ปกติ เช่นอยากให้เริ่มที่ 1000 แล้วเพิ่มทีละ 10

emp_id NUMBER GENERATED BY DEFAULT AS IDENTITY
       (START WITH 1000 INCREMENT BY 10 CACHE 50)

ลองแหย่ดูว่า ALWAYS เข้มจริงไหม

นักศึกษาชอบถามว่าห้ามใส่เองนี่ห้ามจริงหรือห้ามหลอก ลองรันดูครับ

INSERT INTO employees (emp_id, emp_name) VALUES (99, 'สมปอง');

-- ORA-32795: cannot insert into a generated always identity column

ปฏิเสธทันที ไม่มีต่อรอง

ที่ผมชอบโหมดนี้เพราะมันกันพลาดตั้งแต่ระดับฐานข้อมูล ไม่ต้องหวังว่าโปรแกรมเมอร์ทุกคนจะจำกฎได้ครบ ระบบที่พึ่งวินัยของคนอย่างเดียว วันหนึ่งมันจะพังจนได้

อยากรู้ว่าตารางไหนใช้อยู่บ้าง

พอระบบโตขึ้นเราจะเริ่มจำไม่ไหว ไม่ต้องเปิดดูทีละตารางครับ Oracle มีตารางระบบเก็บข้อมูลพวกนี้ไว้ให้อยู่แล้ว

SELECT table_name, column_name, generation_type, sequence_name
FROM   user_tab_identity_cols;

-- TABLE_NAME  COLUMN_NAME  GENERATION_TYPE  SEQUENCE_NAME
-- EMPLOYEES   EMP_ID       ALWAYS           ISEQ$$_73542

generation_type บอกว่าใช้โหมดไหน ส่วน sequence_name คือชื่อ sequence ที่ระบบสร้างให้ รู้ไว้มีประโยชน์เวลาต้องตามดูว่าเลขวิ่งไปถึงไหนแล้ว

ถ้าอยากให้เลขเริ่มนับต่อจากของเดิมหลังย้ายข้อมูล สั่งผ่าน ALTER TABLE ได้เลย ไม่ต้องไปยุ่งกับ sequence ตรง ๆ

ALTER TABLE employees
MODIFY emp_id GENERATED BY DEFAULT AS IDENTITY (START WITH 5001);

สามเรื่องที่มักทำให้เจ็บตัว

เรื่องแรก เลขมันกระโดดได้ และเป็นเรื่องปกติ ถ้า insert แล้ว rollback เลขที่หยิบไปจะไม่ถูกคืนกลับมา หรือถ้า database ปิดแล้วเปิดใหม่ เลขที่ค้างใน cache ก็หายไปเลย

ข้อนี้สำคัญมากครับ อย่าเอา identity column ไปทำเลขที่ใบกำกับภาษีหรือเลขเอกสารที่กฎหมายบังคับให้เรียงต่อเนื่อง วันที่มันกระโดดเราจะอธิบายสรรพากรไม่ถูก งานแบบนั้นต้องออกแบบแยกต่างหาก

เรื่องที่สอง คอลัมน์ที่มีอยู่แล้วจะเปลี่ยนให้เป็น identity ตรง ๆ ไม่ได้ ต้องใช้วิธีอ้อม สร้างคอลัมน์ใหม่ ย้ายข้อมูล แล้วค่อยสลับ วางแผนก่อนลงมือนะครับ

เรื่องที่สาม ตอนย้ายข้อมูลด้วย Data Pump เข้าตารางโหมด BY DEFAULT ให้เช็คว่า sequence เบื้องหลังนับถึงไหนแล้ว ถ้าข้อมูลเก่ามีเลขสูงกว่าที่ sequence นับอยู่ พอ insert แถวใหม่มันจะไปชนของเดิมแล้วขึ้น ORA-00001 คือค่าซ้ำในคอลัมน์ที่ห้ามซ้ำ แก้ด้วย ALTER TABLE ให้เริ่มนับใหม่จากเลขที่พ้นของเดิม

แล้วของเก่าที่ใช้ trigger อยู่ ต้องรื้อไหม

ถ้ามันรันอยู่บนระบบจริงและทำงานได้ดี ไม่ต้องรีบครับ การไล่แก้ทีเดียว 50 ตารางเสี่ยงกว่าประโยชน์ที่ได้ แนวทางที่ผมแนะนำคือตารางใหม่ใช้ identity ตั้งแต่วันแรก ส่วนตารางเก่าค่อยแปลงตอนที่ต้องแตะมันอยู่แล้ว เช่นตอน refactor ใหญ่หรือย้ายเวอร์ชัน จะได้ทดสอบไปพร้อมกันรอบเดียว

อีกคำถามที่เจอบ่อยคือที่บอกว่า insert เร็วขึ้นนี่จริงไหม จริงครับ trigger แบบทำงานทีละแถวทำให้ Oracle ต้องสลับไปมาระหว่างส่วนประมวลผล SQL กับ PL/SQL ทุกแถว งานเล็กไม่รู้สึกหรอก แต่ถ้าเป็นงาน batch ที่ยัดข้อมูลทีละหลายแสนแถว ตรงนี้เห็นชัด identity column ตัดขั้นตอนนี้ทิ้งเพราะทำงานในระดับ engine โดยตรง

สรุป

ถ้าใช้ Oracle 12c ขึ้นไปแล้วยังต้องมานั่งเขียน sequence คู่กับ trigger เพื่อทำเลขรัน แปลว่าเรากำลังทำงานหนักเกินจำเป็นครับ ประกาศ GENERATED AS IDENTITY ตอนสร้างตารางจบในบรรทัดเดียว เลือกโหมดให้ตรงกับงาน แล้วจำไว้อย่างเดียวว่าเลขมันกระโดดได้ อย่าเอาไปใช้กับเลขเอกสารที่ต้องเรียงเป๊ะ

ผมเขียนเรื่อง Oracle และ SQL แบบนี้ทุกสัปดาห์ ทั้งทิปสั้นและเคสที่เจอกันบ่อยในวงการ ติดตามได้ที่เพจ Facebook อาจารย์ตี๋ที่สอน Oracle ครับ