คู่มือการย้าย Oracle ไปยัง SQL Server และ SQL Server ไปยัง Oracle - Pt. 2

ส่วนแรกของชุดนี้กล่าวถึงความแตกต่างระหว่าง Oracle Database และ Microsoft SQL Server ในการใช้งานธุรกรรม โดยเน้นที่ข้อผิดพลาดที่อาจพบในระหว่างการโยกย้าย Oracle ไปยัง SQL Server และในทางกลับกัน งวดถัดไปนี้จะครอบคลุมองค์ประกอบไวยากรณ์ SQL ที่ใช้กันทั่วไปจำนวนหนึ่งซึ่งไม่ตรงกันหรือมีความหมายหรือการใช้งานที่แตกต่างกันโดยสิ้นเชิงในการแบ่ง Oracle–SQL Server

ลำดับใน Oracle และคอลัมน์ข้อมูลประจำตัวใน SQL Server

มีความแตกแยกมายาวนานในชุมชนฐานข้อมูลระหว่างสองค่าย: ผู้รักชาติของกุญแจธรรมชาติและผู้เสนอกุญแจเทียม (หรือ "ตัวแทน")

ตัวฉันเองปกป้องกุญแจธรรมชาติ แต่มักจะพบว่าตัวเองสร้างตัวแทนเสมือนด้วยเหตุผลใดก็ตาม แต่ทิ้งเนื้อหาของการอภิปรายนี้ไว้ มาดูกลไกมาตรฐานสำหรับการสร้างคีย์ประดิษฐ์: ลำดับของ Oracle และคอลัมน์ข้อมูลประจำตัวของ SQL Server

ลำดับของ Oracle เป็นอ็อบเจ็กต์ระดับฐานข้อมูลชั้นหนึ่ง ในทางตรงกันข้าม คอลัมน์ข้อมูลประจำตัวของ SQL Server เป็นประเภทคอลัมน์ ไม่ใช่วัตถุ

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

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

ในทางกลับกัน เมื่อหลายกระบวนการจำเป็นต้องเข้าถึง NEXTVAL (ค่าที่เพิ่มขึ้นถัดไป) จากลำดับ ลำดับจะกลายเป็นทรัพยากรที่เข้าถึงได้ครั้งเดียวที่สำคัญ มันจะทำให้กระบวนการทั้งหมดเข้าถึงได้ตามลำดับอย่างมีประสิทธิภาพ เปลี่ยนการใช้งานแบบมัลติเธรด (เดี่ยวหรือหลายเซิร์ฟเวอร์) ให้เป็นกระบวนการแบบเธรดเดียว โดยมีเวลารอนานและหน่วยความจำสูง/การใช้ CPU ต่ำ

การดำเนินการดังกล่าวเกิดขึ้นจริง วิธีแก้ปัญหานี้คือการกำหนดอ็อบเจ็กต์ลำดับที่เป็นปัญหาด้วยค่าแคชที่เหมาะสม หมายความว่าช่วงของค่าที่กำหนดไว้ (ไม่ว่าจะเป็น 100 หรือ 100 พัน) จะถูกเลือกลงในแคชสำหรับกระบวนการเรียก ซึ่งบันทึกไว้ในพจนานุกรมข้อมูลตามที่ใช้ และพร้อมใช้งานสำหรับกระบวนการนี้โดยเฉพาะโดยไม่จำเป็นต้องเข้าถึงพจนานุกรมข้อมูลทุกครั้งที่มีการเรียก NEXTVAL

แต่นี่คือสาเหตุที่ทำให้เกิดช่องว่างขึ้น เนื่องจากค่าที่แคชไว้ทั้งหมดไม่น่าจะถูกใช้งาน นอกจากนี้ยังหมายความว่าระหว่างหลายกระบวนการในเซสชันคู่ขนาน ค่าลำดับที่บันทึกไว้บางค่าสามารถกลับด้านตามลำดับเวลาได้ การผกผันนี้ไม่สามารถเกิดขึ้นได้ภายในกระบวนการเดียว เว้นแต่จะมีการรีเซ็ตค่าของลำดับหรือย้อนกลับ แต่สถานการณ์สุดท้ายนี้ทำให้ต้องมองหาปัญหา: ไม่จำเป็น และหากนำไปใช้อย่างไม่ถูกต้อง อาจส่งผลให้เกิดการสร้างค่าที่ซ้ำกัน

ดังนั้น วิธีเดียวที่ถูกต้องในการใช้ลำดับของ Oracle คือการสร้างคีย์ตัวแทน: คีย์ที่ไม่ซ้ำกันแต่ไม่ถือว่าเก็บข้อมูลอื่นๆ ที่ใช้งานได้อย่างน่าเชื่อถือ

คอลัมน์ข้อมูลประจำตัวใน SQL Server

แล้ว SQL Server ล่ะ? ในขณะที่มีการแนะนำลำดับที่มีฟังก์ชันการทำงานและการใช้งานที่คล้ายกันมากกับคู่ของ Oracle ใน SQL Server 2012 แต่ก็ไม่ใช่เทคนิคชั้นหนึ่ง เช่นเดียวกับการเพิ่มคุณสมบัติอื่น ๆ พวกเขาเหมาะสมสำหรับการแปลงจาก Oracle แต่เมื่อใช้งานคีย์ตัวแทนตั้งแต่เริ่มต้นบน SQL Server IDENTITY เป็นตัวเลือกที่ดีกว่ามาก

IDENTITY เป็นวัตถุ "ลูก" ของตาราง ไม่เข้าถึงทรัพยากรภายนอกตารางและรับประกันว่าจะเป็นไปตามลำดับ เว้นแต่จะมีการจัดการโดยเจตนา และได้รับการออกแบบมาโดยเฉพาะสำหรับงานนี้ มากกว่าความเข้ากันได้ทางความหมายกับ Oracle

เนื่องจาก Oracle ได้นำฟังก์ชัน IDENTITY ไปใช้ในเวอร์ชัน 12.1 จึงเป็นเรื่องปกติที่จะสงสัยว่าเมื่อไม่มีมันมาก่อน เหตุใดจึงนำมาใช้ในตอนนี้ และเหตุใด SQL Server จึงต้องการตั้งแต่เริ่มต้น (จากต้นกำเนิดของ Sybase SQL Server)

เหตุผลก็คือ Oracle มีคุณลักษณะคีย์ข้อมูลประจำตัวเสมอ: ROWID pseudocolumn ซึ่งมีประเภทข้อมูลเป็น ROWID หรือ UROWID ค่านี้ไม่ใช่ตัวเลข ( ROWID และ UROWID เป็นประเภทข้อมูลของ Oracle ที่เป็นกรรมสิทธิ์) และระบุเร็กคอร์ดข้อมูลโดยไม่ซ้ำกัน

ต่างจาก IDENTITY ของ SQL Server ตรงที่ ROWID ของ Oracle ไม่สามารถจัดการได้อย่างง่ายดาย (สามารถสอบถามได้ แต่ไม่สามารถแทรกหรือแก้ไขได้) และถูกสร้างขึ้นในพื้นหลังสำหรับทุกแถวในทุกตาราง Oracle นอกจากนี้ วิธีที่มีประสิทธิภาพที่สุดในการเข้าถึงแถวข้อมูลใดๆ ในฐานข้อมูล Oracle ก็คือ ROWID ของมัน ดังนั้นจึงใช้เป็นเทคนิคการเพิ่มประสิทธิภาพการทำงาน สุดท้ายนี้ จะกำหนดลำดับการจัดเรียงเอาต์พุตของคิวรีเริ่มต้น เนื่องจากจะสร้างดัชนีการจัดเก็บข้อมูลแถวระดับต่ำได้อย่างมีประสิทธิภาพ

หาก ROWID ของ Oracle มีความสำคัญมาก SQL Server จะอยู่รอดได้อย่างไรในช่วงหลายปีที่ผ่านมาและปล่อยให้เป็นอิสระ โดยใช้คอลัมน์ IDENTITY เป็นคีย์หลัก (ตัวแทน)

สิ่งสำคัญคือต้องสังเกตความแตกต่างในการใช้งานโครงสร้างดัชนีระหว่าง Oracle และ SQL Server

ใน SQL Server ดัชนีแรก—คีย์หลัก บ่อยกว่าไม่—ถูกจัดกลุ่ม ซึ่งหมายความว่าโดยปกติ ข้อมูลในไฟล์ข้อมูลหลักจะถูกเรียงลำดับโดยคีย์นี้ ด้าน Oracle เทียบเท่ากับดัชนีคลัสเตอร์คือตารางที่จัดดัชนี นี่เป็นโครงสร้างทางเลือกใน Oracle ที่ใช้เป็นระยะๆ ตามความจำเป็นเท่านั้น เช่น สำหรับตารางค้นหาแบบอ่านอย่างเดียว

รูปแบบการออกแบบทั้งหมดใน Oracle ที่อิงตามการใช้ ROWID (เช่น การขจัดข้อมูลซ้ำซ้อน) ควรใช้ตามคอลัมน์ IDENTITY เมื่อย้ายไปยัง SQL Server

ขณะย้ายจากการใช้ IDENTITY บน SQL Server ไปเป็นการใช้ IDENTITY บน Oracle สามารถสร้างโค้ดที่ใช้งานได้จริง แต่ก็ไม่เหมาะ เนื่องจากในฝั่ง Oracle ROWID จะทำงานได้อย่างมีประสิทธิภาพมากขึ้น

เช่นเดียวกับเมื่อทำการแปลงไวยากรณ์ SQL อย่างง่ายเพื่อย้ายลำดับของ Oracle ไปยัง SQL Server: โค้ดจะทำงาน แต่การใช้ IDENTITY เป็นตัวเลือกที่นิยมมากในแง่ของความเรียบง่ายและประสิทธิภาพของโค้ด

ดัชนีที่กรองใน Microsoft SQL Server

เมื่อหลายปีก่อน Microsoft SQL Server 2008 ได้นำเสนอคุณลักษณะสำคัญจำนวนหนึ่งที่เปลี่ยนให้เป็นฐานข้อมูลระดับองค์กรชั้นหนึ่งอย่างแท้จริง รายการที่บันทึกวันของฉันมากกว่าหนึ่งครั้งได้รับการกรองดัชนี

ดัชนีที่กรองแล้วคือดัชนีที่ไม่ทำคลัสเตอร์ (เช่น ดัชนีที่มีอยู่เป็นไฟล์ข้อมูลของตัวเอง) ที่มีส่วน WHERE หมายความว่าไฟล์ดัชนีมีเฉพาะบันทึกข้อมูลที่เกี่ยวข้องกับอนุประโยค เพื่อใช้ประโยชน์จากดัชนีที่ถูกกรองอย่างเต็มที่ ดัชนีควรมีส่วนคำสั่ง INCLUDE ที่แสดงรายการคอลัมน์ทั้งหมดที่จำเป็นเมื่อส่งคืนชุดข้อมูล เมื่อการสืบค้นของคุณได้รับการปรับให้เหมาะสมเพื่อใช้ดัชนีที่กรองเฉพาะซึ่งรวมถึงจุดข้อมูลที่จำเป็นทั้งหมด กลไกจัดการฐานข้อมูลจำเป็นต้องเข้าถึงไฟล์ดัชนี (ขนาดเล็ก) เท่านั้นโดยไม่ได้ดูไฟล์ข้อมูลตารางหลัก

สิ่งนี้มีค่ามากสำหรับฉันเมื่อสองสามปีก่อนเมื่อทำงานกับตารางขนาดเทราไบต์ ลูกค้าที่เป็นปัญหามักจำเป็นต้องเข้าถึงบันทึกเพียงเศษเสี้ยวของเปอร์เซ็นต์ที่ทำงานอยู่ในช่วงเวลาหนึ่งๆ การใช้งานเบื้องต้นของการเข้าถึงนี้ (เกิดจากการกระทำของ UI ของผู้ใช้ปลายทาง) ไม่เพียงแต่จะช้าอย่างเจ็บปวดเท่านั้น แต่ยังใช้งานไม่ได้ เมื่อฉันเพิ่มดัชนีที่กรองแล้วด้วย INCLUDE ที่จำเป็น มันกลายเป็นการค้นหาระดับเสี้ยววินาที เวลาที่ฉันใช้ไปกับงานการปรับให้เหมาะสมนี้ใช้เวลาเพียงหนึ่งชั่วโมงเท่านั้น

แน่นอนว่าดัชนีที่กรองแล้วมีข้อจำกัดบางประการ ไม่สามารถรวมคอลัมน์ LOB ได้ มีข้อจำกัดเกี่ยวกับเงื่อนไขส่วน WHERE ที่ดัชนีสามารถรวมได้ และเพิ่มไปยังพื้นที่จัดเก็บของฐานข้อมูล แต่หากมีกรณีการใช้งานที่เหมาะสมกับพารามิเตอร์เหล่านี้ การแลกเปลี่ยนด้านการจัดเก็บมักจะค่อนข้างน้อยเมื่อเทียบกับดัชนีการเพิ่มประสิทธิภาพที่สำคัญที่กรองได้

สิ่งที่เกี่ยวกับดัชนีกรองในฐานข้อมูล Oracle?

ต่อมาฉันพบว่าตัวเองอยู่ในทีมขนาดใหญ่ของบริษัทที่ติดอันดับ Fortune 500 ในฐานะนักพัฒนา/DBA ในโครงการย้ายข้อมูลของ SQL Server–to-Oracle โค้ดรอบๆ ฐานข้อมูลต้นทาง —SQL Server 2008— ถูกนำไปใช้งานได้ไม่ดี โดยมีประสิทธิภาพน้อยซึ่งทำให้การแปลงมีความจำเป็น: งานการซิงโครไนซ์แบ็คเอนด์รายวันทำงานนานกว่า 23 ชั่วโมง มันไม่มีดัชนีที่ถูกกรอง แต่ในระบบใหม่—Oracle 11g— ฉันเห็นหลายกรณีที่ดัชนีที่ถูกกรองจะมีประโยชน์มาก แต่ Oracle 11g ไม่มีดัชนีกรอง!

และดัชนีที่กรองจะไม่ถูกนำมาใช้ใน Oracle 18c ล่าสุด

แต่หน้าที่ของเราในฐานะผู้เชี่ยวชาญด้านเทคนิคคือการใช้ประโยชน์จากสิ่งที่เรามีให้ดีที่สุด ดังนั้นฉันจึงใช้ดัชนีที่เทียบเท่ากับตัวกรองในระบบ Oracle 11g ของฉัน (และเทคนิคเดียวกับที่ฉันใช้ในภายหลังใน 12c) แนวคิดนี้ขึ้นอยู่กับวิธีที่ Oracle จัดการ NULL และสามารถใช้ได้ใน Oracle เวอร์ชันใดก็ได้

Oracle ไม่ปฏิบัติกับ NULL เช่นเดียวกับข้อมูลปกติ NULL ใน Oracle ไม่มีอะไร—ไม่มีอยู่จริง ดังนั้น หากคุณกำหนดคอลัมน์ที่จัดทำดัชนีของคุณเป็น NULLABLE และคุณกำลังค้นหาด้วยค่าที่ไม่ใช่ NULL ไฟล์ข้อมูลดัชนีของคุณจะมีเฉพาะบันทึกที่น่าสนใจเท่านั้น เนื่องจากคำนิยามดัชนีของ Oracle ไม่มีส่วนคำสั่ง INCLUDE คุณจะต้องสร้างดัชนีแบบรวมที่มีคอลัมน์ทั้งหมดที่ต้องรวมอยู่ในชุดผลลัพธ์ (เทคนิคนี้มีค่าใช้จ่ายบางอย่างเมื่อเทียบกับส่วนคำสั่ง INCLUDE ของ SQL Server แต่ไม่มีนัยสำคัญพอสมควร)

การแก้ปัญหาชั่วคราวดังกล่าวจะเพิ่มข้อจำกัด: คอลัมน์ดัชนีชั้นนำต้องอนุญาตให้มี NULL ดังนั้นจึงไม่สามารถเป็นคีย์หลักของตารางได้ อย่างไรก็ตาม อาจ เป็นคอลัมน์ที่ได้รับหรือจากการคำนวณซึ่งสร้างขึ้นเพื่อรองรับวิธีการเพิ่มประสิทธิภาพประสิทธิภาพนี้โดยเฉพาะ ในแง่หนึ่ง คอลัมน์นำหน้าของดัชนีจะเป็นไบนารีเชิงตรรกะ: ค่าที่ไม่ใช่ NULL สำหรับข้อมูลที่รวมอยู่ในการค้นหาของคุณ และ NULL สำหรับข้อมูลใดๆ ที่ควร "มองไม่เห็น"

ตัวเลือกอื่นที่เป็นไปได้ในการย้ายลอจิกดัชนีกรอง SQL Server ไปยัง Oracle คือการนำดัชนี (หรือตารางทั้งหมดไปใช้) เป็นพาร์ติชัน ในกรณีนี้ กลไกฐานข้อมูลจะเข้าถึงเฉพาะพาร์ติชั่นดัชนีที่เกี่ยวข้องเท่านั้น—การสืบค้นที่ให้มาจะถูกนำไปใช้อย่างถูกต้องโดยใช้เงื่อนไขการแบ่งพาร์ติชั่นที่แน่นอนในส่วน WHERE

การดำเนินการนี้จะทำงานได้ดีแม้ในขนาดที่เหมาะสมกับข้อมูลที่ค่อนข้างคงที่ แต่อาจทำให้มีภาระการบำรุงรักษาสูงในทีม DBA หากนำไปใช้กับข้อมูลที่เปลี่ยนแปลงบ่อย ตัวอย่างเช่น เมื่อปรับการเข้าถึงข้อมูลวันนี้ให้เหมาะสมในแอปพลิเคชันที่เน้นเวลาเป็นหลัก: ทีม DBA จะต้องกำหนดพาร์ติชั่นใหม่ทุกวัน แม้ว่าการกำหนดนิยามใหม่นี้สามารถเขียนสคริปต์ในงานบำรุงรักษาตอนกลางคืนได้ แต่จะทำให้ระบบของคุณซับซ้อนมากขึ้นและแนะนำจุดความล้มเหลวของระบบใหม่ที่อาจเกิดขึ้น

ดังนั้น เราต้องมีความเฉพาะเจาะจงและระมัดระวังทุกครั้งที่ต้องย้ายลอจิกดัชนีที่กรองด้วย SQL Server ไปยัง Oracle

วิธีจัดการกับคอนเวอร์ชั่น

ด้วยการย้าย Oracle ไปยัง SQL Server ให้มองหาโอกาสในการปรับให้เหมาะสมโดยใช้ดัชนีที่กรอง คุณจะไม่เห็นดัชนีที่ถูกกรองใน Oracle แต่คุณอาจเห็นดัชนีที่มี NULL อย่าคัดลอกตามที่เป็นอยู่: อาจเป็นที่ที่ดีที่สุดที่คุณสามารถเพิ่มประสิทธิภาพและปรับปรุงการออกแบบในการแปลงของคุณ

สำหรับการโยกย้าย SQL Server ไปยัง Oracle หากคุณเห็นดัชนีที่ถูกกรอง ให้มองหาวิธีหลีกเลี่ยงปัญหาคอขวดด้านประสิทธิภาพในโค้ด Oracle ที่เกี่ยวข้องของคุณ ดูวิธีที่คุณสามารถออกแบบโฟลว์ข้อมูลใหม่เพื่อชดเชยการเพิ่มประสิทธิภาพที่ขาดหายไปซึ่งดัชนีที่กรองได้ระบุไว้ในการใช้งานแหล่งที่มา

ความท้าทายในการโยกย้ายเซิร์ฟเวอร์ SQL ไปยัง Oracle / Oracle ไปยัง SQL Server Demystified

สำหรับโครงการการย้ายข้อมูลระหว่าง Oracle และ SQL Server ในทิศทางใดทิศทางหนึ่ง จำเป็นต้องมีความเข้าใจอย่างลึกซึ้งเกี่ยวกับกลไกที่เกี่ยวข้อง เมื่อฐานข้อมูลที่เผยแพร่ในปัจจุบัน (Oracle 18c และ Microsoft SQL Server 2017*) มีคำศัพท์ที่เทียบเท่ากันของฟังก์ชันของกันและกัน เช่น ตามลำดับและเอกลักษณ์ อาจดูเหมือนเป็นชัยชนะที่ง่ายดาย แต่การคัดลอกการออกแบบที่ดีบน RDBMS หนึ่งไปยังอีกชุดหนึ่งโดยตรงอาจส่งผลให้โค้ดมีความซับซ้อนและประสิทธิภาพต่ำโดยไม่จำเป็น

ในตอนต่อไปและตอนสุดท้ายของชุดนี้ ฉันจะพูดถึงความสอดคล้องในการอ่านและการใช้เครื่องมือการย้ายข้อมูล คอยติดตาม!

* SQL Server 2019 (หรือ “15.x”) ไม่นานพอสำหรับการนำไปใช้ในองค์กรอย่างแพร่หลาย