Oracle Tech Talk

All about Oracle Technologies

Saturday, 4 July 2015

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

13:47 Posted by NTP , No comments
Bài viết này sẽ giới thiệu cách sử dụng mệnh đề MODEL.

1. Giới thiệu:
Mệnh đề MODEL được Oracle giới thiệu từ phiên bản Oracle 10g. Mệnh đề này cho phép chúng ta thực hiện việc tính toán ngay từ bên trong câu lệnh. Việc tính toán này tương tự như chúng ta thực hiện tính toán trong Excel vậy, nghĩa là chúng ta có thể tham chiếu đến 1 cột bất kỳ của 1 hàng bất kỳ để lấy giá trị ô đó thực hiện việc tính toán.

2. Tạo môi trường test:
Chúng ta tải script tạo bảng và dữ liệu mẫu tại đây

3. Giải quyết yêu cầu:
Chúng ta xem thử dữ liệu mẫu chúng ta đang có ở đây:

1
2
3
4
5
6
SELECT a.year,
       a.month,
       a.prd_type_id,
       a.emp_id,
       a.amount
  FROM all_sales a;
(Câu lệnh 1)
Kết quả câu lệnh:
YEAR
MONTH
PRD_TYPE_ID
EMP_ID
AMOUNT
2003
1
1
21
10034.84
2003
1
2
21
1034.84
2003
2
1
21
15144.65
2003
2
2
21
1544.65
2003
3
1
21
20137.83
2003
3
2
21
2037.83
2003
4
1
21
25057.45
2003
4
2
21
2557.45
2003
5
1
21
17214.56
2003
5
2
21
1714.56
2003
6
1
21
15564.64
2003
6
2
21
1564.64
2003
7
1
21
12654.84
2003
7
2
21
1264.84
2003
8
1
21
17434.82
2003
8
2
21
1734.82
2003
9
1
21
19854.57
2003
9
2
21
1854.57
2003
10
1
21
21754.19
2003
10
2
21
2754.19
2003
11
1
21
13029.73
2003
11
2
21
1329.73
2003
12
1
21
10034.84
2003
12
2
21
1034.84
(Bảng kết quả 1)

Với bảng kết quả trên, giả sử chúng ta có các yêu cầu sau:

 Yêu cầu 1: Cho biết doanh số của năm 2013 (như bảng kết quả 1) và doanh số dự kiến của 3 tháng cuối năm 2014 bằng 1.5 lần doanh số 3 tháng tương ứng của năm 2013
- Chúng ta có thể viết câu lệnh sau để thực hiện yêu cầu:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
SELECT a.year,
       a.month,
       a.prd_type_id,
       a.amount
  FROM all_sales a
UNION ALL
SELECT 2014,
       a.month,
       a.prd_type_id,
       a.amount * 1.5
  FROM all_sales a
 WHERE month IN (10, 11, 12);
(Câu lệnh 2)

- Chúng ta cũng có thể viết cách khác bằng cách sử dụng mệnh đề MODEL. Cách viết 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
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
        (sales_amount [10, 2004] = sales_amount[10, 2003] * 1.5,
        sales_amount [11, 2004] = sales_amount[11, 2003] * 1.5,
        sales_amount [12, 2004] = sales_amount[12, 2003] * 1.5)
ORDER BY prd_type_id, year, month;
(Câu lệnh 3)

Chúng ta thấy rằng cả 2 cách viết đều trả về cùng 1 kết quả.

Yêu cầu 2: Cho biết doanh số của năm 2013 (như bảng kết quả 1) và doanh số dự kiến của:
   + Tháng 10/2014 gấp 1.5 lần tháng 10/2013
   + Tháng 11/2014 gấp 2 lần tháng 11/2013
   + Tháng 12/2014 bằng tháng 10/2014 + tháng 11/2014
Chúng ta thấy với yêu cầu phức tạp như vậy với cách viết số 1 sẽ rất mất thời gian, khó thực hiện và đôi khi sẽ tốn Performance của hệ thống nhưng với cách viết số 2 thì ta có thể thực hiện dễ dàng như sau:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
SELECT prd_type_id,
       year,
       month,
       sales_amount
  FROM all_sales a
MODEL
    PARTITION BY (prd_type_id)
    DIMENSION BY (month, year)
    MEASURES (amount sales_amount)
        (sales_amount [10, 2004] =
                sales_amount[10, 2003] * 1.5,
        sales_amount [11, 2004] =
                sales_amount[11, 2003] * 2,
        sales_amount [12, 2004] =
                sales_amount[10, 2004] + sales_amount[11, 2004])
ORDER BY prd_type_id, year, month;
(Câu lệnh 4)

4. Các mệnh đề con được dùng trong mệnh đề MODEL

(Hình 1)

- Sau khi thực thi 1 câu lệnh SQL, Oracle sẽ trả về 1 tập kết quả (Result Set). Tập kết quả này sẽ được lưu vào vùng nhớ và cho phép mệnh để MODEL phân chia tập này ra thành mảng nhiều chiều. Như hình trên ta thấy rằng khối lập phương ở giữa có thể được cắt theo chiều ngang, chiều dọc hay cắt thành từng lớp. Với mảng nhiều chiều này, mệnh đề MODEL cho phép chúng ta định nghĩa các luật (RULE) để thực hiện việc tính toán trên đó.
- Chúng ta khảo sát cú pháp đơn giản của mệnh để MODEL:
1
2
3
4
5
6
MODEL
  [PARTITION BY (<cols>)]
  DIMENSION BY (<cols>)
  MEASURES (<cols>)
  [RULES]
  (<rule>, <rule>,.., <rule>)

Trong đó:
  • PARTITION BY: dùng để chia tập kết quả thành nhiều phân vùng. 
  • DIMENSION BY: dùng để chia nhỏ từng vùng thành các chiều dữ liệu khác nhau.
  • MEASURES: dùng để tính toán giá trị trong từng chiều dữ liệu.
  • RULES: quy luật / cách tính toán 
Chúng ta lấy ví dụ 1 tập tin Excel để hiểu rõ hơn các khái niệm này:
  • PARTITION: tương ứng với từng sheet trong 1 workbook (workbook là tập kết quả trả về) 
  • DIMENSION: tương ứng với các cột (column) hoặc các dòng (row) trong 1 sheet
  • MEASURES: tương ứng với từng ô trong 1 sheet
  • RULES: tương ứng với việc dùng hàm để tính toán các giá trị trong các ô bất kỳ
(Hình 2)
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

Saturday, 6 June 2015

Thiết lập 1 Job đơn giản bằng SQL Navigator

14:13 Posted by NTP No comments
Bài viết này giới thiệu cách tạo 1 Job chạy tự động trên Oracle bằng SQL Navigator

Bước 1: tạo 1 thủ tục đơn giản
1
2
3
4
5
6
7
8
CREATE OR REPLACE PROCEDURE pr_demo_job
IS
    v_date   DATE;
BEGIN
    SELECT SYSDATE INTO v_date FROM DUAL;
    INSERT INTO job VALUES(v_date);
    COMMIT;
END;
(Câu lệnh 1)

Bước 2: tạo 1 bảng test để kiểm tra kết quả khi Job chạy
1
2
CREATE TABLE job
    (run_job_datetime DATE);
(Câu lệnh 2)

 Bước 3: khai báo 1 tiến trình chạy tự động bằng SQL Navigator theo các bước:
(Hình 1: Công cụ Job Scheduler)

(Hình 2: Công cụ Job Manager)

(Hình 3: Tạo New Job)

(Hình 4: Khai báo lịch chạy định kỳ cho Job)

(Hình 5: Khai báo phần xử lý cho Job)

(Hình 6: Kiểm tra Job đã được khai báo thành công và chạy thử)

Bước 4: kiểm tra kết quả của Job bằng cách query bảng JOB
1
 SELECT * FROM job;
(Câu lệnh 3)

Saturday, 9 May 2015

Quản lý vấn đề lưu trữ khi xây dựng 1 hệ thống (Phần 2)

13:47 Posted by NTP , No comments
Bài viết này nêu 1 Case cụ thể trong việc dựng 1 CSDL mới và quản lý dung lượng của CDSL này bằng các script chạy tự động.
Trình tự các bước thực hiện trong Case này như sau:
1. Tạo 1 schema mới
2. Tạo thêm các tablespace dùng để test: Tablespace Data dành cho các Partitioned Table, Tablespace Index dành cho các Partitioned Index
3. Tạo các script quản lý dung lượng gồm: script thêm Datafile cho các Tablespace khi dung lượng tới hạn, script thêm Tablespace cho các tháng, script thêm Partition Table theo ngày
4. Testcase cho các trường hợp
Chi tiết các bước thực hiện:

1. Tạo 1 schema mới như sau:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
CREATE TABLESPACE orcl_data
DATAFILE 'C:\Oracle\oradata\orcl\orcl_owner\ORCL_DATA.dbf'
SIZE 10M
AUTOEXTEND ON
NEXT 10M;
/

CREATE TABLESPACE orcl_idx
DATAFILE 'C:\Oracle\oradata\orcl\orcl_owner\ORCL_IDX.dbf'
SIZE 10M
AUTOEXTEND ON
NEXT 10M;
/

CREATE TEMPORARY TABLESPACE orcl_tmp
TEMPFILE 'C:\Oracle\oradata\orcl\orcl_owner\ORCL_TMP.dbf'
SIZE 200M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
/

CREATE USER orcl_owner
IDENTIFIED BY orclo
DEFAULT TABLESPACE orcl_data
TEMPORARY TABLESPACE orcl_tmp
/
GRANT UNLIMITED TABLESPACE TO orcl_owner
/
GRANT CONNECT TO orcl_owner
/
GRANT DBA TO orcl_owner
/
GRANT RESOURCE TO orcl_owner
/
GRANT SELECT ANY DICTIONARY TO orcl_owner
/
ALTER USER orcl_owner DEFAULT ROLE ALL
 2. Tạo thêm các tablespace dùng để test: Tablespace Data dành cho các Partitioned Table, Tablespace Index dành cho các Partitioned Index

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
CREATE TABLESPACE orcl_data_201407
DATAFILE 'C:\Oracle\oradata\orcl\orcl_owner\orcl_data_201407_1.dbf'
SIZE 5M
AUTOEXTEND ON
NEXT 10M
MAXSIZE 50M;
/

CREATE TABLESPACE orcl_idx_201407
DATAFILE 'C:\Oracle\oradata\orcl\orcl_owner\orcl_idx_201407_2.dbf'
SIZE 5M
AUTOEXTEND ON
NEXT 10M
MAXSIZE 50M;
3. Tạo các script quản lý dung lượng gồm: script thêm Datafile cho các Tablespace khi dung lượng tới hạn, script thêm Tablespace cho các tháng, script thêm Partition Table tất cả các ngày trong tháng
3.1 Script thêm Datafile
Tham số của script:

  • p_percent_free: nếu dung lượng của Tablespace dưới ngưỡng này thì thủ tục sẽ thêm 1 Datafile mới cho Tablespace
  • p_oradata_home: đường dẫn chứa Datafile
  • p_init_size: kích thước khởi tạo cho Datafile
  • p_extend_size: kích thước mở rộng mỗi lần cho Datafile
  • p_maxsize: kích thước tối đa cho Datafile
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
CREATE OR REPLACE PROCEDURE pr_add_datafile (p_percent_free   IN NUMBER,
                                              p_oradata_home   IN VARCHAR2,
                                              p_init_size      IN VARCHAR2,
                                              p_extend_size    IN VARCHAR2,
                                              p_maxsize        IN VARCHAR2)
IS
    CURSOR c_tablespace
    IS
          SELECT df.tablespace_name
            FROM dba_free_space fs,
                 (  SELECT tablespace_name, SUM (bytes) bytes
                      FROM dba_data_files
                     WHERE tablespace_name LIKE 'ORCL%'
                  GROUP BY tablespace_name) df
           WHERE fs.tablespace_name(+) = df.tablespace_name
        GROUP BY df.tablespace_name, df.bytes
          HAVING NVL (ROUND (SUM (fs.bytes) * 100 / df.bytes), 1) <
                     p_percent_free;

    v_count          NUMBER;
    v_sql            VARCHAR2 (3000);
BEGIN
    FOR r_tablespace IN c_tablespace
    LOOP
        v_sql :=
               'ALTER TABLESPACE '
            || r_tablespace.tablespace_name
            || ' ADD DATAFILE '''
            || p_oradata_home
            || r_tablespace.tablespace_name
            || '_'
            || seq_datafile.NEXTVAL
            || '.DBF'' SIZE '
            || p_init_size
            || ' REUSE AUTOEXTEND ON NEXT '
            || p_extend_size
            || ' MAXSIZE '
            || p_maxsize;
        DBMS_OUTPUT.put_line (v_sql);

        EXECUTE IMMEDIATE v_sql;
    END LOOP;
EXCEPTION
    WHEN OTHERS
    THEN
        raise_application_error (-20080, 'Add Datafile: ' || SQLERRM);
END;
Script trên có sử dụng sequence trong lúc tạo Datafile. Đây là đoạn script tạo sequence: 
1
CREATE SEQUENCE seq_datafile START WITH 10 INCREMENT BY 1 MAXVALUE 999999999;
3.2 Script tạo Tablespace hàng tháng
Tham số của script:

  • p_list_tablespace: danh sách các tablespace cần tạo
  • p_oradata_home: đường dẫn chứa Datafile
  • p_init_size: kích thước khởi tạo cho Datafile
  • p_extend_size: kích thước mở rộng mỗi lần cho Datafile
  • p_maxsize: kích thước tối đa cho Datafile
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
CREATE OR REPLACE PROCEDURE pr_create_monthly_tablespace (
    p_list_tablespace   IN VARCHAR2,
    p_oradata_home      IN VARCHAR2,
    p_init_size         IN VARCHAR2,
    p_extend_size       IN VARCHAR2,
    p_maxsize           IN VARCHAR2)
IS
    v_sql            VARCHAR2 (3000);
    v_date_pattern   VARCHAR2 (7) := '_yyyymm';

    CURSOR c_tablespace
    IS
        SELECT TRIM (COLUMN_VALUE) || TO_CHAR (SYSDATE, v_date_pattern)
                   tablespace_name
          FROM XMLTABLE (
                   ('"' || REPLACE (p_list_tablespace, ',', '","') || '"'))
        UNION ALL
        SELECT    TRIM (COLUMN_VALUE)
               || TO_CHAR (ADD_MONTHS (SYSDATE, 1), v_date_pattern)
                   tablespace_name
          FROM XMLTABLE (
                   ('"' || REPLACE (p_list_tablespace, ',', '","') || '"'))
        MINUS
        SELECT tablespace_name FROM dba_tablespaces;
BEGIN
    FOR r_tablespace IN c_tablespace
    LOOP
        v_sql :=
               'CREATE TABLESPACE '
            || r_tablespace.tablespace_name
            || ' DATAFILE '''
            || p_oradata_home
            || r_tablespace.tablespace_name
            || '_'
            || seq_datafile.NEXTVAL
            || '.DBF'' SIZE '
            || p_init_size
            || ' REUSE AUTOEXTEND ON NEXT '
            || p_extend_size
            || ' MAXSIZE '
            || p_maxsize;
        DBMS_OUTPUT.put_line (v_sql);

        EXECUTE IMMEDIATE v_sql;
    END LOOP;
EXCEPTION
    WHEN OTHERS
    THEN
        raise_application_error (-20080,
                                 'Create Monthly Tablespace: ' || SQLERRM);
END;
 3.3 Script tạo Partition tất cả các ngày trong tháng cho tất cả các Partitioned Table 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
CREATE OR REPLACE PROCEDURE pr_add_monthly_partition
IS
    v_sql            VARCHAR2 (3000);
    v_date_pattern   VARCHAR2 (8) := 'yyyymmdd';

    CURSOR c_tab_partitions
    IS
        SELECT DISTINCT table_name
          FROM dba_tab_partitions
         WHERE table_owner = 'ORCL_OWNER' AND table_name NOT LIKE 'BIN$%';

BEGIN
    FOR r_tab_partitions IN c_tab_partitions
    LOOP
        DBMS_OUTPUT.put_line (
            'Add partition for table: ' || r_tab_partitions.table_name);

        FOR r_partition
            IN (SELECT TO_CHAR (
                             TO_DATE (SUBSTR (partition_name, 6),
                                      v_date_pattern)
                           + 1,
                           v_date_pattern)
                           upper_bound,
                       a.*
                  FROM (    SELECT    'DATA_'
                                   || TO_CHAR (
                                            TRUNC (ADD_MONTHS (SYSDATE, 1),
                                                   'MONTH')
                                          + LEVEL
                                          - 1,
                                          v_date_pattern)
                                       partition_name,
                                      'ORCL_DATA_'
                                   || TO_CHAR (
                                          TRUNC (ADD_MONTHS (SYSDATE, 1),
                                                 'MONTH'),
                                          'yyyymm')
                                       tablespace_name
                              FROM DUAL
                        CONNECT BY LEVEL <=
                                       (SELECT TO_NUMBER (
                                                   TO_CHAR (
                                                       LAST_DAY (
                                                           ADD_MONTHS (
                                                               SYSDATE,
                                                               1)),
                                                       'dd'))
                                          FROM DUAL)
                        MINUS
                        SELECT partition_name, tablespace_name
                          FROM dba_tab_partitions
                         WHERE table_name = r_tab_partitions.table_name) a)
        LOOP
            v_sql :=
                   'ALTER TABLE '
                || r_tab_partitions.table_name
                || ' ADD PARTITION '
                || r_partition.partition_name
                || ' VALUES LESS THAN (TO_DATE('''
                || r_partition.upper_bound
                || ''','''
                || v_date_pattern
                || ''')) TABLESPACE orcl_data_'
                || TO_CHAR (ADD_MONTHS (SYSDATE, 1), 'yyyymm');

            DBMS_OUTPUT.put_line (v_sql);

            EXECUTE IMMEDIATE v_sql;
        END LOOP;
    END LOOP;
EXCEPTION
    WHEN OTHERS
    THEN
        raise_application_error (-20080,
                                 'Create Monthly Partition: ' || SQLERRM);
END;
4. Testcase cho các trường hợp
4.1 Tạo 1 Partitioned Table

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
CREATE TABLE big_table
(
    id            NUMBER NOT NULL,
    bt_date       DATE NOT NULL,
    bt_comments   VARCHAR2 (100)
)
PARTITION BY RANGE
    (bt_date)
    (
        PARTITION
            data_20140701
            VALUES LESS THAN (TO_DATE ('02/07/2014', 'DD/MM/YYYY'))
            TABLESPACE orcl_data_201407);
* Lưu ý: dữ liệu của bảng này ta lưu vào tablespace "orcl_data_201407" để dễ quản lý. Ta có thể tạo thêm các partition theo ngày cho đủ các partition trong tháng 7.
 4.2 Tạo Index cho bảng này

1
2
3
4
CREATE INDEX big_table_idx
    ON big_table (id, bt_date)
    LOCAL
    TABLESPACE orcl_idx_201407;
* Lưu ý: index của bảng này ta lưu vào tablespace "orcl_idx_201407" để dễ quản lý
Trong ví dụ này ta sử dụng Local Index

4.3 Kiểm tra dung lượng của các Tablespace bằng script sau

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
SELECT                                                       
        df.tablespace_name "Tablespace",
         df.bytes / (1024 * 1024) "Size (MB)",
         SUM (fs.bytes) / (1024 * 1024) "Free (MB)",
         NVL (ROUND (SUM (fs.bytes) * 100 / df.bytes), 1) "% Free",
         ROUND ( (df.bytes - SUM (fs.bytes)) * 100 / df.bytes) "% Used"
    FROM dba_free_space fs,
         (  SELECT tablespace_name, SUM (bytes) bytes
              FROM dba_data_files
             WHERE tablespace_name LIKE 'ORCL%'
          GROUP BY tablespace_name) df
   WHERE fs.tablespace_name(+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes;
4.4 Insert dữ liệu vào bảng "big_table"

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
DECLARE
    v_create_date   DATE := TO_DATE ('01/07/2014', 'dd/mm/yyyy');
    v_rows          NUMBER := 1000000;
BEGIN
    FOR i IN 1 .. v_rows
    LOOP
        INSERT INTO big_table (id, bt_date, bt_comments)
             VALUES (i, v_create_date, 'This is some data for ' || i);
    END LOOP;

    COMMIT;
EXCEPTION
    WHEN OTHERS
    THEN
        raise_application_error (-20080, 'Insert data: ' || SQLERRM);
        ROLLBACK;
END;
4.5 Ta dùng script ở bước 4.3 để kiểm tra dung lượng của tablespace "orcl_data_201407" và "orcl_idx_201407" đã tới ngưỡng gần đầy chưa (ngưỡng tạo ban đầu có maxsize=50M). Nếu chưa tới ta có thể chạy lại script insert dữ liệu ở bước 4.4 cho các ngày tiếp theo và sau đó lại dùng script 4.3 để kiểm tra. Cứ lặp lại như vậy cho đến khi dung lượng tablespace chứa dữ liệu gần tới hạn.
Lúc này ta sẽ dùng script 3.1 để thêm Datafile cho các tablespace bị thiếu.
Các script 3.2 và 3.3 sẽ được dùng để tạo cho các tháng tiếp theo
Cuối cùng để cho các script 3.1, 3.2, 3.3 làm việc 1 cách tự động để giám sát và quản lý dung lượng ta có thể đặt lịch cho các script này chạy tự động hàng ngày (script 3.1) và hàng tháng (script 3.2 và 3.3) thông qua tiến trình Schedule của Oracle hoặc dùng 1 ứng dụng khác để gọi các script này 1 cách định kỳ.

Phụ lục: script mẫu gọi các thủ tục 3.1, 3.2 và 3.3

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- Add Datafile

DECLARE
    free          NUMBER := 30;
    ora_home      VARCHAR2 (3000) := 'C:\ORACLE\ORADATA\ORCL\ORCL_OWNER\';
    init_size     VARCHAR2 (10) := '5M';
    extend_size   VARCHAR2 (10) := '10M';
    max_size      VARCHAR2 (10) := '50M';
BEGIN
    pr_add_datafile2 (p_percent_free   => free,
                      p_oradata_home   => ora_home,
                      p_init_size      => init_size,
                      p_extend_size    => extend_size,
                      p_maxsize        => max_size);
END;

-- Add Tablespace

DECLARE
    list_tablespace   VARCHAR2 (100) := 'ORCL_DATA,ORCL_IDX';
    ora_home          VARCHAR2 (3000) := 'C:\ORACLE\ORADATA\ORCL\ORCL_OWNER\';
    init_size         VARCHAR2 (10) := '5M';
    extend_size       VARCHAR2 (10) := '10M';
    max_size          VARCHAR2 (10) := '50M';
BEGIN
    pr_create_monthly_tablespace (p_list_tablespace   => list_tablespace,
                                  p_oradata_home      => ora_home,
                                  p_init_size         => init_size,
                                  p_extend_size       => extend_size,
                                  p_maxsize           => max_size);
END;

-- Add Partitions

BEGIN
    pr_add_monthly_partition;
END;
*** Lưu ý: trong quá trình thực thi các script có thể Oracle sẽ báo thiếu quyền, ta chạy các câu lệnh sau để thêm quyền cho user:

1
2
3
4
GRANT CREATE TABLESPACE TO orcl_owner;
GRANT SELECT ON dba_tablespaces TO orcl_owner;
GRANT SELECT ON dba_data_files TO orcl_owner;
GRANT ALTER TABLESPACE TO orcl_owner;

Saturday, 2 May 2015

Quản lý vấn đề lưu trữ khi xây dựng 1 hệ thống (Phần 1)

13:30 Posted by NTP , No comments
Trong quá trình xây dựng CSDL cho 1 hệ thống ta thường hay gặp phải vấn đề về quản lý dung lượng lưu trữ khi dữ liệu hệ thống "phình" lên. Để có thể quản lý được dữ liệu này cũng như quản lý dung lượng trên thiết bị lưu trữ tốt thì khâu thiết kế lưu trữ dữ liệu ban đầu rất quan trọng.

Trước hết ta hãy khảo sát trường hợp "Dung lượng lưu trữ tăng trong khi dung lượng thiết bị lưu trữ gần tới hạn" với 2 phạm vi sau:
1. Phạm vi của yêu cầu này là không mở rộng thêm dung lượng lưu trữ (chỉ sử dụng phân vùng lưu trữ được cấp phát ban đầu):  --> Yêu cầu phải xóa bớt dữ liệu theo 1 điều kiện nào đó như từ tháng N đến tháng M và sau khi xóa PHẢI TRẢ LẠI FREE SPACE để chứa dữ liệu mới
2. Phạm vi của yêu cầu này là được mở rộng thêm dung lượng lưu trữ: --> Yêu cầu là di chuyển dữ liệu của 1 số bảng được tổ chức lưu trữ theo ngày / tháng / năm sang phân vùng mới

Với 2 trường hợp trên, ta thấy rằng để thực hiện được các yêu cầu trên thì việc thiết kế CSDL lúc ban đầu rất quan trọng nếu không khi có phát sinh những yêu cầu tương tự như trên thì việc thực hiện sẽ rất khó và chiếm nhiều thời gian của chúng ta.

Khi thiết kế CSDL, Oracle đã cho phép chúng ta thiết kế các vấn đề về lưu trữ theo các khái niệm của Oracle như: Tablespace, Datafile, Partitioned Table, Partitioned Index, ... để giúp chúng ta dễ quản lý hơn. Dưới đây là 1 vài tóm tắt ngắn gọn theo ý của tác giả bài viết cho các khái niệm trên:
1. Tablespace: tên của vùng lưu trữ các Object trong Oracle
2. Datafile: tên tập tin lưu trữ Tablespace
3. Partitioned Table: là các table lưu trữ dữ liệu được phân hoạch theo từng phân vùng (Partition) để dễ quản lý
4. Partitioned Index: là các index lưu trữ dữ liệu được phân hoạch theo từng phân vùng (Partition) để dễ quản lý

Nhìn vào hình trên ta thấy rằng kiến trúc của Oracle đã rất rõ ràng cho việc thiết kế lưu trữ và quản lý do đó khi thiết kế 1 CSDL cho ứng dụng ta cứ theo mô hình cơ bản trên thì sẽ đảm bảo được cho việc dễ dàng quản lý sau này.

Để có thể đảm bảo được việc thiết kế lúc ban đầu và phát triển giữ được mô hình thiết kế thì người phát triển phải tuân thủ một số quy định như sau:
1. Các Partitioned Table được lưu trữ vào các Tablespace dành riêng cho các Partitioned Table
2. Các Partitioned Index được lưu trữ vào các Tablespace dành riêng cho các Partitioned Index
3. Các table bình thường còn lại được lưu trữ vào Tablespace Data Dafault
4. Các index bình thường còn lại được lưu trữ vào Tablespace Index Default
Người phát triển khi phát triển cần lưu ý các vấn đề trên để khi tạo table và index cần phải xác định và chỉ rõ đối tượng đó thuộc về Tablespace nào theo cú pháp sau:

1
2
3
 
CREATE TABLE <table_name> (
<column_name>  <column_data_type>)
TABLESPACE <tablespace_name>;

Saturday, 4 April 2015

PIVOT và UNPIVOT (Phần 2)

13:24 Posted by NTP , No comments
Trong quá trình Pivot hoặc Unpivot ta có thể sẽ gặp thêm các trường hợp sau đây:
1. Pivot nhiều cột:
1
2
3
4
SELECT *
  FROM cust_sales PIVOT (SUM (sales) AS SUM, COUNT (sales) AS COUNT
                  FOR month
                  IN ('Jan' AS jan, 'Feb' AS feb, 'Mar' AS mar));
(Câu lệnh 1)
Kết quả:
 (Hình 1)

2. Xử lý giá trị NULL khi PIVOT:
Khi thực hiện PIVOT, với các ô không có giá trị Oracle sẽ trả về giá trị NULL. Để thay giá trị NULL bằng 1 giá trị khác ta dùng hàm COALESCE.
1
2
3
4
5
6
7
8
SELECT customer_id,
       product_id,
       COALESCE (jan, 0) jan,
       COALESCE (feb, 0) feb,
       COALESCE (mar, 0) mar
  FROM cust_sales PIVOT (SUM (sales)
                  FOR month
                  IN ('Jan' AS jan, 'Feb' AS feb, 'Mar' AS mar));
(Câu lệnh 2)
Kết quả:
(Hình 2)

3. Hạn chế của Pivot:
Chúng ta thấy rằng khi thực hiện Pivot thì pivot_in_clause bắt buộc chúng ta phải nhập tay các giá trị chúng ta muốn pivot. Như ví dụ trên giả sử có thêm tháng April thì chúng ta phải sửa lại mệnh đề pivot_in_clause. Vậy có cách nào để ta lấy tự động  khi có các tháng mới phát sinh không?
Oracle có hỗ trợ cho ta cách lấy động này nhưng khi đó kết quả trả về của các cột được PIVOT sẽ thể hiện trong 1 cột và có định dạng là XML.
1
2
3
4
SELECT *
  FROM cust_sales PIVOT XML (SUM (sales)
                  FOR month
                  IN (SELECT DISTINCT month FROM cust_sales));
(Câu lệnh 3)
Kết quả:
CUSTOMER_ID PRODUCT_ID MONTH_XML
Adam Prod A <PivotSet><item><column name = "MONTH">Feb</column><column name = "SUM(SALES)"></column></item><item><column name = "MONTH">Jan</column><column name = "SUM(SALES)">34</column></item><item><column name = "MONTH">Mar</column><column name = "SUM(SALES)"></column></item></PivotSet>
Adam Prod B <PivotSet><item><column name = "MONTH">Feb</column><column name = "SUM(SALES)">49</column></item><item><column name = "MONTH">Jan</column><column name = "SUM(SALES)"></column></item><item><column name = "MONTH">Mar</column><column name = "SUM(SALES)"></column></item></PivotSet>
Adam Prod C <PivotSet><item><column name = "MONTH">Feb</column><column name = "SUM(SALES)"></column></item><item><column name = "MONTH">Jan</column><column name = "SUM(SALES)"></column></item><item><column name = "MONTH">Mar</column><column name = "SUM(SALES)">134</column></item></PivotSet>
Jones Prod A <PivotSet><item><column name = "MONTH">Feb</column><column name = "SUM(SALES)">92</column></item><item><column name = "MONTH">Jan</column><column name = "SUM(SALES)"></column></item><item><column name = "MONTH">Mar</column><column name = "SUM(SALES)">34</column></item></PivotSet>
Kanes Prod A <PivotSet><item><column name = "MONTH">Feb</column><column name = "SUM(SALES)"></column></item><item><column name = "MONTH">Jan</column><column name = "SUM(SALES)"></column></item><item><column name = "MONTH">Mar</column><column name = "SUM(SALES)">39</column></item></PivotSet>
Kanes Prod B <PivotSet><item><column name = "MONTH">Feb</column><column name = "SUM(SALES)"></column></item><item><column name = "MONTH">Jan</column><column name = "SUM(SALES)">24</column></item><item><column name = "MONTH">Mar</column><column name = "SUM(SALES)"></column></item></PivotSet>
Kanes Prod C <PivotSet><item><column name = "MONTH">Feb</column><column name = "SUM(SALES)">83</column></item><item><column name = "MONTH">Jan</column><column name = "SUM(SALES)"></column></item><item><column name = "MONTH">Mar</column><column name = "SUM(SALES)"></column></item></PivotSet>
(Bảng kết quả 1)

Ta có thể thay câu lệnh SELECT DISTINCT month FROM cust_sales  bằng từ khóa ANY.
Với kết quả nhận được của cột MONTH_XML ta có thể xử lý thêm 1 phần nữa để lấy được kết quả mong muốn.

Phần tiếp theo là phần bổ sung để xử lý dữ liệu định dạng XML khi lấy được ở trên. Đây là dữ liệu XML của dòng đầu tiên:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
<PivotSet>
 <item>
  <column name = "CUSTOMER_ID">Adam</column>
  <column name = "SUM(SALES)"/>
 </item>
 <item>
  <column name = "CUSTOMER_ID">Jones</column>
  <column name = "SUM(SALES)">92</column>
 </item>
 <item>
  <column name = "CUSTOMER_ID">Kanes</column>
  <column name = "SUM(SALES)"/>
 </item>
</PivotSet>

Nhìn vào dữ liệu XML trên, ta thấy rằng có thể duyệt qua các phần tử bằng cách dùng hàm EXTRACTVALUE kết hợp với cấu trúc XPATH.
1
2
3
4
5
6
SELECT month,
       product_id,
       EXTRACTVALUE (customer_id_xml, '/PivotSet/item[1]/column[2]') person1,
       EXTRACTVALUE (customer_id_xml, '/PivotSet/item[2]/column[2]') person2,
       EXTRACTVALUE (customer_id_xml, '/PivotSet/item[3]/column[2]') person3
  FROM v_cust_sales t;
(Câu lệnh 4)
Kết quả:
(Hình 3)
Ta thấy rằng với kết quả trên thì các cột PERSON1, PERSON2 và PERSON3 vẫn không xác định được là của Customer_ID nào. Để xác định được các cột đó là của Person nào ta có thể dùng XPATH lấy column 1 và chỉ lấy 1 dòng đầu tiên (ROWNUM=1) vì các dòng còn lại đều cho kết quả như nhau.
(Hình 4)

 4. Sắp xếp kết quả trả về khi UNPIVOT:
 Ta xét ví dụ sau:
1
2
3
4
5
SELECT 
 FROM cust_sales_month UNPIVOT (yes
                        FOR month
                        IN("JANUARY", "FEBRUARY", "MARCH"))
ORDER BY customer_id, product_id;
(Câu lệnh 5)
CUSTOMER_ID PRODUCT_ID MONTH YES
Adam Prod A JANUARY 1
Adam Prod A FEBRUARY 0
Adam Prod A MARCH 0
Adam Prod B FEBRUARY 1
Adam Prod B JANUARY 0
Adam Prod B MARCH 0
Adam Prod C FEBRUARY 0
Adam Prod C JANUARY 0
Adam Prod C MARCH 1
Jones Prod A FEBRUARY 1
Jones Prod A JANUARY 0
Jones Prod A MARCH 1
Kanes Prod A MARCH 1
Kanes Prod A JANUARY 0
Kanes Prod A FEBRUARY 0
Kanes Prod B FEBRUARY 0
Kanes Prod B JANUARY 1
Kanes Prod B MARCH 0
Kanes Prod C MARCH 0
Kanes Prod C FEBRUARY 1
Kanes Prod C JANUARY 0
(Bảng kết quả 2)
Với kết quả trên ta thấy rằng cột MONTH sau khi Unpivot thì không được sắp xếp theo thứ tự từng tháng, để thực hiện việc sắp xếp cho cột MONTH ta thực hiện như sau:
1
2
3
4
5
6
7
SELECT *
 FROM cust_sales_month UNPIVOT (yes
                        FOR month
                        IN("JANUARY", "FEBRUARY", "MARCH"))
ORDER BY customer_id,
         product_id,
         DECODE (month, 'JANUARY', 1, 'FEBRUARY', 2,'MARCH', 3);
(Câu lệnh 6)
CUSTOMER_ID PRODUCT_ID MONTH YES
Adam Prod A JANUARY 1
Adam Prod A FEBRUARY 0
Adam Prod A MARCH 0
Adam Prod B JANUARY 0
Adam Prod B FEBRUARY 1
Adam Prod B MARCH 0
Adam Prod C JANUARY 0
Adam Prod C FEBRUARY 0
Adam Prod C MARCH 1
Jones Prod A JANUARY 0
Jones Prod A FEBRUARY 1
Jones Prod A MARCH 1
Kanes Prod A JANUARY 0
Kanes Prod A FEBRUARY 0
Kanes Prod A MARCH 1
Kanes Prod B JANUARY 1
Kanes Prod B FEBRUARY 0
Kanes Prod B MARCH 0
Kanes Prod C JANUARY 0
Kanes Prod C FEBRUARY 1
Kanes Prod C MARCH 0
(Bảng kết quả 3)
Các bài viết có liên quan
  1. Giới thiệu về PIVOT và UNPIVOT
  2. Các vấn đề khác liên quan đến PIVOT và UNPIVOT
  3. PIVOT với cột động (Dynamic SQL Pivot)