Oracle Tech Talk

All about Oracle Technologies

Friday, 9 October 2015

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

16:00 Posted by NTP , No comments
Bài viết này sẽ hướng dẫn cách sử dụng từ khóa UNIQUE DIMENSION và UNIQUE SINGLE REFERENCE

Cú pháp: 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
<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]
    [UNIQUE DIMENSION | UNIQUE SINGLE REFERENCE]
  [RULES
    [UPSERT | UPDATE]
    [AUTOMATIC ORDER | SEQUENTIAL ORDER]
    [ITERATE (n) [UNTIL <condition>]]
    ( <cell_assignment> = <expression> ... )

1. Các cách sử dụng với từ khóa UNIQUE DIMENSION
Từ khóa UNIQUE DIMENSION được sử dụng khi chúng ta chắc chắn muốn Oracle kiểm tra việc trùng dữ liệu trước khi thực hiện việc tính toán các biểu thức trong mệnh đề MODEL.
Chúng ta giả lập ví dụ sau: thêm 1 dòng dữ liệu trùng vào bảng ALL_SALES
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- disable constraint for inserting duplicate values
ALTER TABLE all_sales DISABLE CONSTRAINT all_sales_pk;

INSERT INTO all_sales
VALUES (2003,
        12,
        2,
        21,
        2000);

COMMIT;
(Câu lệnh 1)
Chạy câu lệnh sau với từ khóa UNIQUE DIMENSION
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month IN (10, 11, 12)
MODEL
    PARTITION BY (prd_type_id)
    DIMENSION BY (year, month)
    MEASURES (amount sales_amount)
     UNIQUE DIMENSION
    RULES
        (sales_amount [2003, 12] =
                (sales_amount[2003, 10] + sales_amount[2003, 11]) / 2)
ORDER BY prd_type_id, year, month;
(Câu lệnh 2)
Sau khi thực thi câu lệnh 2, chúng ta nhận được lỗi trả về như sau:
ORA-32638: Non unique addressing in MODEL dimensions

Lý do: trong bảng all_sales có 2 dòng có cùng thông tin về (year,month,prd_type_id,emp_id) có giá trị là (2003,12,2,21).

Cách sửa: đảm bảo tập kết quả của câu lệnh SELECT ban đầu (câu SELECT không bao gồm mệnh đề MODEL) không có chứa dữ liệu trùng. Cụ thể câu lệnh như sau:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month IN (10, 11)
MODEL
    PARTITION BY (prd_type_id)
    DIMENSION BY (year, month)
    MEASURES (amount sales_amount)
     UNIQUE DIMENSION
    RULES
        (sales_amount [2003, 12] =
                (sales_amount[2003, 10] + sales_amount[2003, 11]) / 2)
ORDER BY prd_type_id, year, month;
(Câu lệnh 3)
Kết quả:
(Hình 1)
So sánh câu lệnh 2 và câu lệnh 3, chúng ta thấy rằng câu lệnh 3 mệnh đề WHERE không chứa tháng 12/2003 (dòng gây dữ liệu trùng).

Chú ý: Từ khóa UNIQUE DIMENSION là từ khóa mặc định được sử dụng nếu như chúng ta không khai báo trong mệnh đề MODEL.

2. Cách sử dụng với từ khoá UNIQUE SINGLE REFERENCE
Chúng ta chạy lại câu lệnh 2 và 3 nhưng thay từ khóa UNIQUE DIMENSION bằng UNIQUE SINGLE REFERENCE.
Câu lệnh:
 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
 WHERE month IN (10, 11, 12)
MODEL UNIQUE SINGLE REFERENCE
    PARTITION BY (prd_type_id)
    DIMENSION BY (year, month)
    MEASURES (amount sales_amount)
        (sales_amount [2003, 12] =
                (sales_amount[2003, 10] + sales_amount[2003, 11]) / 2)
ORDER BY prd_type_id, year, month;
(Câu lệnh 4)
Kết quả:
(Hình 2)
Câu lệnh:
 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
 WHERE month IN (10, 11)
MODEL UNIQUE SINGLE REFERENCE
    PARTITION BY (prd_type_id)
    DIMENSION BY (year, month)
    MEASURES (amount sales_amount)
        (sales_amount [2003, 12] =
                (sales_amount[2003, 10] + sales_amount[2003, 11]) / 2)
ORDER BY prd_type_id, year, month;
(Câu lệnh 5)
Kết quả:
(Hình 3)
Chúng ta thấy rằng khi sử dụng với từ khóa UNIQUE SINGLE REFERENCE thì ở cả câu lệnh 4 và 5 đều trả về kết quả (không báo lỗi ở câu lệnh này). So sánh hình 2 và hình 3 chúng ta thấy số lượng dòng của hình 2 nhiều hơn hình 3 do có dòng 6, 7 được lặp lại. Việc lặp lại này là do tập dữ liệu ban đầu (tập dữ liệu câu SELECT chưa có mệnh đề MODEL) có chứa 2 dòng trùng nên khi biểu thức tính toán sẽ thực hiện tính toán luôn cho 2 dòng này và trả kết quả về.

Chúng ta xét thêm ví dụ sau:
Câu lệnh:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month IN (12)
MODEL UNIQUE SINGLE REFERENCE
    PARTITION BY (prd_type_id)
    DIMENSION BY (year, month)
    MEASURES (amount sales_amount)
        (sales_amount [2004, 1] = sales_amount[2003, 12] * 1.1)
ORDER BY prd_type_id, year, month;
(Câu lệnh 6)
Sau khi thực thi câu lệnh 6, chúng ta nhận được lỗi trả về như sau:
ORA-32638: Non unique addressing in MODEL dimensions

Lý do: trong bảng all_sales có 2 dòng chứa dữ liệu của tháng 12/2003 và biểu thức tính toán cho tháng 01/2004 có liên quan đến tháng 12/2003 này. Oracle sẽ không biết lấy dòng nào trong 2 dòng trùng nhau để tính toán nên sẽ báo lỗi.

3. Các kết luận
Với từ khóa UNIQUE DIMENSION
  • Oracle sẽ kiểm tra trùng tập dữ liệu của câu lệnh SELECT, nếu có thì Oracle sẽ báo lỗi. 
  • Dòng trùng ở đây được hiểu là sự kết hợp của các cột được khai báo trong mệnh đề PARTITION BY và DIMENSION BY.
Với từ khóa UNIQUE SINGLE REFERENCE
  • Oracle sẽ bỏ qua việc kiểm tra trùng tập dữ liệu của câu lệnh SELECT.
  • Oracle chỉ kiểm tra dữ liệu trùng của mệnh đề bên phải biểu thức tính toán, nếu có thì Oracle sẽ báo lỗi. 
  • Do bỏ qua việc kiểm tra trùng dữ liệu của tập SELECT nên hiệu năng với tập dữ liệu lớn khi sử dụng với từ khóa UNIQUE SINGLE REFERENCE sẽ nhanh hơn khi dùng với từ khóa UNIQUE DIMENSION.
4. Case Study khác
Xét câu lệnh sau (điều kiện là chưa chạy câu lệnh 1)
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
SELECT year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month IN (10,11,12)
MODEL
    PARTITION BY (year)
    DIMENSION BY (month)
    MEASURES (amount sales_amount)
     [UNIQUE DIMENSION | UNIQUE SINGLE REFERENCE]
    RULES
        (sales_amount [ANY] = sales_amount[CV()] * 1.1)
ORDER BY year, month;
(Câu lệnh 7)
Câu lệnh này khi sử dụng 1 trong 2 từ khóa đều báo lỗi
ORA-32638: Non unique addressing in MODEL dimensions

Lý do: dữ liệu trùng ở đây đều vi phạm ở cả dữ liệu câu SELECT trước khi áp mệnh đề MODEL lẫn trùng ở cả biểu thức tính toán trong mệnh đề MODEL.

Cách sửa: như đã nói ở phần trên, bộ các cột trong mệnh đề PARTITION BY và DIMENSION BY phải là duy nhất. Do vậy ở đây chúng ta thêm vào 1 cột để đảm bảo bộ các cột này là duy nhất. Câu lệnh được sửa lại như sau:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
SELECT year,
       month,
       sales_amount
  FROM all_sales a
 WHERE month IN (10,11,12)
MODEL
    PARTITION BY (year, rownum rn)
    DIMENSION BY (month)
    MEASURES (amount sales_amount)
     [UNIQUE DIMENSION | UNIQUE SINGLE REFERENCE]
    RULES
        (sales_amount [ANY] = sales_amount[CV()] * 1.1)
ORDER BY year, month;
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