臨時表

来源:https://www.cnblogs.com/outmanxiaozhou/archive/2018/12/11/10101564.html
-Advertisement-
Play Games

臨時表概念 臨時表就是用來暫時保存臨時數據（亦或叫中間數據）的一個資料庫對象，它和普通表有些類似，然而又有很大區別。它只能存儲在臨時表空間，而非用戶的表空間。ORACLE臨時表是會話或事務級別的，只對當前會話或事務可見。每個會話只能查看和修改自己的數據。 臨時表語法 臨時表分類 ORACLE臨時表有 ...


臨時表概念

   臨時表就是用來暫時保存臨時數據（亦或叫中間數據）的一個資料庫對象，它和普通表有些類似，然而又有很大區別。它只能存儲在臨時表空間，而非用戶的表空間。ORACLE臨時表是會話或事務級別的，只對當前會話或事務可見。每個會話只能查看和修改自己的數據。

 

臨時表語法

 

clip_image002

 

 

臨時表分類

 

ORACLE臨時表有兩種類型：會話級的臨時表和事務級的臨時表。

1）ON COMMIT DELETE ROWS

它是臨時表的預設參數，表示臨時表中的數據僅在事物過程（Transaction）中有效，當事物提交（COMMIT）後，臨時表的暫時段將被自動截斷（TRUNCATE），但是臨時表的結構 以及元數據還存儲在用戶的數據字典中。如果臨時表完成它的使命後，最好刪除臨時表，否則資料庫會殘留很多臨時表的表結構和元數據。

2）ON COMMIT PRESERVE ROWS

它表示臨時表的內容可以跨事物而存在，不過，當該會話結束時，臨時表的暫時段將隨著會話的結束而被丟棄，臨時表中的數據自然也就隨之丟棄。但是臨時表的結構以及元數據還存儲在用戶的數據字典中。如果臨時表完成它的使命後，最好刪除臨時表，否則資料庫會殘留很多臨時表的表結構和元數據。

1:會話級的臨時表的數據和你當前會話有關係，當前SESSION不退出的情況下，臨時表中的數據就還存在，臨時表的數據只有當你退出當前SESSION的時候才被截斷（TRUNCATE TABLE），如下所示：

會話級別的臨時表創建：

複製代碼 
CREATE GLOBAL TEMPORARY TABLE TMP_TEST

(

　　　　ID NUMBER ,

　　　　NAME VARCHAR2(32)

) ON COMMIT PRESERVE ROWS;

或

CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT PRESERVE ROWS

AS

SELECT * FROM TEST;

操作示例：

SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST

 (

　　 ID NUMBER ,

 　　NAME VARCHAR2(32)

 ) ON COMMIT PRESERVE ROWS;

Table created

SQL> INSERT INTO TMP_TEST

    SELECT 1, 'kerry' FROM DUAL;

1 row inserted

SQL> COMMIT;

Commit complete

SQL> SELECT * FROM TMP_TEST;

ID 　　　　　　　　NAME

---------- ----------------

1 　　　　　　　　kerry

SQL> INSERT INTO TMP_TEST

 　　SELECT 2, 'rouce' FROM DUAL;

1 row inserted

SQL> ROLLBACK;

Rollback complete

SQL> SELECT * FROM TMP_TEST;

ID 　　　　　　　　　　NAME

---------- ----------------------

1 　　　　　　　　　　kerry

SQL>
複製代碼

 

2:事務級的臨時表（預設），這種類型的臨時表與事務有關，當進行事務提交或者事務回滾的時候，臨時表的數據將自行截斷，即當COMMIT或ROLLBACK時，數據就會被TRUNCATE掉，其它的特性和會話級的臨時表一致。

事務級臨時表的創建方法：

複製代碼 
CREATE GLOBAL TEMPORARY TABLE TMP_TEST

(

　　　　ID NUMBER ,

　　　　NAME VARCHAR2(32)

) ON COMMIT DELETE ROWS;

或

CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT DELETE AS SELECT * FROM TEST;

SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST
(
 　　　　ID NUMBER ,
 　　　　NAME VARCHAR2(32) 
 ) ON COMMIT DELETE ROWS;

Table created

SQL> INSERT INTO TMP_TEST

 　　SELECT 1, 'kerry' FROM DUAL;

1 row inserted

SQL> SELECT * FROM TMP_TEST;

ID 　　　　　　　　　　NAME

---------- ----------------------

1 　　　　　　　　　　kerry

SQL> COMMIT;

Commit complete

SQL> SELECT * FROM TMP_TEST;

ID 　　　　　　　　　　  NAME

---------- ------------------------

SQL>
複製代碼

 

3:關於臨時表只對當前會話或事務可見。每個會話只能查看和修改自己的數據。

用DM用戶登錄資料庫，打開SESSION 1後，創建臨時表TMP_TEST

 

複製代碼 
CREATE GLOBAL TEMPORARY TABLE TMP_TEST

(

　　　　ID NUMBER ,

　　　　NAME VARCHAR2(32)

) ON COMMIT DELETE ROWS;

或

CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT DELETE AS SELECT * FROM TEST;

SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST

 (

 　　　　ID NUMBER ,

 　　　　NAME VARCHAR2(32)

 ) ON COMMIT DELETE ROWS;

Table created

SQL> INSERT INTO TMP_TEST

　　 SELECT 1, 'kerry' FROM DUAL;

1 row inserted

SQL> SELECT * FROM TMP_TEST;

ID 　　　　　　　　　　NAME

---------- ---------------------

1 kerry

SQL> COMMIT;

Commit complete

SQL> SELECT * FROM TMP_TEST;

ID 　　　　　　　　　　NAME

---------- -----------------------

SQL>
複製代碼

 

 

用sys用戶登錄資料庫，打開SESSION 2

SELECT * FROM DBA_TABLES WHERE TABLE_NAME='TMP_TEST' -- 可以查到臨時表數據

SELECT * FROM DM.TMP_TEST; -- 查不到數據，即使TMP_TEST臨時表存在數據。

 

臨時表與永久表區別

複製代碼 
SQL> SELECT TABLE_NAME, TABLESPACE_NAME,"LOGGING",

　　  "TEMPORARY", DURATION, "MONITORING"

　　 FROM DBA_TABLES WHERE TABLE_NAME IN ('TMP_TEST', 'TEST') ;

TABLE_NAME   TABLESPACE_NAME LOGGING TEMPORARY DURATION    MONITORING

------------- --------------  ------- --------- ----------- ---------

TEST          TBS_EDS_DATA    YES       N                      YES

TMP_TEST                      NO        Y     SYS$SESSION       NO
複製代碼

 

如上所示，臨時表是存儲在臨時表空間裡面的，但是上面腳本可以看出，臨時表在數據字典中沒有指定其表空間，臨時表是NOLOGGING，DURATION為SYS$SESSION

臨時表的DML操作速度比較快，但同樣也是要產生 Redo Log ，只是同樣的DML語句，比對 PERMANENT 的DML 產生的Redo Log 少其實在應用中，往往會創建一個NOLOGGING的永久表（中間表）來保存中間數據，從而代替臨時表，至於這這兩者有啥優劣，真是很難說清道明(歡迎大家探討)。

 

臨時表用途

 

什麼時候使用臨時表？用臨時表和用中間表有啥區別呢？

我覺得是在需要的時候應用，下麵是David Dai關於臨時表的一個應用說明，我覺得非常形象的說明瞭臨時表的應用場景：對於一個電子商務類網站，不同消費者在網站上購物，就是一個獨立的 SESSION，選購商品放進購物車中，最後將購物車中的商品進行結算。也就是說，必須在整個SESSION期間保存購物車中的信息。同時，還存在有些消費者，往往最終結賬時放棄購買商品。如果，直接將消費者選購信息存放在最終表（PERMANENT）中，必然對最終表造成非常大的壓力。因此，對於這種案例，就可以採用創建臨時表(ON COMMIT PRESERVE ROWS)的方法來解決。數據只在 SESSION 期間有效，對於結算成功的有效數據，轉移到最終表中後，ORACLE自動TRUNCATE 臨時數據；對於放棄結算的數據，ORACLE 同樣自動進行 TRUNCATE ，而無須編碼控制，並且最終表只處理有效訂單，減輕了頻繁的DML操作的壓力。

1：當處理某一批臨時數據，需要多次DML操作時（插入、更新等），建議使用臨時表。

2：當某些表在查詢裡面，需要多次用來做連接時。（為了獲取目標數據需要關聯A、B、C， 同時為了獲取另外一個目標數據，需要關聯D、B、C....）

關於臨時表和中間表（NOLOGGING，保存中間數據，使用完後刪除）那個更適合用來存儲中間數據，我個人更傾向於使用臨時表，而不建議使用中間表。

 

註意事項

1 ) 不支持 lob 對象，這也許是設計者基於運行效率的考慮，但實際應用中確實需要此功能時就無法使用臨時表了。這點網上很多資料都這麼說，我沒有追查到底是那個版本不支持lob對象，至少在ORACLE 10g這個版本中，臨時表是支持lob對象的.

SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST

2 (

3 ID NUMBER ,

4 NAME CLOB

5 ) ON COMMIT PRESERVE ROWS;

Table created

SQL>

SQL> INSERT INTO TMP_TEST

2 SELECT 1, 'ADF' FROM DUAL;

1 row inserted

SQL> SELECT * FROM V$VERSION;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

2 ) 不支持主外鍵關係

3 ）臨時表不能永久的保存數據。

4 ）臨時表的數據不會備份，恢復，對其的修改也不會有任何日誌信息

5 ）臨時表不會有DML 鎖

DML locks are not acquired on the data of the temporary tables. The LOCK statement has no effect on a temporary table, because each session has its own private data.

6 ）儘管對臨時表的DML操作速度比較快，但同樣也是要產生 Redo Log ，只是同樣的DML語句，比對 PERMANENT 的DML 產生的Redo Log 少。請見官方文檔：

DML statements on temporary tables do not generate redo logs for the data changes.However, undo logs for the data and redo logs for the undo logs are generated. Data from the temporary table is automatically dropped in the case of session termination,either when the user logs off or when the session terminates abnormally such as during a session or instance failure.

7 ） 臨時表可以創建臨時的索引、視圖、觸發器。

8 ) 如果要DROP會話級別臨時表，並且其中包含數據時，必須先截斷其中的數據。否則會報錯。

SQL> DROP TABLE TMP_TEST PURGE;

DROP TABLE TMP_TEST PURGE

ORA-14452: 試圖創建, 更改或刪除正在使用的臨時表中的索引

SQL> TRUNCATE TABLE TMP_TEST;

Table truncated

SQL> DROP TABLE TMP_TEST PURGE;

Table dropped


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Cortext-A7_i.MX 6ULL——多模式DDR控制器（MMDC）
    1、概述 i.MX 6ULL系列晶元的MMDC是一個多模式DDR控制器，支持DDR3/DDR3Lx16和LPDDR2x16的存儲類型，MMDC是可配置，高性能，優化的記憶體控制器。 註：DDR3/DDR3Lx16、LPDDR2x16 ，此處的x16表示晶元位寬，每個傳輸周期能夠提供的數據量（bit）。 ...
  • SceneKit下關於修改SCNNode 的Shader展示自定義圖形
    由於某些需求，需要在蘋果OS x系統下展示一組點雲，準備使用蘋果官方的三維顯示控制項來完成這一功能。場景點雲作為離散的點， 如果每個點以SCNnode的形式加入場景中，則回造成過大的記憶體消耗，筆者電腦下，單個場景展示到1w點時記憶體就崩潰了。所以準備通過 修改單個node的shader屬性，實現自定義的 ...
  • Golang學習：sublime text3配置golang環境
    最近導師讓學習golang, 然後我就找了些有關golang的學習視頻和網站。 昨天在電腦上下載了go tools, 之後在sublime上配置了golang的運行環境。By the way, 我的電腦是windows的操作系統。 Golang學習資料： 學習視頻：https://www.cours ...
  • mysql5.6 配置 文件
    mysql 3306 主庫配置文件 [client]port = 3306default-character-set=utf8mb4socket = /ssd/mysql/3306/tmp/mysql.sock# Here follows entries for some specific prog ...
  • 2018阿裡雲雙十二年終鉅惠，雲伺服器ECS優惠低至2折，還有返利
    硝煙剛剛散去，馬上又將迎來雙十二了。自從雙十一火了之後，逐漸的雙十二也演變成為了一個全民狂歡的購物節日。我們都知道阿裡雲在雙十一推出了拼團的優惠活動，那麼在接下來的2018年雙十二又會給到我們一些什麼樣的優惠呢?下麵阿裡雲官方雲大使伺服器吧小編就帶大家來看看。 阿裡雲在12月7日正式上線了2018年 ...
  • redis 系列21 複製Replication (上)
    一. 概述 使用和配置主從複製非常簡單，每次當 slave 和 master 之間的連接斷開時， slave 會自動重連到 master 上，並且無論這期間 master 發生了什麼， slave 都將嘗試讓自身成為 master 的精確副本。這個系統的運行依靠三個主要的機制： (1) 當一個 ma ...
  • 大數據-Hadoop生態(16)-MapReduce框架原理-自定義FileInputFormat
    1. 需求 將多個小文件合併成一個SequenceFile文件（SequenceFile文件是Hadoop用來存儲二進位形式的key-value對的文件格式），SequenceFile裡面存儲著多個文件，存儲的形式為文件路徑+名稱為key，文件內容為value 三個小文件 one.txt two.t ...
  • 一次單核CPU占用過高問題的處理
    客戶現場反饋，top的檢查結果中，一個CPU的占用一直是100%。實際上現場有4個CPU，而且這個伺服器是mysql專屬伺服器。 我的第一反應是io_thread一類的參數設置有問題，檢查以後發現read和write的thread設置都是4，這和CPU數一致，因此可以斷定這並不是單顆CPU占用過高的 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類