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;
|
