Trang chủ » Lập trình » Một số kỹ năng làm việc với Oracle

Một số kỹ năng làm việc với Oracle

1942 Lượt xem
Bài viết này sẽ tổng hợp các tips, hướng dẫn những kỹ năng cơ bản và nâng cao khi làm việc với cơ sở dữ liệu Oracle. Các kiến thức trong bài là nội dung tóm tắt được trình bày thông qua các bài toán thực tế, để tìm hiểu sâu hơn thì người đọc cần tham khảo qua Internet hoặc trong một số tài liệu về Oracle.
 
1. Tối ưu hóa câu lệnh SQL
2. Cấu trúc dữ liệu hình cây
3. Lấy ra n giá trị theo sắp xếp
4. Sử dụng database link
5. Sử dụng lệnh Merge
6. Schema trigger
7. Xóa dữ liệu trùng nhau
8. Thuộc tính rowdependencies của bảng
9. Sử dụng select table as timestamp
10. Thực hiện export database
11. Thực hiện import database
12. Tìm kiếm bản ghi bị lock và kill session lock bản ghi
13. Một số bảng hệ thống hay sử dụng
14. Toán tử: some, any, all
15. Sử dụng bảng tạm để lưu trữ dữ liệu (TEMPORARY TABLE)
16. Dùng ký tự loại trừ trong Oracle với toán tử like
17. Đánh partition tự động
18. Drop scheduler đang chạy
19. Mã hóa thủ tục, function trong database
20. Commit trong hàm khi viết package

1. Tối ưu hóa câu lệnh SQL

Tối ưu hóa câu lệnh SQL



– Tối ưu hóa câu lệnh truy vấn với bảng đánh partition và index

Vấn đề: Đối với những bảng có dữ liệu lớn, khi tạo bảng người thiết kế phải thực hiện một số kỹ thuật để tăng tốc độ truy vấn dữ liệu như đánh index, partition. Khi truy vấn người lập trình phải tuân thủ một số nguyên tắc để đảm bảo tối ưu hóa thời gian thực hiện câu lệnh:

Không nên sử dụng hàm đối với trường đánh index, partition:

— khong nen su dung
SELECT *
  FROM users
 WHERE UPPER (user_name) = UPPER ('USER_NAME');

Không nên sử dụng sử dụng toán tử not like, not in, <> với trường đánh index, partition

— khong nen su dung
SELECT *
  FROM users
 WHERE user_name NOT LIKE 'USER_NAME%';
— khong nen su dung
SELECT *
  FROM users
 WHERE user_name NOT IN ('USER_NAME1', 'USER_NAME2', 'USER_NAME3');
— khong nen su dung
SELECT *
  FROM users
 WHERE user_name <> 'USER_NAME1';

Không nên sử dụng like ‘%str’

— khong nen su dung
SELECT *
  FROM users
 WHERE user_name LIKE '%USER_NAME';

Lưu ý: Các trường hợp trên không nên sử dụng do làm mất tác dụng của cột đánh index

– Khi nào dùng IN, khi nào dùng EXISTS

Vấn đề: Trong một số trường hợp dữ liệu lớn, khi cần sử dụng toán tử IN hoặc Exists trong câu lệnh có thể tốc độ thực thi câu lệnh khác nhau tương đối lớn. Để quyết định dùng toán tử nào ta dựa vào đánh giá sau:
Bản chất câu lệnh khi dùng toán tử IN

SELECT *
  FROM t1
 WHERE x IN (SELECT y
               FROM t2);
–Dien giai (tuong duong voi cau lenh sau)
SELECT *
  FROM t1,
       (SELECT DISTINCT y
                   FROM t2) t2
 WHERE t1.x = t2.y;

Câu lệnh này quét dữ liệu full bảng T2 khi thực hiện câu lệnh SELECT DISTINCT y FROM t2; nếu bảng dữ liệu T2 lớn thì thời gian thực hiện câu lệnh tương đối chậm

Bản chất câu lệnh dùng Exists

for x in ( select * from t1 )
loop
   if ( exists ( select null from t2 where y = x.x ))
   then
       OUTPUT THE RECORD
   end if
end loop;

Câu lệnh này quét dữ liệu full bảng T1, trường hợp bảng T2 đánh index theo trường y thì tốc độ truy vấn dữ liệu bảng T2 tương đối nhanh
 

  • Dùng IN khi: Dữ liệu bảng T1 lớn hơn dữ liệu ở bảng T2
  • Dùng Exists khi: Dữ liệu bảng T2 lớn hơn dữ liệu bảng T1, trường điều kiện được đánh index
  • Nếu dữ liệu cả 2 bảng đều lớn, hoặc tương đương nhau thì 2 cách dùng có thời gian thực hiện tương đương nhau

2. Cấu trúc dữ liệu hình cây

Cấu trúc dữ liệu hình cây



Vấn đề: Bảng dữ liệu được thiết kế theo quan hệ cha – con, xây dựng cấu trúc hình cây dữ liệu, lấy ra cấp của mỗi nút trên cây



Giải pháp: Sử dụng cấu trúc start with, connect by để lấy ra thông tin



Cấu trúc lệnh:

SELECT     object_code, LPAD (' ', LEVEL * 5, ' ') || object_name,
           CONNECT_BY_ROOT (object_code)
      FROM objects
     WHERE app_id = 2609
START WITH parent_id IS NULL
CONNECT BY PRIOR object_id = parent_id;

Trong đó:
 

  • START WITH parent_id IS NULL: Điều kiện xác định nút gồc, có thể có nhiều nút gốc trong trường hợp có nhiều cây
  • CONNECT BY PRIOR object_id = parent_id: hướng phát triển của cây, đây là cách xác định nút con, vế có prior là nút cha đã được xác định, vế còn lại là nút con.
  • CONNECT_BY_ROOT(column_name) lấy ra giá trị trường của nút gốc
  • Level: Lấy ra cấp của nút, bắt đầu từ 1
  • Hàm LPAD(str1, length, str2): So sánh độ dài xâu str1 với length, nếu nhó hơn bao nhiêu ký tự thì thêm bếnh nhiêu ký tự của xâu str2 vào bên trái xâu str1

3. Lấy ra n giá trị theo sắp xếp

Lấy ra n giá trị theo sắp xếp



Bài toán: Cho bảng SINHVIEN với 2 trường (MA_SV, DIEM), lấy ra 10 sinh viên có số điểm cao nhất



Giải pháp:



– Cách 1: sử dụng thuộc tính rownum khi để giới hạn số lượng bản ghi lấy ra từ tập các bản ghi đã sắp xếp tăng dần (hoặc giảm dầm).

Câu lệnh

SELECT *
  FROM (SELECT   *
            FROM sinhvien
        ORDER BY diem DESC)
 WHERE ROWNUM <= 10

Nhận xét: Với cách sử dụng này ta lấy ra được đúng 10 bản ghi đứng đầu khi sắp xếp, trường hợp có 20 sinh viên dẫn đầu có điểm bằng nhau thì chỉ lấy được 10 sinh viên
Lưu ý: Câu lệnh sau lấy ra 10 bản ghi nhưng không phải bản ghi có điểm lớn nhất, do thuộc tính rownum được gán trước khi sắp xếp

SELECT   *
    FROM sinhvien
   WHERE ROWNUM <= 10
ORDER BY diem DESC;

– Cách 2: Sử dụng hàm rank() over để đưa ra thứ tự của bản ghi

SELECT   *
    FROM (SELECT ma_sv, RANK () OVER (ORDER BY diem DESC) AS rank_diem
            FROM sinhvien)
   WHERE rank_diem <= 10
ORDER BY rank_diem;

Nhận xét: Câu lệnh này lấy ra danh sách sinh viên thuộc top 10, có nghĩa là có thể có nhiều hơn 10 bản ghi nếu có nhiều sinh viên trùng điểm nhau. Điểm của sinh viên được sắp xếp là gán số thứ tự, các sinh viên có điểm giống nhau thì có số thứ tự giống nhau.

4. Sử dụng Database Link

Sử dụng Database Link



– Vấn đề: Để truy vấn dữ liệu từ một DB khác ta thông qua đối tượng Database link



– Câu lệnh tạo Database link:

CREATE SHARED DATABASE LINK dbl_test
CONNECT TO bccs_anypay_app IDENTIFIED BY abc
AUTHENTICATED BY bccs_anypay_app IDENTIFIED BY abc
USING '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.3.65)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = dbtest)
    )
  )
';

– Câu lệnh sử dụng Database link:

SELECT *
  FROM <schema_name>.<table_name>@dbl_test;

5. Sử dụng lệnh Merge

Sử dụng lệnh Merge



Vấn đề: Khi cần insert hoặc update dữ liệu từ bảng A sang bảng B có cấu trúc tương tự nhau mà không cần kiểm tra bản ghi đó đã tồn tại hay chưa tao dùng câu lệnh sau:

MERGE INTO test1 a
 USING test2 b
 ON (a.object_id = b.object_id)
 WHEN MATCHED THEN
    UPDATE
       SET a.status = b.status
 WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status)

Giải thích: Bảng test1, test2 có cấu trúc như nhau, câu lệnh trên dùng để đẩy dữ liệu từ bảng test2 sang bảng test1, nếu bản ghi ở bảng test2 đã tồn tại ở test1 thì thực hiện update trường status, ngược lại insert bản ghi mới vào bảng test1

6. Schema Trigger

Schema Trigger



– Vấn đề: Khi trong code có sử dụng các câu lệnh SQL thuần, tên bảng không đặt tên schema ở đầu. Khi triển khai lên server, user dùng kết nối vào DB không phải là user chứa các bảng dữ liệu



– Giải pháp: Tạm thời để chạy được các câu truy vấn với các bảng của một schema khác mà không có tiền tố schema ở đầu ta tạo trigger set schema mặc định cho user sau khi đăng nhập = schema chứa bảng dữ liệu



– Câu lệnh thực hiện:

CREATE OR REPLACE TRIGGER db_anypay_app_logon
 AFTER   LOGON  ON DATABASE
WHEN (USER = 'BCCS_ANYPAY' )
BEGIN
    execute immediate 'ALTER SESSION SET CURRENT_SCHEMA = ANYPAY_OWNER';
END;

7. Xóa dữ liệu trùng nhau

Xóa dữ liệu trùng nhau



– Vấn đề: Khi bảng dữ liệu không có khóa chính có 2 hoặc nhiều bản ghi trùng nhau, nếu cần xóa 1 bản ghi trong 2 hoặc nhiều bản ghi để tránh trùng lặp dữ liệu.



– Giái pháp: Để xóa 1 trong 2 bản ghi trùng nhau ta xác định các bản ghi bị lặp sau đó lấy ra 1 bản ghi dựa vào thuộc tính ROWID



– Câu lệnh thực hiện:

DELETE FROM users
      WHERE ROWID IN (SELECT row_id
                        FROM (SELECT   MAX (ROWID) AS row_id, COUNT (*),
                                       user_id
                                  FROM users
                              GROUP BY user_id
                                HAVING COUNT (*) > 1))

– Lưu ý: Nếu số bản ghi trùng nhau > 2 thì phải chạy nhiều lần để xóa hết

8. Thuộc tính rowdependencies của bảng

Thuộc tính rowdependencies của bảng



– Vấn đề: Với một bảng dữ liệu, khi cần biết thời gian sau cùng tác động lên bản ghi dữ liệu



– Giải pháp: Dựa vào thuộc tính ora_rowscn và sử dụng hàm scn_to_timestamp để chuyển về kiểu date



– Ví dụ:

SELECT scn_to_timestamp (ORA_ROWSCN)
  FROM staff;

– Để thông tin thuộc tính ora_rowscn với mỗi bản ghi khác nhau, khi tạo bảng ta phải đặt thuộc tính rowdependencies cho bảng, ví dụ

CREATE TABLE tbl_temp
(
    ID NUMBER(10),
    user_name VARCHAR (20),
    passwd VARCHAR (100)
)
ROWDEPENDENCIES;

9. Sử dụng select table as timestamp

Sử dụng select table as timestamp



– Vấn đề: Khi ta chẳng may xóa mất dữ liệu hoặc update 1 bản ghi và commit dữ liệu, ta cần xem lại hoặc khôi phục lại bản ghi đó



– Giải pháp: Select bảng tại thời điểm trước khi xóa hoặc update dữ liệu bằng lệnh ten_bang as of timestamp thoi_gian



– Câu lệnh:

— xoa du lieu
DELETE FROM staff
      WHERE staff_id = 172143;
— commit du lieu
COMMIT ;
  
— hien thi lai du lieu da xoa trong vong 1 ngay
SELECT *
  FROM staff AS OF TIMESTAMP SYSDATE – 1
 WHERE staff_id = 172143;
 — Khoi phuc lai du lieu da xoa
  
INSERT INTO staff
   SELECT *
     FROM staff AS OF TIMESTAMP SYSDATE – 1
    WHERE staff_id = 172143;
  
    — commit du lieu
COMMIT;

10. Thực hiện export database

– Điều kiện: Máy tính phải cài oracle_client, trong file ts_name phải có thông tin DB cần dump



Bước 1: gõ lệnh exp

Bước 2: nhập user_name/password@ts_name

Trong đó:

 

  • User_name: user đăng nhập DB
  • Password: mật khẩu đăng nhập
  • Ts_name: tên Database trong file ts_name



Bước 3: Nhập số byte buffer khi dump DB, nếu để mặc định là 4096 thì nhấn Enter

Bước 4: Nhập đường dẫn lưu file dmp trên máy local, sau khi dump sẽ sinh ra file duôi dmp và lưu trên máy

Bước 5: Lựa chọn import cả schema hay chỉ một số bảng, nếu chọn export một số bảng thì phải nhập lần lượt từng bảng

Bước 6: Lựa chọn có export dữ liệu trong bảng hay không, nếu chọn no thì chỉ export được cấu trúc của bảng

Bước 7: Chọn nén dữ liệu khi export hay không? mặc định chọn yes

Bước 8: Nhập tên bảng cần export

Bước 9: Nhập tên bảng tiếp theo cần export

Bước 10: Nhấn Enter để bắt đầu quá trình export dữ liệu

11. Thực hiện import database

Thực hiện import database



Bước 1: Gõ lệnh imp

Bước 2: Nhập thông tin kết nối vào Database

Bước 3: Nhập đường dẫn chứa file dump

Bước 4: Nhập số by dùng cho bộ nhớ đệm, thường để mặc định

Bước 5: Lựa chọn liệt kê nội dung của file import

Bước 6: Tùy chọn bỏ qua việc import nếu gặp lỗi tạo đối tượng đã có

Bước 7: Import quyền của các user đối với các đối tượng import

Bước 8: Tùy chọn import dữ liệu của bảng

Bước 9: Tùy chọn import toàn bộ file hay chỉ import theo schema

Bước 10: Nếu bước 9 chọn No thì phải nhập schema cần import

Bước 11: Tùy chọn import một số bảng hay là tất cả các bảng trong schema

12. Tìm kiếm bản ghi bị lock và kill session lock bản ghi

Tìm kiếm bản ghi bị lock và kill session lock bản ghi



– Vấn đề: Khi thực hiện thao tác với dữ liệu của bảng, một số trường hợp bản ghi bị session lock nhưng không có thao tác commit hay rollback nên không được giải phóng. Với các bản ghi này người dùng không thể thực hiện update hay delete được



– Giải pháp: Tìm kiếm ra session đang lock bản ghi để thực hiện kill session đó



– Câu lệnh:

SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status,
       b.osuser, b.machine,
          'alter system kill session '''
       || b.SID
       || ','
       || b.serial#
       || ''' IMMEDIATE;' AS kill_sta
  FROM v$locked_object a, v$session b, dba_objects c
 WHERE b.SID = a.session_id AND a.object_id = c.object_id;

– Câu lệnh kill session
ALTER SYSTEM KILL SESSION 'sid,serial#';

Trong đó: SID, Serial#: được lấy từ bảng v$session

13. Một số bảng hệ thống hay sử dụng

Một số bảng hệ thống hay sử dụng



– Bảng v$sql: Lưu các câu lệnh tác động vào Database

– Bảng v$session: Lưu các session hiện tại đang kết nối vào DB

– Bảng v$locked_object: Lưu các đối được đang bị lock do một số thao tác cập nhật cấu trúc, dữ liệu

– V$SPPARAMETER: Bảng lưu tham số cấu hình của hệ thống

14. Toán tử: some, any, all

Toán tử: some, any, all



– Toán tử all: Được sử dụng để so sánh giá trị của một trường với một danh sách giá trị, toán tử all đi sau =, !=, >, <, <=, >=.



– Bài toán: Cho bảng sinh_vien với các trường ma_sv, lop, diem. Tìm tất cả các sinh viên của lớp A có điểm lớn hơn bất kỳ sinh viên nào của lớp B



– Câu lệnh như sau:

SELECT *
  FROM sinh_vien
 WHERE lop = 'A' AND diem > ALL (SELECT diem
                                   FROM sinh_vien
                                  WHERE lop = 'B');

– Giải thích: khi dùng toán tử all thì giá trị của cột diem phải > bất kỳ giá trị nào trong tập được chỉ ra sau all

– Toán tử any, some: trong oracle 2 toán tử này có ý nghĩa như nhau:

Bài toán: với bảng sinh_vien như trên, lấy ra danh sách các sinh viên lớp A có điểm lớn hơn 1 trong các sinh viên của lớp B

– Câu lệnh:

SELECT *
  FROM sinh_vien
 WHERE lop = 'A' AND diem > ANY (SELECT diem
                                   FROM sinh_vien
                                  WHERE lop = 'B');
SELECT *
  FROM sinh_vien
 WHERE lop = 'A' AND diem > SOME (SELECT diem
                                   FROM sinh_vien
                                  WHERE lop = 'B');

15. Sử dụng bảng tạm để lưu trữ dữ liệu (TEMPORARY TABLE)

Sử dụng bảng tạm để lưu trữ dữ liệu (TEMPORARY TABLE)



– Vấn đề: Trong một số trường hợp cần tổng hợp dữ liệu để xuất báo cáo hoặc đưa dữ liệu hiển thị ra giao diện. Khi ta không thể dùng 1 câu lệnh để lấy ra dữ liệu mà tổng hợp qua nhiều bước thì ta cần lưu lại dữ liệu tổng hợp sau đó lấy dữ liệu



– Giải pháp: Trong oracle hỗ trợ việc tạo một bảng tạm (temporary table) để lưu trữ dữ liệu tạm thời với các đặc điểm:

 

  • Thao tác: Có thể sử dụng các câu Query, DML để thao tác với Temporary Table giống như một table bình thường.
  • Lưu trữ: Temporary table sử dụng Temporary tablespace để lưu dữ liệu.
  • Ảnh hưởng: Dữ liệu chỉ có hiệu lực trong phạm vi một Session hoặc một Transaction, việc quy định bảng nằm trong câu lệnh tạo bảng:

— Du lieu chi ton tai trong 1 transationCREATE GLOBAL TEMPORARY TABLE my_temp_table (

  column1  NUMBER,
  column2  NUMBER
) ON COMMIT DELETE ROWS;
  
–du lieu ton tai trong session
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  column1  NUMBER,
  column2  NUMBER
) ON COMMIT PRESERVE ROWS;
 

  • Bảo mật: Cho dù dữ liệu đã commit, mỗi session cũng chỉ thấy được dữ liệu trong session đó
  • Xung đột: Do mỗi session có phần dữ liệu riêng nên tránh được việc xung đột dữ liệu, ví dụ như việc ghi đè dữ liệu của nhau là không xảy ra.
  • Dọn rác: Temporary table tự động dọn sạch dữ liệu khi kết thúc Session hoặc Transaction.

– Ngoài ra còn có một số đặc điểm khác như:
 

  • Có thể tạo trigger, index trên temporary table.
  • Khi dùng lệnh truncate (DDL), chỉ có dữ liệu trên session hiện tại bị mất.
  • Khi export, phần cấu trúc của Temporary table cũng được export theo như table thường.

16. Dùng ký tự loại trừ trong Oracle với toán tử like

Dùng ký tự loại trừ trong Oracle với toán tử like



– Vấn đề: Khi sử dụng toán tử like để tìm kiếm xâu gần đúng ta thường dùng 2 ký tự thay thế là ‘%’(thay thế cho 1 chuối) và ‘_’ (thay thế cho 1 ký tự). Trường hợp ta muốn tìm kiếm theo các trường có chứa ký tự này ta phải dùng ký tự loại trừ



– Giải quyết: Trong Oracle để dùng ký tự loại trừ sau toán tử like ta dùng toán tử escape, cú pháp như sau:

select * from test1 where col1 like '\%\_' escape '\';

– Trong đó: ‘\’ là ký tự loại trừ, ta có thể thay thế ký tự ‘\’ bằng 1 ký tự bất kỳ ngoài ‘%’, ‘_’. Khi sử dụng ký tự loại trừ ta làm mất ý nghĩa của ký tự thay thế đứng ngay sau nó, nó trở thành 1 ký tự thường

17. Đánh partition tự động

– Vấn đề: Đối với các bảng dữ liệu có đánh partition theo tháng hoặc theo ngày, người QTHT thường xuyên phải đánh partition, nếu quên dữ liệu insert vào sẽ bị lỗi và có thể dẫn đến lỗi toàn hệ thống.

– Giải pháp: Để giải quyết vấn đề trên ta thường viết thủ tục đánh partition tự động cho bảng và đặt scheduler để chạy định kỳ. Việc đánh partition không ảnh hưởng tới các thao tác insert, select, update, delete.

– Tư tưởng: Để đảm bảo có thể đánh partition một cách tự động ta phải đặt tên partition theo một số quy ước:
Trong tên partition phải chứa thông tin về ngày tháng của dữ liệu
Phần đầu của tên partition phải giống nhau
– Dựa vào thông tin partition trong tên ta xác định thời gian cần đánh partition tiếp theo

PROCEDURE proc_create_partition
IS
   CURSOR c_partition
   IS
      SELECT   object_name,
               MAX (SUBSTR (subobject_name,
                            LENGTH (subobject_name) – 5,
                            LENGTH (subobject_name)
                           )
                   ) AS sub_partition
          FROM user_objects
         WHERE object_type = 'TABLE PARTITION'
           AND object_name NOT LIKE 'BIN$%'
           AND object_name NOT IN ('AGENT_COUNTER')
      GROUP BY object_name;
  
   v_loop_str   VARCHAR (3000);
   v_date       DATE;
   p_error      VARCHAR (2000);
BEGIN
   FOR v_partition IN c_partition
   LOOP
      v_date := TO_DATE (v_partition.sub_partition, 'yyMMdd');
  
      WHILE v_date <=
                ADD_MONTHS (TO_DATE (v_partition.sub_partition, 'yyMMdd'), 1)
      LOOP
         BEGIN
            v_date := v_date + 1;
            v_loop_str := '';
            v_loop_str :=
                  'alter table '
               || v_partition.object_name
               || ' add partition DATA20'
               || TO_CHAR (v_date, 'yyMMdd')
               || ' values less than (to_date(''20'
               || TO_CHAR (v_date, 'yyMMdd')
               || ''',''yyyyMMdd''))';
            DBMS_OUTPUT.put_line (v_loop_str);
  
            EXECUTE IMMEDIATE v_loop_str;
  
            v_loop_str := '';
         EXCEPTION
            WHEN OTHERS
            THEN
               p_error := 'Loi xay ra khi them partition: ' || SQLERRM;
               DBMS_OUTPUT.put_line (p_error);
         END;
      END LOOP;
   END LOOP;
END;