การปรับสมดุลการเขียนและการอ่านในฐานข้อมูล

ในคราวที่แล้ว статье ฉันอธิบายแนวคิดและการใช้งานฐานข้อมูลที่สร้างขึ้นบนพื้นฐานของฟังก์ชัน แทนที่จะเป็นตารางและฟิลด์เช่นเดียวกับในฐานข้อมูลเชิงสัมพันธ์ มีตัวอย่างมากมายที่แสดงให้เห็นถึงข้อดีของแนวทางนี้เหนือแนวทางคลาสสิก หลายคนพบว่าพวกเขาไม่น่าเชื่อเพียงพอ

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

ลักษณะ

เช่นเคย เพื่อความเข้าใจที่ดีขึ้น ฉันจะเริ่มคำอธิบายด้วยตัวอย่าง สมมติว่าเราจำเป็นต้องใช้ตรรกะที่จะส่งคืนรายชื่อแผนกพร้อมจำนวนพนักงานและเงินเดือนทั้งหมด

ในฐานข้อมูลเชิงฟังก์ชันจะมีลักษณะดังนี้:

CLASS Department ‘Отдел’;
name ‘Наименование’ = DATA STRING[100] (Department);

CLASS Employee ‘Сотрудник’;
department ‘Отдел’ = DATA Department (Employee);
salary ‘Зарплата’ =  DATA NUMERIC[10,2] (Employee);

countEmployees ‘Кол-во сотрудников’ (Department d) = 
    GROUP SUM 1 IF department(Employee e) = d;
salarySum ‘Суммарная зарплата’ (Department d) = 
    GROUP SUM salary(Employee e) IF department(e) = d;

SELECT name(Department d), countEmployees(d), salarySum(d);


ความซับซ้อนของการดำเนินการค้นหานี้ใน DBMS ใด ๆ จะเทียบเท่ากับ O(จำนวนพนักงาน)เพราะการคำนวณนี้ต้องสแกนตารางพนักงานทั้งหมดแล้วจัดกลุ่มตามแผนก นอกจากนี้ยังมีส่วนเสริมเล็กๆ น้อยๆ (เราเชื่อว่ามีพนักงานมากกว่าแผนก) ขึ้นอยู่กับแผนที่เลือก O(บันทึกจำนวนพนักงาน) หรือ O(จำนวนแผนก) สำหรับการจัดกลุ่มและอื่นๆ

เป็นที่ชัดเจนว่าค่าใช้จ่ายในการดำเนินการอาจแตกต่างกันใน DBMS ที่แตกต่างกัน แต่ความซับซ้อนจะไม่เปลี่ยนแปลงแต่อย่างใด

ในการใช้งานที่เสนอ DBMS ฟังก์ชันจะสร้างแบบสอบถามย่อยหนึ่งรายการที่จะคำนวณค่าที่จำเป็นสำหรับแผนก จากนั้นทำการเข้าร่วมกับตารางแผนกเพื่อรับชื่อ อย่างไรก็ตาม สำหรับแต่ละฟังก์ชัน เมื่อประกาศ คุณสามารถตั้งค่าเครื่องหมาย MATERIALIZED พิเศษได้ ระบบจะสร้างฟิลด์ที่เกี่ยวข้องสำหรับแต่ละฟังก์ชันดังกล่าวโดยอัตโนมัติ เมื่อเปลี่ยนค่าของฟังก์ชัน ค่าของฟิลด์จะเปลี่ยนในธุรกรรมเดียวกันด้วย เมื่อเข้าถึงฟังก์ชันนี้ จะเข้าถึงฟิลด์ที่คำนวณไว้ล่วงหน้า

โดยเฉพาะอย่างยิ่ง หากคุณตั้งค่า MATERIALIZED สำหรับฟังก์ชัน นับพนักงาน и เงินเดือนSumจากนั้นจะมีการเพิ่มสองฟิลด์ลงในตารางพร้อมกับรายชื่อแผนกซึ่งจะจัดเก็บจำนวนพนักงานและเงินเดือนทั้งหมด เมื่อใดก็ตามที่มีการเปลี่ยนแปลงพนักงาน เงินเดือน หรือสังกัดแผนก ระบบจะเปลี่ยนค่าของฟิลด์เหล่านี้โดยอัตโนมัติ ข้อความค้นหาข้างต้นจะเข้าถึงช่องเหล่านี้โดยตรงและจะดำเนินการในนั้น O(จำนวนแผนก).

มีข้อจำกัดอะไรบ้าง? มีเพียงสิ่งเดียวเท่านั้น: ฟังก์ชันดังกล่าวจะต้องมีค่าอินพุตจำนวนจำกัดซึ่งกำหนดค่าไว้ มิฉะนั้น จะไม่สามารถสร้างตารางที่เก็บค่าทั้งหมดได้ เนื่องจากไม่สามารถมีตารางที่มีจำนวนแถวไม่สิ้นสุดได้

ตัวอย่าง:

employeesCount ‘Количество сотрудников с зарплатой > N’ (Department d, NUMERIC[10,2] N) = 
    GROUP SUM salary(Employee e) IF department(e) = d AND salary(e) > N;


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

ตัวอย่างเช่น ในปัญหา 2.2 ของบทความก่อนหน้านี้ คุณสามารถใส่ MATERIALIZED บนทั้งสองฟังก์ชันได้:

bought 'Купил' (Customer c, Product p, INTEGER y) = 
    GROUP SUM sum(Detail d) IF 
        customer(order(d)) = c AND 
        product(d) = p AND 
        extractYear(date(order(d))) = y MATERIALIZED;
rating 'Рейтинг' (Customer c, Product p, INTEGER y) = 
    PARTITION SUM 1 ORDER DESC bought(c, p, y), p BY c, y MATERIALIZED;
SELECT contactName(Customer c), name(Product p) WHERE rating(c, p, 1997) < 3;


ระบบจะสร้างตารางหนึ่งตารางพร้อมคีย์ประเภท ลูกค้า, ผลิตภัณฑ์ и จำนวนเต็มจะเพิ่มสองฟิลด์ลงไปและจะอัปเดตค่าฟิลด์ในนั้นหากมีการเปลี่ยนแปลงใด ๆ เมื่อมีการเรียกใช้ฟังก์ชันเหล่านี้เพิ่มเติม จะไม่ได้รับการคำนวณ แต่จะอ่านค่าจากฟิลด์ที่เกี่ยวข้อง

การใช้กลไกนี้ คุณสามารถกำจัดการเรียกซ้ำ (CTE) ในเคียวรีได้ เป็นต้น โดยเฉพาะอย่างยิ่ง ให้พิจารณากลุ่มที่สร้างแผนผังโดยใช้ความสัมพันธ์ระหว่างเด็ก/ผู้ปกครอง (แต่ละกลุ่มมีลิงก์ไปยังกลุ่มผู้ปกครอง):

parent = DATA Group (Group);


ในฐานข้อมูลเชิงฟังก์ชัน ตรรกะการเรียกซ้ำสามารถระบุได้ดังต่อไปนี้:

level (Group child, Group parent) = RECURSION 1l IF child IS Group AND parent == child
                                                             STEP 2l IF parent == parent($parent);
isParent (Group child, Group parent) = TRUE IF level(child, parent) MATERIALIZED;


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

childrenCount (Group g) = GROUP SUM 1 IF isParent(Group child, g);


จะไม่มี CTE ในแบบสอบถาม SQL แต่จะมี GROUP BY แบบธรรมดาแทน

การใช้กลไกนี้ทำให้คุณสามารถทำให้ฐานข้อมูลเป็นปกติได้อย่างง่ายดายหากจำเป็น:

CLASS Order 'Заказ';
date 'Дата' = DATA DATE (Order);

CLASS OrderDetail 'Строка заказа';
order 'Заказ' = DATA Order (OrderDetail);
date 'Дата' (OrderDetail d) = date(order(d)) MATERIALIZED INDEXED;


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

ข้อดี

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

ในทางปฏิบัติมีลักษณะเช่นนี้ บางคนพัฒนาตรรกะตามงานที่ทำอยู่โดยตรง พวกเขาไม่เข้าใจอัลกอริธึมและความซับซ้อน ตลอดจนแผนการดำเนินการ หรือประเภทของการรวม หรือองค์ประกอบทางเทคนิคอื่น ๆ คนเหล่านี้เป็นนักวิเคราะห์ธุรกิจมากกว่านักพัฒนา จากนั้น ทั้งหมดนี้เข้าสู่การทดสอบหรือการใช้งาน เปิดใช้งานการบันทึกการสืบค้นที่ใช้เวลานาน เมื่อตรวจพบข้อความค้นหาที่ยาว บุคคลอื่น (ทางเทคนิคมากกว่า - โดยพื้นฐานแล้ว DBA) จะตัดสินใจเปิดใช้งาน MATERIALIZED ในฟังก์ชันระดับกลางบางอย่าง ซึ่งจะทำให้การบันทึกช้าลงเล็กน้อย (เนื่องจากต้องมีการอัปเดตฟิลด์เพิ่มเติมในธุรกรรม) อย่างไรก็ตาม ไม่เพียงแต่การสืบค้นนี้จะช่วยเร่งความเร็วได้อย่างมาก แต่ยังรวมไปถึงการสืบค้นอื่นๆ ทั้งหมดที่ใช้ฟังก์ชันนี้ด้วย ในขณะเดียวกัน การตัดสินใจเลือกฟังก์ชันที่จะทำให้เป็นจริงนั้นค่อนข้างง่าย พารามิเตอร์หลักสองตัว: จำนวนค่าอินพุตที่เป็นไปได้ (นี่คือจำนวนบันทึกที่จะอยู่ในตารางที่เกี่ยวข้อง) และความถี่ที่ใช้ในฟังก์ชันอื่น ๆ

analogs

DBMS เชิงพาณิชย์สมัยใหม่มีกลไกที่คล้ายกัน: MATERIALIZED VIEW พร้อม FAST REFRESH (Oracle) และ INDEXED VIEW (Microsoft SQL Server) ใน PostgreSQL ไม่สามารถอัปเดต MATERIALIZED VIEW ในธุรกรรมได้ แต่ทำได้ตามคำขอเท่านั้น (และถึงแม้จะมีข้อจำกัดที่เข้มงวดมาก) ดังนั้นเราจึงไม่พิจารณาเรื่องนี้ แต่พวกเขามีปัญหาหลายประการที่จำกัดการใช้งานอย่างมาก

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

ประการที่สอง มีข้อจำกัดมากมาย:

คำพยากรณ์

5.3.8.4 ข้อจำกัดทั่วไปในการรีเฟรชอย่างรวดเร็ว

แบบสอบถามที่กำหนดของมุมมองที่เป็นรูปธรรมถูกจำกัดดังต่อไปนี้:

• มุมมองที่เป็นรูปธรรมต้องไม่มีการอ้างอิงถึงนิพจน์ที่ไม่ซ้ำ เช่น SYSDATE และ ROWNUM.
• มุมมองที่เป็นรูปธรรมจะต้องไม่มีการอ้างอิงถึง RAW or LONG RAW ชนิดข้อมูล
• มันไม่สามารถมี SELECT รายการแบบสอบถามย่อย
• ไม่สามารถมีฟังก์ชันการวิเคราะห์ได้ (เช่น RANK) ใน SELECT ประโยค
• ไม่สามารถอ้างอิงตารางที่ XMLIndex ดัชนีถูกกำหนดไว้
• มันไม่สามารถมี MODEL ประโยค
• มันไม่สามารถมี HAVING ข้อที่มีแบบสอบถามย่อย
• ไม่สามารถมีข้อความค้นหาแบบซ้อนที่มี ANY, ALL,หรือ NOT EXISTS.
• มันไม่สามารถมี [START WITH …] CONNECT BY ประโยค
• ไม่สามารถมีตารางรายละเอียดหลายรายการในไซต์ต่างๆ ได้
• ON COMMIT มุมมองที่เป็นรูปธรรมไม่สามารถมีตารางรายละเอียดระยะไกลได้
• มุมมองที่เป็นรูปธรรมที่ซ้อนกันจะต้องมีการรวมหรือการรวม
• มุมมองการรวมที่เป็นรูปธรรมและมุมมองรวมที่เป็นรูปธรรมด้วย GROUP BY ข้อไม่สามารถเลือกจากตารางที่จัดดัชนี

5.3.8.5 ข้อจำกัดในการรีเฟรชอย่างรวดเร็วบน Materialized Views ที่มีการรวมเท่านั้น

การกำหนดเคียวรีสำหรับมุมมองที่เป็นรูปธรรมด้วยการรวมเท่านั้นและไม่มีการรวมมีข้อจำกัดต่อไปนี้ในการรีเฟรชอย่างรวดเร็ว:

• ข้อจำกัดทั้งหมดจาก «ข้อจำกัดทั่วไปในการรีเฟรชอย่างรวดเร็ว"
• พวกเขาไม่สามารถมีได้ GROUP BY ข้อหรือมวลรวม
• แถวของตารางทั้งหมดใน FROM รายการจะต้องปรากฏใน SELECT รายการแบบสอบถาม
• บันทึกมุมมองที่เป็นรูปธรรมจะต้องมีแถวสำหรับตารางฐานทั้งหมดใน FROM รายการแบบสอบถาม
• คุณไม่สามารถสร้างมุมมองที่เป็นรูปธรรมที่รีเฟรชได้อย่างรวดเร็วจากหลายตารางด้วยการรวมแบบง่ายที่มีคอลัมน์ประเภทออบเจ็กต์ใน SELECT คำแถลง

นอกจากนี้ วิธีการรีเฟรชที่คุณเลือกจะไม่มีประสิทธิภาพสูงสุดหาก:

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

5.3.8.6 ข้อจำกัดในการรีเฟรชอย่างรวดเร็วบน Materialized Views พร้อม Aggregates

การกำหนดเคียวรีสำหรับมุมมองที่เป็นรูปธรรมด้วยการรวมหรือการรวมมีข้อจำกัดต่อไปนี้ในการรีเฟรชอย่างรวดเร็ว:

• ข้อจำกัดทั้งหมดจาก «ข้อจำกัดทั่วไปในการรีเฟรชอย่างรวดเร็ว"

รองรับการรีเฟรชอย่างรวดเร็วสำหรับทั้งคู่ ON COMMIT และ ON DEMAND มุมมองที่เป็นรูปธรรม อย่างไรก็ตาม มีข้อจำกัดต่อไปนี้:

• ตารางทั้งหมดในมุมมองที่เป็นรูปธรรมจะต้องมีบันทึกมุมมองที่เป็นรูปธรรม และบันทึกมุมมองที่เป็นรูปธรรมจะต้อง:
  • ประกอบด้วยคอลัมน์ทั้งหมดจากตารางที่อ้างอิงในมุมมองที่เป็นรูปธรรม
  • ระบุด้วย ROWID และ INCLUDING NEW VALUES.
  • ระบุ SEQUENCE ส่วนคำสั่งหากคาดว่าตารางจะมีการแทรก/การโหลดโดยตรง การลบ และการอัพเดตผสมกัน

• เหลือเพียง SUM, COUNT, AVG, STDDEV, VARIANCE, MIN และ MAX รองรับการรีเฟรชที่รวดเร็ว
• COUNT(*) ต้องระบุ
• ฟังก์ชันการรวมจะต้องเกิดขึ้นเฉพาะในส่วนนอกสุดของนิพจน์เท่านั้น นั่นก็คือมวลรวมเช่น AVG(AVG(x)) or AVG(x)+ AVG(x) ไม่ได้รับอนุญาต.
• สำหรับแต่ละผลรวมเช่น AVG(expr)ที่สอดคล้องกัน COUNT(expr) จะต้องมีอยู่ ออราเคิลขอแนะนำว่า SUM(expr) ระบุไว้
• If VARIANCE(expr) or STDDEV(expr) ระบุไว้ COUNT(expr) และ SUM(expr) จะต้องระบุ ออราเคิลขอแนะนำว่า SUM(expr *expr) ระบุไว้
• พื้นที่ SELECT คอลัมน์ในการกำหนดการสืบค้นต้องไม่เป็นนิพจน์ที่ซับซ้อนที่มีคอลัมน์จากตารางฐานหลายตาราง วิธีแก้ปัญหาที่เป็นไปได้คือการใช้มุมมองที่เป็นรูปธรรมแบบซ้อน
• พื้นที่ SELECT รายการจะต้องมีทั้งหมด GROUP BY คอลัมน์
• มุมมองที่เป็นรูปธรรมไม่ได้ขึ้นอยู่กับตารางระยะไกลตั้งแต่หนึ่งตารางขึ้นไป
• หากคุณใช้ CHAR ชนิดข้อมูลในคอลัมน์ตัวกรองของบันทึกมุมมองที่เป็นรูปธรรม ชุดอักขระของไซต์หลักและมุมมองที่เป็นรูปธรรมจะต้องเหมือนกัน
• หากมุมมองที่เป็นรูปธรรมมีอย่างใดอย่างหนึ่งต่อไปนี้ การรีเฟรชอย่างรวดเร็วจะรองรับเฉพาะการแทรก DML ทั่วไปและการโหลดโดยตรงเท่านั้น
  • ปรากฏเป็นรูปธรรมด้วย MIN or MAX มวลรวม
  • มุมมองที่เป็นรูปธรรมซึ่งมี SUM(expr) แต่ไม่มี COUNT(expr)
  • มุมมองที่เป็นรูปธรรมโดยไม่ต้อง COUNT(*)

  มุมมองที่เป็นรูปธรรมดังกล่าวเรียกว่ามุมมองที่เป็นรูปธรรมแบบแทรกเท่านั้น

• เป็นรูปธรรมด้วย MAX or MIN สามารถรีเฟรชได้อย่างรวดเร็วหลังจากลบหรือผสมคำสั่ง DML หากไม่มี WHERE ประโยค
  การรีเฟรชอย่างรวดเร็วสูงสุด/นาทีหลังจากลบหรือผสม DML จะไม่มีลักษณะการทำงานเหมือนกับกรณีแทรกเท่านั้น มันจะลบและคำนวณค่าสูงสุด/นาทีใหม่สำหรับกลุ่มที่ได้รับผลกระทบ คุณต้องตระหนักถึงผลกระทบต่อประสิทธิภาพการทำงาน
• มุมมองที่เป็นรูปธรรมพร้อมมุมมองที่มีชื่อหรือแบบสอบถามย่อยใน FROM สามารถรีเฟรชประโยคได้อย่างรวดเร็วหากสามารถรวมมุมมองได้อย่างสมบูรณ์ สำหรับข้อมูลว่ามุมมองใดที่จะรวมเข้าด้วยกัน โปรดดู การอ้างอิงภาษา Oracle Database SQL.
• หากไม่มีการรวมภายนอก คุณอาจมีตัวเลือกและเข้าร่วมได้ตามใจชอบ WHERE ประโยค
• มุมมองรวมที่เป็นรูปธรรมพร้อมการรวมภายนอกสามารถรีเฟรชได้อย่างรวดเร็วหลังจาก DML ทั่วไปและโหลดโดยตรง โดยมีเงื่อนไขว่าตารางด้านนอกเท่านั้นที่ได้รับการแก้ไข นอกจากนี้ ต้องมีข้อจำกัดเฉพาะในคอลัมน์การรวมของตารางการรวมภายใน หากมีการรวมภายนอก การรวมทั้งหมดจะต้องเชื่อมต่อด้วย ANDและต้องใช้ความเท่าเทียมกัน (=) ตัวดำเนินการ
• เพื่อปรากฏเป็นรูปธรรมด้วย CUBE, ROLLUPการจัดกลุ่มชุด หรือการต่อกัน มีข้อจำกัดต่อไปนี้:
  • พื้นที่ SELECT รายการควรมีตัวแยกแยะการจัดกลุ่มที่สามารถเป็น a ได้ GROUPING_ID ฟังก์ชั่นทั้งหมด GROUP BY การแสดงออกหรือ GROUPING ฟังก์ชั่นหนึ่งสำหรับแต่ละอัน GROUP BY การแสดงออก. ตัวอย่างเช่น ถ้า GROUP BY ประโยคแห่งทัศนะที่เป็นรูปธรรมคือ "GROUP BY CUBE(a, b)"แล้ว. SELECT รายการควรมีอย่างใดอย่างหนึ่ง "GROUPING_ID(a, b)" หรือ "GROUPING(a) AND GROUPING(b)» เพื่อให้เห็นภาพสามารถรีเฟรชได้รวดเร็ว
  • GROUP BY ไม่ควรส่งผลให้เกิดการจัดกลุ่มซ้ำกัน ตัวอย่างเช่น, "GROUP BY a, ROLLUP(a, b)" ไม่สามารถรีเฟรชได้อย่างรวดเร็วเนื่องจากส่งผลให้เกิดการจัดกลุ่มซ้ำกัน "(a), (a, b), AND (a)"

5.3.8.7 ข้อจำกัดในการรีเฟรชอย่างรวดเร็วบน Materialized Views ด้วย UNION ALL

มุมมองที่เป็นรูปธรรมด้วย UNION ALL ตั้งค่าโอเปอเรเตอร์รองรับ REFRESH FAST ตัวเลือกหากตรงตามเงื่อนไขต่อไปนี้:

• แบบสอบถามที่กำหนดต้องมี UNION ALL ผู้ปฏิบัติงานในระดับบนสุด

  พื้นที่ UNION ALL โอเปอเรเตอร์ไม่สามารถฝังอยู่ในแบบสอบถามย่อยได้ โดยมีข้อยกเว้นประการหนึ่ง: The UNION ALL สามารถอยู่ในแบบสอบถามย่อยใน FROM ประโยคที่กำหนดให้แบบสอบถามที่กำหนดอยู่ในรูปแบบ SELECT * FROM (ดูหรือสืบค้นย่อยด้วย UNION ALL) ดังตัวอย่างต่อไปนี้:

  สร้างมุมมอง view_with_unionall AS (เลือก c.rowid crid, c.cust_id, 2 umarker จากลูกค้า c WHERE c.cust_last_name = 'Smith' UNION ALL SELECT c.rowid crid, c.cust_id, 3 umarker จากลูกค้า c WHERE c.cust_last_name = 'โจนส์'); สร้างมุมมองที่เป็นรูปธรรม unionall_inside_view_mv รีเฟรชอย่างรวดเร็วตามความต้องการตามที่เลือก * จาก view_with_unionall;
  

  โปรดทราบว่าวิว view_with_unionall ตอบสนองความต้องการเพื่อการรีเฟรชที่รวดเร็ว

• แต่ละบล็อกแบบสอบถามใน UNION ALL แบบสอบถามต้องเป็นไปตามข้อกำหนดของมุมมอง Materialized ที่รีเฟรชได้อย่างรวดเร็วพร้อมการรวมหรือมุมมอง Materialized ที่รีเฟรชอย่างรวดเร็วพร้อมการรวม

  ต้องสร้างบันทึกมุมมองที่เป็นรูปธรรมที่เหมาะสมบนตารางตามที่จำเป็นสำหรับประเภทมุมมองที่รีเฟรชอย่างรวดเร็วที่สอดคล้องกัน
  โปรดทราบว่าฐานข้อมูล Oracle ยังอนุญาตให้ใช้กรณีพิเศษของมุมมองที่เป็นรูปธรรมของตารางเดียวพร้อมการรวมที่มีให้เท่านั้น ROWID คอลัมน์ถูกรวมไว้ใน SELECT รายการและในบันทึกมุมมองที่เป็นรูปธรรม สิ่งนี้แสดงในแบบสอบถามที่กำหนดของมุมมอง view_with_unionall.

• พื้นที่ SELECT รายการคำถามแต่ละรายการจะต้องมีก UNION ALL เครื่องหมายและ UNION ALL คอลัมน์จะต้องมีค่าตัวเลขหรือสตริงคงที่ที่แตกต่างกันในแต่ละคอลัมน์ UNION ALL สาขา. นอกจากนี้ คอลัมน์เครื่องหมายจะต้องปรากฏในตำแหน่งลำดับเดียวกันใน SELECT รายการของแต่ละบล็อคแบบสอบถาม ดู "UNION ALL Marker และการเขียนแบบสอบถามใหม่» สำหรับข้อมูลเพิ่มเติมเกี่ยวกับ UNION ALL เครื่องหมาย
• คุณสมบัติบางอย่าง เช่น การรวมภายนอก การสืบค้นมุมมองที่เป็นรูปธรรมรวมแบบแทรกเท่านั้น และตารางระยะไกล ไม่รองรับมุมมองที่เป็นรูปธรรมด้วย UNION ALL. อย่างไรก็ตาม โปรดทราบว่ามุมมองที่เป็นรูปธรรมที่ใช้ในการจำลองแบบ ซึ่งไม่มีการรวมหรือการรวม สามารถรีเฟรชได้อย่างรวดเร็วเมื่อ UNION ALL หรือใช้ตารางระยะไกล
• พารามิเตอร์การเริ่มต้นความเข้ากันได้ต้องตั้งค่าเป็น 9.2.0 หรือสูงกว่าเพื่อสร้างมุมมองที่รีเฟรชได้อย่างรวดเร็ว UNION ALL.

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

ที่ Microsoft SQL Server

ข้อกำหนดเพิ่มเติม

นอกเหนือจากตัวเลือก SET และข้อกำหนดฟังก์ชันที่กำหนดแล้ว ยังต้องเป็นไปตามข้อกำหนดต่อไปนี้:

• ผู้ใช้ที่ดำเนินการ CREATE INDEX จะต้องเป็นเจ้าของวิว
• เมื่อคุณสร้างดัชนีไฟล์ IGNORE_DUP_KEY ต้องตั้งค่าตัวเลือกเป็น OFF (การตั้งค่าเริ่มต้น)
• ตารางจะต้องอ้างอิงด้วยชื่อสองส่วน คี.ชื่อโต๊ะ ในคำจำกัดความของมุมมอง
• ฟังก์ชันที่ผู้ใช้กำหนดซึ่งอ้างอิงในมุมมองต้องถูกสร้างขึ้นโดยใช้ WITH SCHEMABINDING ตัวเลือก
• ฟังก์ชันที่ผู้ใช้กำหนดใดๆ ที่อ้างอิงในมุมมองจะต้องอ้างอิงด้วยชื่อสองส่วน ..
• คุณสมบัติการเข้าถึงข้อมูลของฟังก์ชันที่ผู้ใช้กำหนดจะต้องเป็น NO SQLและคุณสมบัติการเข้าถึงภายนอกจะต้องเป็น NO.
• ฟังก์ชันรันไทม์ภาษาทั่วไป (CLR) สามารถปรากฏในรายการที่เลือกของมุมมอง แต่ไม่สามารถเป็นส่วนหนึ่งของคำจำกัดความของคีย์ดัชนีคลัสเตอร์ได้ ฟังก์ชัน CLR ไม่สามารถปรากฏในส่วนคำสั่ง WHERE ของมุมมองหรือส่วนคำสั่ง ON ของการดำเนินการ JOIN ในมุมมอง
• ฟังก์ชัน CLR และวิธีการของประเภทที่ผู้ใช้กำหนด CLR ที่ใช้ในข้อกำหนดมุมมองจะต้องมีการตั้งค่าคุณสมบัติดังที่แสดงในตารางต่อไปนี้

  อสังหาริมทรัพย์
  หมายเหตุ

  กำหนด = จริง
  จะต้องประกาศอย่างชัดเจนว่าเป็นแอตทริบิวต์ของวิธีการ Microsoft .NET Framework

  แม่นยำ = จริง
  จะต้องประกาศอย่างชัดเจนว่าเป็นแอตทริบิวต์ของวิธีการ .NET Framework

  การเข้าถึงข้อมูล = ไม่มี SQL
  กำหนดโดยการตั้งค่าแอตทริบิวต์ DataAccess เป็น DataAccessKind.None และแอตทริบิวต์ SystemDataAccess เป็น SystemDataAccessKind.None

  การเข้าถึงภายนอก = ไม่
  คุณสมบัตินี้มีค่าเริ่มต้นเป็น NO สำหรับรูทีน CLR

• มุมมองจะต้องถูกสร้างขึ้นโดยใช้ WITH SCHEMABINDING ตัวเลือก
• มุมมองต้องอ้างอิงเฉพาะตารางฐานที่อยู่ในฐานข้อมูลเดียวกันกับมุมมอง มุมมองไม่สามารถอ้างอิงมุมมองอื่นได้
• คำสั่ง SELECT ในข้อกำหนดมุมมองต้องไม่มีองค์ประกอบ Transact-SQL ต่อไปนี้:

  COUNT
  ฟังก์ชัน ROWSET (OPENDATASOURCE, OPENQUERY, OPENROWSET, และ OPENXML)
  OUTER เข้าร่วม (LEFT, RIGHT,หรือ FULL)

  ตารางสืบทอด (กำหนดโดยการระบุ a SELECT คำสั่งใน FROM ข้อ)
  เข้าร่วมด้วยตนเอง
  การระบุคอลัมน์โดยใช้ SELECT * or SELECT <table_name>.*

  DISTINCT
STDEV, STDEVP, VAR, VARP,หรือ AVG
  นิพจน์ตารางทั่วไป (CTE)

  ลอย1, ข้อความ, ข้อความ, ภาพ, XML,หรือ สตรีมไฟล์ คอลัมน์
  แบบสอบถามย่อย
  OVER clause ซึ่งรวมถึงฟังก์ชันการจัดอันดับหรือหน้าต่างรวม

  ภาคแสดงข้อความแบบเต็ม (CONTAINS, FREETEXT)
  SUM ฟังก์ชันที่อ้างอิงถึงนิพจน์ที่เป็นโมฆะ
  ORDER BY

  ฟังก์ชันการรวมที่ผู้ใช้กำหนดของ CLR
  TOP
CUBE, ROLLUP,หรือ GROUPING SETS ผู้ประกอบการ

  MIN, MAX
UNION, EXCEPT,หรือ INTERSECT ผู้ประกอบการ
  TABLESAMPLE

  ตัวแปรตาราง
  OUTER APPLY or CROSS APPLY
PIVOT, UNPIVOT

  ชุดคอลัมน์กระจัดกระจาย
  ฟังก์ชันค่าตารางแบบอินไลน์ (TVF) หรือแบบหลายคำสั่ง (MSTVF)
  OFFSET

  CHECKSUM_AGG

  1 มุมมองที่จัดทำดัชนีสามารถประกอบด้วย ลอย คอลัมน์; อย่างไรก็ตาม คอลัมน์ดังกล่าวไม่สามารถรวมไว้ในคีย์ดัชนีแบบคลัสเตอร์ได้

• If GROUP BY มีอยู่ คำจำกัดความ VIEW ต้องมี COUNT_BIG(*) และต้องไม่มี HAVING. เหล่านี้ GROUP BY ข้อจำกัดมีผลกับคำจำกัดความมุมมองที่จัดทำดัชนีเท่านั้น แบบสอบถามสามารถใช้มุมมองที่จัดทำดัชนีในแผนการดำเนินการได้ แม้ว่าจะไม่เป็นไปตามข้อกำหนดเหล่านี้ก็ตาม GROUP BY ข้อ จำกัด
• หากคำจำกัดความของมุมมองมี GROUP BY ข้อคีย์ของดัชนีคลัสเตอร์ที่ไม่ซ้ำกันสามารถอ้างอิงเฉพาะคอลัมน์ที่ระบุใน GROUP BY ประโยค

เป็นที่แน่ชัดว่าชาวอินเดียไม่มีส่วนเกี่ยวข้อง เนื่องจากพวกเขาตัดสินใจทำตามแผน "เราจะทำน้อยแต่ก็ได้ผลดี" นั่นคือพวกเขามีทุ่นระเบิดในสนามมากกว่า แต่ตำแหน่งของพวกมันโปร่งใสกว่า สิ่งที่น่าผิดหวังที่สุดคือข้อจำกัดนี้:

มุมมองต้องอ้างอิงเฉพาะตารางฐานที่อยู่ในฐานข้อมูลเดียวกันกับมุมมอง มุมมองไม่สามารถอ้างอิงมุมมองอื่นได้

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

คำสั่ง SELECT ในข้อกำหนดมุมมองต้องไม่มีองค์ประกอบ Transact-SQL ต่อไปนี้:

COUNT
ฟังก์ชัน ROWSET (OPENDATASOURCE, OPENQUERY, OPENROWSET, และ OPENXML)
OUTER เข้าร่วม (LEFT, RIGHT,หรือ FULL)

ตารางสืบทอด (กำหนดโดยการระบุ a SELECT คำสั่งใน FROM ข้อ)
เข้าร่วมด้วยตนเอง
การระบุคอลัมน์โดยใช้ SELECT * or SELECT <table_name>.*

DISTINCT
STDEV, STDEVP, VAR, VARP,หรือ AVG
นิพจน์ตารางทั่วไป (CTE)

ลอย1, ข้อความ, ข้อความ, ภาพ, XML,หรือ สตรีมไฟล์ คอลัมน์
แบบสอบถามย่อย
OVER clause ซึ่งรวมถึงฟังก์ชันการจัดอันดับหรือหน้าต่างรวม

ภาคแสดงข้อความแบบเต็ม (CONTAINS, FREETEXT)
SUM ฟังก์ชันที่อ้างอิงถึงนิพจน์ที่เป็นโมฆะ
ORDER BY

ฟังก์ชันการรวมที่ผู้ใช้กำหนดของ CLR
TOP
CUBE, ROLLUP,หรือ GROUPING SETS ผู้ประกอบการ

MIN, MAX
UNION, EXCEPT,หรือ INTERSECT ผู้ประกอบการ
TABLESAMPLE

ตัวแปรตาราง
OUTER APPLY or CROSS APPLY
PIVOT, UNPIVOT

ชุดคอลัมน์กระจัดกระจาย
ฟังก์ชันค่าตารางแบบอินไลน์ (TVF) หรือแบบหลายคำสั่ง (MSTVF)
OFFSET

CHECKSUM_AGG

OUTER JOINS, UNION, ORDER BY และอื่นๆ เป็นสิ่งต้องห้าม การระบุสิ่งที่สามารถใช้ได้อาจง่ายกว่าการระบุสิ่งที่ใช้ไม่ได้ รายการน่าจะน้อยกว่านี้มาก

โดยสรุป: มีข้อจำกัดจำนวนมากในทุก DBMS (โปรดทราบเชิงพาณิชย์) เทียบกับไม่มีเลย (ยกเว้นตรรกะเดียว ไม่ใช่ทางเทคนิค) ในเทคโนโลยี LGPL อย่างไรก็ตาม ควรสังเกตว่าการนำกลไกนี้ไปใช้ในตรรกะเชิงสัมพันธ์นั้นค่อนข้างยากกว่าในตรรกะการทำงานที่อธิบายไว้

การดำเนินงาน

มันทำงานอย่างไร? PostgreSQL ถูกใช้เป็น “เครื่องเสมือน” มีอัลกอริธึมที่ซับซ้อนอยู่ภายในซึ่งสร้างแบบสอบถาม ที่นี่ แหล่งที่มา. และไม่ได้มีเพียงชุดฮิวริสติกชุดใหญ่ที่มี ifs มากมายเท่านั้น ดังนั้น หากคุณมีเวลาเรียนสองสามเดือน คุณก็สามารถพยายามทำความเข้าใจสถาปัตยกรรมได้

มันทำงานได้อย่างมีประสิทธิภาพหรือไม่? ค่อนข้างมีประสิทธิภาพ น่าเสียดายที่นี่เป็นเรื่องยากที่จะพิสูจน์ ฉันบอกได้แค่ว่าหากคุณพิจารณาคำค้นหานับพันที่มีอยู่ในแอปพลิเคชันขนาดใหญ่ โดยเฉลี่ยแล้วคำค้นหาเหล่านั้นจะมีประสิทธิภาพมากกว่าคำค้นหาของนักพัฒนาที่ดี โปรแกรมเมอร์ SQL ที่ยอดเยี่ยมสามารถเขียนแบบสอบถามได้อย่างมีประสิทธิภาพมากขึ้น แต่ด้วยแบบสอบถามนับพันรายการ เขาจึงไม่มีแรงจูงใจหรือเวลาในการเขียน สิ่งเดียวที่ฉันสามารถอ้างเป็นข้อพิสูจน์ถึงประสิทธิผลได้คือหลายโครงการกำลังทำงานบนแพลตฟอร์มที่สร้างขึ้นบน DBMS นี้ ระบบอีอาร์พีซึ่งมีฟังก์ชัน MATERIALIZED ที่แตกต่างกันหลายพันรายการ โดยมีผู้ใช้หลายพันรายและฐานข้อมูลเทราไบต์พร้อมบันทึกหลายร้อยล้านรายการที่ทำงานบนเซิร์ฟเวอร์ที่มีโปรเซสเซอร์สองตัวปกติ อย่างไรก็ตาม ใครๆ ก็สามารถตรวจสอบ/ปฏิเสธประสิทธิภาพได้โดยการดาวน์โหลด พลาทโฟรมุส และ PostgreSQL เปิด บันทึกคำสั่ง SQL และพยายามเปลี่ยนตรรกะและข้อมูลที่นั่น

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

ที่มา: will.com