Oracle Tech Talk

All about Oracle Technologies

Wednesday 23 September 2015

Cách sử dụng mệnh đề MODEL (Phần 6)

13:09 Posted by NTP , No comments
Bài viết này sẽ giới thiệu về thứ tự thực hiện của các biểu thức tính toán được sử dụng trong mệnh đề MODEL.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
<prior clauses of SELECT statements>
MODEL [main] [RETURN {ALL|UPDATED} ROWS]
  [reference models]
  [PARTITION BY (<cols>)]
  DIMENSION BY (<cols>)
  MEASURES (<cols>)
    [IGNORE NAV] | [KEEP NAV]
  [RULES
    [UPSERT | UPDATE]
    [AUTOMATIC ORDER | SEQUENTIAL ORDER]
    [ITERATE (n) [UNTIL <condition>] ]
    ( <cell_assignment> = <expression> ... )

Qua các phần trước, chúng ta thấy rằng trong mệnh đề MODEL có rất nhiều biểu thức tính toán được khai báo. Các biểu thức tính toán này có thể phụ thuộc lẫn nhau, kết quả tính toán của 1 biểu thức có thể sẽ là đầu vào cho 1 biểu thức tính toán tiếp theo. Do vậy việc chọn cách sắp xếp thứ tự của các phép tính khác nhau sẽ cho ra những kết quả khác nhau.

Chúng ta xét tập dữ liệu sau:
1
2
3
4
5
6
SELECT a.prd_type_id,
       a.year,
       a.month,
       a.amount
  FROM all_sales a
 WHERE month IN (10, 11, 12);
(Câu lệnh 1)
Kết quả:
(Hình 1)
Với tập kết quả này, giả sử chúng ta cần thực hiện các biểu thức tính toán sau:
  • Cập nhật doanh số bán hàng của 12/2003 biết rằng doanh số mới này bằng bình quân doanh số bán hàng của 10/2003 và 11/2003
  • Cập nhật doanh số bán hàng của 11/2003 biết rằng doanh số mới này bằng 110% doanh số bán hàng của 10/2003
  • Cập nhật doanh số bán hàng của 10/2003 là 1000

Với các yêu cầu như trên, tùy thuộc vào thứ tự của các biểu thức tính toán và các từ khóa sử dụng kèm theo mà ta sẽ có các kết quả khác nhau. Chúng ta lần lượt xét các trường hợp câu lệnh sau:

TRƯỜNG HỢP 1: thứ tự của các biểu thức tính toán lần lượt cho tháng 10, tháng 11 và tháng 12
Câu lệnh:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
    (sales_amount [10, 2003] =
                1000,
        sales_amount [11, 2003] =
                sales_amount[10, 2003] * 1.1,
        sales_amount [12, 2003] =
                  (  sales_amount[10, 2003]
                   + sales_amount[11, 2003])
                / 2)
ORDER BY prd_type_id, year, month;
(Câu lệnh 2)
Kết quả:
(Hình 2)

Chúng ta thấy rằng theo thứ tự thì biểu thức tính toán cho tháng 10 sẽ được thực hiện trước, biểu thức tính toán cho tháng 11 sẽ lấy kết quả của tháng 10 vừa tính được nhân cho 110%, biểu thức tính toán cho tháng 12 sẽ lấy tổng kết quả của tháng 10 và tháng 11 vừa tính được rồi chia cho 2.

TRƯỜNG HỢP 2: thứ tự của các biểu thức tính toán lần lượt cho tháng 12, tháng 11 và tháng 10
Câu lệnh:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
    (sales_amount [12, 2003] =
                  (  sales_amount[10, 2003]
                   + sales_amount[11, 2003])
                / 2,
        sales_amount [11, 2003] =
                sales_amount[10, 2003] * 1.1,
        sales_amount [10, 2003] =
                1000)
ORDER BY prd_type_id, year, month;
(Câu lệnh 3)
Kết quả:
(Hình 3)
Trường hợp này nhìn vào thứ tự của các biểu thức chúng ta thấy rằng biểu thức tính toán cho tháng 12 được thực hiện trước, khi đó giá trị của tháng 10 và tháng 11 chưa thay đổi. Tương tự cho biểu thức tính toán của tháng 11. Biểu thức tính toán cho tháng 10 cập nhật doanh số bán hàng về 1000.

Kết luận: Oracle mặc định sẽ thực hiện các biểu thức tính toán theo thứ tự được khai báo.

Tiếp theo chúng ta sẽ khảo sát về cách kết hợp thứ tự các biểu thức tính toán và các từ khóa AUTOMATIC ORDER | SEQUENTIAL ORDER.

TRƯỜNG HỢP 3: thứ tự của các biểu thức tính toán lần lượt cho tháng 12, tháng 11 và tháng 10; đồng thời sử dụng thêm các từ khóa AUTOMATIC ORDER | SEQUENTIAL ORDER.
Câu lệnh:
SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
    RULES AUTOMATIC ORDER
(
sales_amount[12, 2003] =
  (sales_amount[10, 2003] +
  sales_amount[11, 2003]) / 2,
sales_amount[11, 2003] = 
  sales_amount[10, 2003] * 1.1,
sales_amount[10, 2003] = 1000
)
ORDER BY prd_type_id, year, month;

SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
    RULES SEQUENTIAL ORDER
(
sales_amount[12, 2003] =
  (sales_amount[10, 2003] +
  sales_amount[11, 2003]) / 2,
sales_amount[11, 2003] = 
  sales_amount[10, 2003] * 1.1,
sales_amount[10, 2003] = 1000
)
ORDER BY prd_type_id, year, month;

Kết quả:
Khi sử dụng với từ khóa AUTOMATIC ORDER

Khi sử dụng với từ khóa SEQUENTIAL ORDER

Từ 2 kết quả trên chúng ta thấy rằng, khi sử dụng với:
  • AUTOMATIC ORDER: các biểu thức tính toán sẽ được sắp xếp lại theo thứ tự nhất định trước khi thực hiện tính toán.
  • SEQUENTIAL ORDER: các biểu thức tính toán sẽ được thực hiện tuần tư theo thứ tự được khai báo. Biểu thức tính toán nào được khai báo trước sẽ được tính trước, biểu thức nào khai báo sau sẽ được tính sau. Mỗi một trật tự biểu thức tính toán khác nhau sẽ cho các kết quả khác nhau.
Để hiểu rõ thêm về 2 cách dùng trên, chúng ta xét tiếp ví dụ sau:
SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
    RULES AUTOMATIC ORDER
(
sales_amount[2, 2004] =
  sales_amount[1, 2004] + 1000,
sales_amount[1, 2004] = 
  sales_amount[12, 2003] * 1.1, 
sales_amount[12, 2003] = 1000
)
ORDER BY prd_type_id, year, month;

SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
    RULES SEQUENTIAL ORDER
(
sales_amount[2, 2004] =
  sales_amount[1, 2004] + 1000,
sales_amount[1, 2004] = 
  sales_amount[12, 2003] * 1.1, 
sales_amount[12, 2003] = 1000 
)
ORDER BY prd_type_id, year, month;

Kết quả:
Khi sử dụng với từ khóa AUTOMATIC ORDER

Khi sử dụng với từ khóa SEQUENTIAL ORDER

Ở ví dụ này chúng ta thấy rằng khi sử dụng với AUTOMATIC ORDER thì mệnh đề này đảm bảo kết quả trả về không bị bất cứ giá trị NULL nào. Từ khóa này tạo ra 1 vòng lặp vô hạn để gán các giá trị tính toán vào đó. Chỉ đến khi nào kết quả trả về không có giá trị NULL nào thì vòng lặp này mới được xem là kết thúc. Chúng ta xét ví dụ sau để thấy được sự hoạt động của vòng lặp khi dùng với AUTOMATIC ORDER.
SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
    RULES AUTOMATIC ORDER
        (sales_amount [2, 2004] = sales_amount[1, 2004] + 1000,
        sales_amount [2, 2004] = 1000,
        sales_amount [1, 2004] = sales_amount[12, 2003] * 1.1,
        sales_amount [12, 2003] = 1000)
ORDER BY prd_type_id, year, month;

Câu lệnh trên sẽ báo lỗi:
ORA-32630: multiple assignment in automatic order MODEL
Nhưng nếu chúng ta thay bằng từ khóa SEQUENTIAL ORDER thì lỗi trên sẽ không xuất hiện.

Kết luận:
  • Khi sử dụng với từ khóa AUTOMATIC ORDER: Oracle sẽ dựa vào sự phụ thuộc của các biểu thức tính toán (biểu thức nào ít phụ thuộc nhất sẽ được thực hiện trước, kết quả của nó sẽ là đầu vào của biểu thức tiếp theo) để sắp xếp lại thứ tự các biểu thức chứ không dựa vào thứ tự khai báo và sẽ đảm bảo rằng kết quả trả về không có giá trị NULL nào. Với từ khóa này, Oracle không cho phép gán 1 biểu thức tính toán nhiều lần.
  • Khi sử dụng với từ khóa SEQUENTIAL ORDER: Oracle sẽ thực hiện tuần tự các biểu thức tính toán theo thứ tự được khai báo và không quan tâm đến sự phụ thuộc lẫn nhau của các biểu thức. Từ khóa này cho phép chúng ta gán 1 biểu thức tính toán được nhiều lần.
Lưu ý: khi chúng ta không khai báo sử dụng AUTOMATIC | SEQUENTIAL ORDER trong mệnh đề MODEL thì mặc định là từ khóa SEQUENTIAL ORDER sẽ được sử dụng.

Vậy có nên sử dụng AUTOMATIC ORDER trong tất cả các trường hợp không? Câu trả lời là tùy thuộc vào kết quả mà chúng ta muốn nhận. Nếu như chúng ta chắc chắn với thứ tự của các biểu thức thì chúng ta sẽ dùng SEQUENTIAL ORDER. Ngoài ra chúng ta cũng nên lưu ý là khi sử dụng với AUTOMATIC ORDER thì chúng ta sẽ mất thêm 1 khoảng chi phí về thời gian cho việc Oracle phải xây dựng và sắp xếp lại sự phụ thuộc của các biểu thức trước khi thực thi câu lệnh.
Các bài viết có liên quan
  1. Giới thiệu tổng quan
  2. Các cách trả dữ liệu về với từ khóa RETURN
  3. Cách truy xuất dữ liệu (Positional Cell Reference và Symbolic Cell Reference)
  4. Cách sử dụng hàm CV, từ khóa ANY và IS ANY
  5. Cách xử lý các giá trị NULL qua các từ khóa IS PRESENT | KEEP NAV | IGNORE NAV và các hàm PRESENTV | PRESENTNNV
  6. Quy định về trật tự tính toán của các biểu thức qua từ khóa AUTOMATIC ORDER | SEQUENTIAL ORDER
  7. Vòng lặp các biểu thức tính toán với từ khóa ITERATE
  8. Cách sử dụng từ khóa UPSERT | UPSERT ALL và UPDATE với từ khóa RULES
  9. Cách sử dụng từ khóa UNIQUE DIMENSION và UNIQUE SINGLE REFERENCE
  10. Cách sử dụng mệnh đề REFERENCE để tham chiếu dữ liệu

0 comments :

Post a Comment