Oracle Tech Talk

All about Oracle Technologies

Tuesday 29 September 2015

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

16:33 Posted by NTP , No comments
Bài viết này sẽ giới thiệu về từ khóa UPSERT | UPSERT ALL và UPDATE khi được sử dụng kèm theo với từ khóa RULES trong mệnh đề MODEL.

1. Từ khóa UPSERT
UPSERT là từ khóa viết tắt của UPdate và inSERT được sử dụng trong Oracle - nghĩa là trong tập dữ liệu gốc ban đầu nếu như tồn tại giá trị tính toán thì từ khóa này sẽ thực hiện UPdate dữ liệu, còn nếu như không tồn tại giá trị tính toán thì từ khóa này sẽ inSERT 1 dòng dữ liệu mới.
Chúng ta xét câu lệnh sau:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month IN (11, 12)
MODEL
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
    RULES UPSERT
        (sales_amount [12, 2003] = sales_amount[11, 2003] * 1.1,
        sales_amount [1, 2004] = sales_amount[12, 2003] * 1.1)
ORDER BY prd_type_id, year, month;
(Câu lệnh 1)
Kết quả:
(Hình 1)
Chúng ta thấy rằng doanh số bán hàng của tháng 12/2003 được UPdate và doanh số bán hàng của tháng 01/2004 thì được inSERT.

* Kết hợp với từ khóa RETURN UPDATED ROWS
Câu lệnh:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month IN (11, 12)
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
    RULES UPSERT
        (sales_amount [12, 2003] = sales_amount[11, 2003] * 1.1,
        sales_amount [1, 2004] = sales_amount[12, 2003] * 1.1)
ORDER BY prd_type_id, year, month;
(Câu lệnh 2)
Kết quả:
(Hình 2)
Khi kết hợp với từ khóa RETURN UPDATED ROWS thì chỉ có các dòng được UPdate hoặc inSERT mới có trong tập kết quả trả về. Với Câu lệnh 1 thì từ khóa RETURN ALL ROWS mặc định được sử dụng (Xem thêm bài viết Các cách trả dữ liệu về với từ khóa RETURN)

Lưu ý: từ khóa UPSERT mặc định sẽ được sử dụng nếu như chúng ta không khai báo

2. Từ khóa UPDATE
Khi sử dụng với từ khóa này thì chỉ có các giá trị nào tồn tại trong tập dữ liệu gốc ban đầu mới được cập nhật. Các giá trị không tồn tại sẽ không được xử lý tính toán.
Câu lệnh:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month IN (11, 12)
MODEL
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
    RULES UPDATE
        (sales_amount [12, 2003] = sales_amount[11, 2003] * 1.1,
        sales_amount [1, 2004] = sales_amount[12, 2003] * 1.1)
ORDER BY prd_type_id, year, month;
(Câu lệnh 3)
Kết quả:
(Hình 3)
Tập dữ liệu gốc ban đầu trước khi tính toán chứa doanh số bán hàng của 2 tháng: 11/2003 và 12/2003. Khi tập này được sử dụng với từ khóa UPDATE thì chỉ có biểu thức tính toán sales_amount [12, 2003] = sales_amount[11, 2003] * 1.1 được áp dụng, biểu thức còn lại không được áp dụng nên tập kết quả trả về sẽ không có dữ liệu của tháng 01/2004.

* Kết hợp với từ khóa RETURN UPDATED ROWS
Câu lệnh:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month IN (11, 12)
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
    RULES UPDATE
        (sales_amount [12, 2003] = sales_amount[11, 2003] * 1.1,
        sales_amount [1, 2004] = sales_amount[12, 2003] * 1.1)
ORDER BY prd_type_id, year, month;
(Câu lệnh 4)
Kết quả:
(Hình 4)

3. Từ khóa UPSERT ALL
Chúng ta so sánh 2 câu lệnh sau:
SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month IN (11, 12)
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
    RULES UPSERT
     (sales_amount [11, 2004] = 
       sales_amount[11, 2003] * 1.1,
     sales_amount [12, 2004] = 
       sales_amount[12, 2003] * 1.1)
ORDER BY prd_type_id, year, month;

SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month 
         IN (11, 12)
MODEL 
    RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
    RULES UPSERT
        (sales_amount [any, 2004] = 
           sales_amount[cv(), 2003] * 1.1)
ORDER BY prd_type_id, year, month;
(Câu lệnh 5)
(Câu lệnh 6)
Kết quả:
(Hình 5)

(Hình 6)
Khi viết câu lệnh 6, chúng ta muốn nó là cách viết gọn của câu lệnh 5 nhưng kết quả lại không như ta mong đợi, câu lệnh 5 cho kết quả như hình 5 còn với câu lệnh 6 thì không có dữ liệu trả về.

Câu lệnh 5 sử dụng Positional Notation trong khi câu lệnh 6 lại sử dụng Symbolic Notation (xem bài Cách truy xuất dữ liệu (Positional Cell Reference và Symbolic Cell Reference)). Trong khi đó từ khóa ANY thì luôn được sử dụng với Symbolic Notation (xem bài Cách sử dụng hàm CV, từ khóa ANY và IS ANY) và sẽ không có dòng mới được insert.

Vì vậy, với câu lệnh 6 muốn kết quả trả về như hình 5 chúng ta phải dùng từ khóa UPSERT ALL.
Câu lệnh:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month IN (11, 12)
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
    RULES UPSERT ALL
        (sales_amount [any, 2004] = 
           sales_amount[cv(), 2003] * 1.1)
ORDER BY prd_type_id, year, month;
(Câu lệnh 7)
Chú ý: ngoài từ khóa ANY thì các phép so sánh (>, >=, <, <=) hay từ khóa IN cũng cho kết quả như ví dụ trên.
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

Friday 25 September 2015

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

11:46 Posted by NTP , No comments
Bài viết này sẽ giới thiệu cách sử dụng từ khóa ITERATE trong mệnh đề MODEL.

Trong mệnh đề MODEL, chúng ta có thể sử dụng vòng lặp cho các biểu thức tính toán bằng cách sử dụng từ khóa ITERATE.
 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> ... )

Chúng ta xét câu lệnh sau:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
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
        ITERATE (3)
        (sales_amount [2, 2003] = sales_amount[1, 2003] + 1000)
ORDER BY prd_type_id, year, month;
(Câu lệnh 1)
Câu lệnh trên sẽ thực thi biểu thức tính toán doanh số bán hàng của tháng 2/2003 3 lần.

1. Sử dụng với hàm ITERATION_NUMBER
Để biết được vòng lặp đang thực thi ở lần thứ mấy, Oracle cung cấp cho chúng ta hàm ITERATION_NUMBER. Giá trị bắt đầu của hàm này là 0 và được tăng dần lên 1 đơn vị. Chúng ta bổ sung vào câu lệnh 1 một cột nữa để kiểm tra sự hoạt động của hàm ITERATION_NUMBER.
Câu lệnh:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
SELECT prd_type_id,
       year,
       month,
       sales_amount,
       iterator
  FROM all_sales a
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount, CAST (NULL AS VARCHAR2 (20)) AS iterator)
    RULES
        ITERATE (3)
        (sales_amount [2, 2003] = sales_amount[1, 2003] + 1000,
        iterator [2, 2003] = iterator[2, 2003] || ITERATION_NUMBER || ' ')
ORDER BY prd_type_id, year, month;
(Câu lệnh 2)
Kết quả:
(Hình 1)
Chúng ta thấy rằng cột ITERATOR qua các lần lặp sẽ cho chúng ta biết được giá trị hiện tại của các lần lặp đó và có giá trị khởi đầu là 0 ở lần lặp đầu tiên.

Chúng ta cũng lưu ý về cách thức hoạt động của vòng lặp như sau:
  • Lần lặp thứ 1
    • Thực thi biểu thức tính toán 1
    • Thực thi biểu thức tính toán 2
    • ...
    • Thực thi biểu thức tính toán n
  • Lần lặp thứ 2
    • Thực thi biểu thức tính toán 1
    • Thực thi biểu thức tính toán 2
    • ...
    • Thực thi biểu thức tính toán n
  • Lần lặp thứ n
    • Thực thi biểu thức tính toán 1
    • Thực thi biểu thức tính toán 2
    • ...
    • Thực thi biểu thức tính toán n
Xét câu lệnh sau để thấy cách hoạt động của vòng lặp:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
SELECT prd_type_id,
       year,
       month,
       sales_amount,
       iterator
  FROM all_sales a
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount, CAST (NULL AS VARCHAR2 (20)) AS iterator)
    RULES
        ITERATE (3)
        (sales_amount [2, 2003] = sales_amount[1, 2003] + 1000,
        iterator [2, 2003] = iterator[2, 2003] || 'x',
        iterator [2, 2003] = iterator[2, 2003] || 'y')
ORDER BY prd_type_id, year, month;
(Câu lệnh 3)
Kết quả:

(Hình 2)
2. Sử dụng với từ khóa UNTIL
Từ khóa UNTIL được sử dụng kèm theo mệnh đề điều kiện để kết thúc vòng lặp khi thỏa điều kiện. Chúng ta xét ví dụ sau:
 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,
       iterator
  FROM all_sales a
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount, CAST (NULL AS VARCHAR2 (20)) iterator)
    RULES
        ITERATE (12) UNTIL sales_amount[12, 2003] > 4500
        (sales_amount [12, 2003] =
                  sales_amount[ITERATION_NUMBER + 1, 2003]
                + sales_amount[ITERATION_NUMBER + 2, 2003],
        iterator [12, 2003] =
                iterator[12, 2003] || ITERATION_NUMBER || ' ')
ORDER BY prd_type_id, year, month;
(Câu lệnh 4)
Kết quả:
(Hình 3)

Câu lệnh 4 có số lần lặp cho các biểu thức tính toán là 12 lần và sẽ dừng biểu thức tính toán khi thỏa điều kiện của biểu thức điều kiện: sales_amount[12, 2003] > 4500. Kết quả cho thấy với PRD_TYPE_ID là 1 thì ở lần lặp thứ 1 đã thỏa điều kiện trong khi PRD_TYPE_ID là 2 thì ở lần lặp thứ 3 mới thỏa.

Chú ý: các biểu thức tính toán sẽ được thực hiện ít nhất 1 lần trước khi xét đến biểu thức điều kiện được khai báo với từ khóa UNTIL.

3. Sử dụng với hàm PREVIOUS
Để tham chiếu với giá trị trước đó trong vòng lặp, chúng ta có thể dùng hàm PREVIOUS.
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,
       iterator
  FROM all_sales a
MODEL RETURN UPDATED ROWS
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount, CAST (NULL AS VARCHAR2 (20)) iterator)
    RULES
        ITERATE (12) UNTIL PREVIOUS (sales_amount[12, 2003]) > 4500
        (sales_amount [12, 2003] =
                  sales_amount[ITERATION_NUMBER + 1, 2003]
                + sales_amount[ITERATION_NUMBER + 2, 2003],
        iterator [12, 2003] =
                iterator[12, 2003] || ITERATION_NUMBER || ' ')
ORDER BY prd_type_id, year, month;
(Câu lệnh 5)
Kết quả:
(Hình 4)
Chú ý: hàm PREVIOUS chỉ được sử dụng với từ khóa UNTIL, các vị trí khác khi dùng hàm này sẽ bị báo lỗi:
ORA-32618: incorrect use of MODEL PREVIOUS function

4. Các vấn đề khác
  • Số vòng lặp tối đa được cho phép ở phiên bản 12c là: 4,294,967,295 (4Gig - 1) lần.
  • Hàm ITERATION_NUMBER có thể được sử dụng trong biểu thức điều kiện của từ khóa UNTIL nhưng không được sử dụng trong PARTITION BY hoặc DIMENSION BY hoặc MEASURES.
  • Bản chất của việc lặp này là với mỗi giá trị duy nhất trong PARTITION BY Oracle sẽ lặp lại n lần bộ các biểu thức tính toán được khai báo.
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

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