Thứ Tư, 31 tháng 3, 2021

Global Temporary Tables trong Oracle Database

Các ứng dụng thường sử dụng một số dạng lưu trữ dữ liệu tạm thời cho các quy trình phức tạp để hoàn thành trong một lần xử lý. Từ Oracle Database 8i đã hỗ trợ Global Temporary Tables (GTT).

Nội dung:
  • Temporary Tables
  • Creation of Global Temporary Tables
  • Global Temporary Tables and Undo
  • Global Temporary Tables and Redo
  • Miscellaneous Features
  • Private Temporary Tables (18c+)

Temporary Tables

  • Global Temporary Tables : Từ Oracle 8i  và chủ đề của bài viết này.
  • Private Temporary Tables : Có sẵn kể từ Oracle 18c. Thảo luận sau

Tạo Global Temporary Tables

Từ khóa ON COMMIT DELETE ROWS  chỉ ra dữ liệu cần xóa vào cuối của giao dịch, hoặc khi kết thúc phiên
CREATE GLOBAL TEMPORARY TABLE my_temp_table ( id NUMBER, description VARCHAR2(20) ) ON COMMIT DELETE ROWS; -- Insert, but don't commit, then check contents of GTT. INSERT INTO my_temp_table VALUES (1, 'ONE'); SELECT COUNT(*) FROM my_temp_table; COUNT(*) ---------- 1 SQL> -- Commit and check contents. COMMIT; SELECT COUNT(*) FROM my_temp_table; COUNT(*) ---------- 0 SQL>
Ngược lại, mệnh đề ON COMMIT PRESERVE ROWS  chỉ ra rằng các row sẽ tồn tại sau khi kết thúc giao dịch, chỉ bị xóa vào cuối phiên
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT PRESERVE ROWS;

-- Insert và commit, sau đó kiểm tra nội dung của GTT.
INSERT INTO my_temp_table VALUES (1, 'ONE'); COMMIT; SELECT COUNT(*) FROM my_temp_table; COUNT(*) ---------- 1 SQL> -- Kết nối lại và kiểm tra nội dung của GTT.
CONN test/test SELECT COUNT(*) FROM my_temp_table; COUNT(*) ---------- 0 SQL>

Global Temporary Tables và Undo

Bảng Global Temporary Tables vẫn sinh ra undo data bình thường:
DROP TABLE my_temp_table PURGE;

-- Tạo bảng thông thường.
CREATE TABLE my_temp_table ( id NUMBER, description VARCHAR2(20) ); -- Insert dữ liệu INSERT INTO my_temp_table WITH data AS ( SELECT 1 AS id FROM dual CONNECT BY level < 10000 ) SELECT rownum, TO_CHAR(rownum) FROM data a, data b WHERE rownum <= 1000000; -- Kiểm tra undo được sử dụng bởi giao dịch.
SELECT t.used_ublk, t.used_urec FROM v$transaction t, v$session s WHERE s.saddr = t.ses_addr AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID'); USED_UBLK USED_UREC ---------- ---------- 302 6237 SQL>
Bây giờ  lặp lại thử nghiệm trước đó, nhưng lần này là sử dụng GTT.
DROP TABLE my_temp_table PURGE;

-- Tạo GTT.
CREATE GLOBAL TEMPORARY TABLE my_temp_table ( id NUMBER, description VARCHAR2(20) ) ON COMMIT PRESERVE ROWS; -- Insert dữ liệu vào GTT. INSERT INTO my_temp_table WITH data AS ( SELECT 1 AS id FROM dual CONNECT BY level < 10000 ) SELECT rownum, TO_CHAR(rownum) FROM data a, data b WHERE rownum <= 1000000; -- Kiểm tra undo được sử dụng bởi giao dịch
SELECT t.used_ublk, t.used_urec FROM v$transaction t, v$session s WHERE s.saddr = t.ses_addr AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID'); USED_UBLK USED_UREC ---------- ---------- 303 6238 SQL> TRUNCATE TABLE my_temp_table;
Trong 12c có chức năng Temporary Undo cho phép undo cho một GTT được ghi vào vùng temporary tablespace, do đó giảm việc undo và redo.

Global Temporary Tables và Redo

Dữ liệu bảng Global Temporary Tables vẫn được ghi vào temporary tablespace, không ghi trực tiếp vào redo (giảm lượng redo sinh ra), nhưng không phải loại bỏ toàn bộ redo.

DROP TABLE my_temp_table PURGE;

-- Tạo conventional table.
CREATE TABLE my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
);

SET AUTOTRACE ON STATISTICS;

-- Insert table.
INSERT INTO my_temp_table
WITH data AS (
  SELECT 1 AS id
  FROM   dual
  CONNECT BY level < 10000
)
SELECT rownum, TO_CHAR(rownum)
FROM   data a, data b
WHERE  rownum <= 1000000;

1000000 rows created.

Statistics
----------------------------------------------------------
        106  recursive calls
      20119  db block gets
       2603  consistent gets
         16  physical reads
   23039396  redo size
        853  bytes sent via SQL*Net to client
        987  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL>
Bây giờ lặp lại thử nghiệm trước đó, nhưng lần này là sử dụng GTT.
DROP TABLE my_temp_table PURGE;

-- Create GTT.
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT PRESERVE ROWS;

SET AUTOTRACE ON STATISTICS;

-- Populate GTT.
INSERT INTO my_temp_table
WITH data AS (
  SELECT 1 AS id
  FROM   dual
  CONNECT BY level < 10000
)
SELECT rownum, TO_CHAR(rownum)
FROM   data a, data b
WHERE  rownum <= 1000000;

1000000 rows created.

Statistics
----------------------------------------------------------
         45  recursive calls
      15333  db block gets
       2381  consistent gets
         16  physical reads
    2944180  redo size
        862  bytes sent via SQL*Net to client
        987  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL>

TRUNCATE TABLE my_temp_table;

Các tính năng khác

  • Nếu câu lệnh TRUNCATE được đưa ra dựa trên một bảng tạm thời, thì chỉ dữ liệu cụ thể của phiên mới bị truncate. Không có ảnh hưởng đến dữ liệu của các phiên khác.
  • Dữ liệu trong các bảng tạm thời được lưu trữ trong các phân đoạn tạm thời trong temp tablespace.
  • Dữ liệu trong các bảng tạm thời sẽ tự động bị xóa vào cuối phiên cơ sở dữ liệu, ngay cả khi nó kết thúc bất thường.
  • Index có thể được tạo trên các bảng tạm thời. Nội dung của index và phạm vi của chỉ mục giống như phiên cơ sở dữ liệu.
  • View  có thể được tạo dựa trên các bảng tạm thời và kết hợp các bảng tạm thời và vĩnh viễn.
  • Các bảng tạm thời có thể có các ttrigger được liên kết với chúng.
  • Tiện ích export và import có thể được sử dụng để chuyển các định nghĩa bảng, nhưng không có row dữ liệu nào được xử lý.
  • Thống kê (statistics) trên các bảng tạm thời là chung cho tất cả các phiên. Oracle 12c cho phép thống kê từng phiên cụ thể.
  • Có một số hạn chế liên quan đến bảng tạm thời nhưng dựa vào phiên bản cụ thể
Hy vọng hữu ích cho bạn.
=============================
* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
hoặc
https://bit.ly/oaz_fp
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile: 0902912888
⚡️ Skype: tranbinh48ca
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: http://bit.ly/ytb_binhoraclemaster
👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhoracle
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội

=============================
học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,khóa học pl/sql, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle dataguard, oracle goldengate, oracle weblogic, oracle exadata, hoc solaris, hoc linux, hoc aix

ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master