PL/SQL批處理語句：BULK COLLECT 和 FORALL

???PL/SQL程序中運行SQL語句是存在開銷的，因為SQL語句是要提交給SQL引擎處理，這種在PL/SQL引擎和SQL引擎之間的控制轉移叫做上下文卻換，每次卻換時，都有額外的開銷
???????請看下圖：

???????

???????但是，FORALL和BULK COLLECT可以讓PL/SQL引擎把多個上下文卻換壓縮成一個，這使得在PL/SQL中的要處理多行記錄的SQL語句執行的花費時間驟降
???????請再看下圖：

???????

BULK COLLECT 加速查詢

采用BULK COLLECT可以將查詢結果一次性地加載到collections中，而不是通過cursor一條一條地處理
?可以在select into ，fetch into ， returning into語句使用BULK COLLECT
?注意在使用BULK COLLECT時，所有的INTO變量都必須是collections

select into語句中使用bulk collect

DECLARE TYPE sallist IS TABLE OF employees.salary%TYPE;sals sallist;
BEGINSELECT salary BULK COLLECT INTO sals FROM employees where rownum<=50;--接下來使用集合中的數據
END;
/

在fetch into中使用bulk collect

DECLARETYPE deptrectab IS TABLE OF departments%ROWTYPE;dept_recs deptrectab;CURSOR cur IS SELECT department_id,department_name FROM departments where department_id>10;
BEGINOPEN cur;FETCH cur BULK COLLECT INTO dept_recs;--接下來使用集合中的數據
END;
/

returning into中使用bulk collect

CREATE TABLE emp AS SELECT * FROM employees;DECLARE TYPE numlist IS TABLE OF employees.employee_id%TYPE;enums numlist;TYPE namelist IS TABLE OF employees.last_name%TYPE;names namelist;
BEGINDELETE emp WHERE department_id=30RETURNING employee_id,last_name BULK COLLECT INTO enums,names;DBMS_OUTPUT.PUT_LINE('deleted'||SQL%ROWCOUNT||'rows:');FOR i IN enums.FIRST .. enums.LASTLOOPDBMS_OUTPUT.PUT_LINE('employee#'||enums(i)||':'||names(i));END LOOP;
END;
/deleted6rows:
employee#114:Raphaely
employee#115:Khoo
employee#116:Baida
employee#117:Tobias
employee#118:Himuro
employee#119:Colmenares

?BULK COLLECT 對大數據DELETE UPDATE的優化

DECLARE
--按rowid排序的cursor
--刪除條件是oo=xx，這個需根據實際情況來定CURSOR mycursor IS SELECT rowid FROM t WHERE OO=XX ORDER BY rowid;TYPE rowid_table_type IS TABLE OF rowid index  by  pls_integer;v_rowid rowid_table_type;
BEGINOPEN mycursor;LOOPFETCH mycursor BULK COLLECT INTO v_rowid LIMIT 5000;--5000行提交一次EXIT WHEN v_rowid.count=0;FORALL i IN v_rowid.FIRST..v_rowid.LASTDELETE t WHERE rowid=v_rowid(i);COMMIT;END LOOP;CLOSE mycursor;
END;
/

限制BULK COLLECT 提取的記錄數

語法：
?????????????FETCH cursor BULK COLLECT INTO ...[LIMIT rows]；
?????????????其中，rows可以是常量，變量或者求值的結果是整數的表達式
?????????????
?????????????假設你需要查詢并處理1W行數據，你可以用BULK COLLECT一次取出所有行，然后填充到一個非常大的集合中
?????????????可是，這種方法會消耗該會話的大量PGA，APP可能會因為PGA換頁而導致性能下降
?????????????
?????????????這時，LIMIT子句就非常有用，它可以幫助我們控制程序用多大內存來處理數據
?

DECLARECURSOR allrows_cur IS SELECT * FROM employees;TYPE employee_aat IS TABLE OF allrows_cur%ROWTYPE INDEX BY BINARY_INTEGER;v_emp employee_aat;
BEGINOPEN allrows_cur;LOOPFETCH allrows_cur BULK FETCH INTO v_emp LIMIT 100;/*通過掃描集合對數據進行處理*/FOR i IN 1 .. v_emp.countLOOPupgrade_employee_status(v_emp(i).employee_id);END LOOP;EXIT WHEN allrows_cur%NOTFOUND;END LOOP;CLOSE allrows_cur;
END;
/

?FORALL注意事項

使用FORALL時，應該遵循如下規則：

  1. FORALL語句的執行體，必須是一個單獨的DML語句，比如INSERT，UPDATE或DELETE。
  2. 不要顯式定義index_row，它被PL/SQL引擎隱式定義為PLS_INTEGER類型，并且它的作用域也僅僅是FORALL。
  3. 這個DML語句必須與一個集合的元素相關，并且使用FORALL中的index_row來索引。注意不要因為index_row導致集合下標越界。
  4. lower_bound和upper_bound之間是按照步進 1 來遞增的。
  5. 在sql_statement中，不能單獨地引用集合中的元素，只能批量地使用集合。
  6. 在sql_statement中使用的集合，下標不能使用表達式。

BULK COLLECT介紹

BULK COLLECT子句會批量檢索結果，即一次性將結果集綁定到一個集合變量中，并從SQL引擎發送到PL/SQL引擎。

通常可以在SELECT INTO、FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。下面逐一描述BULK COLLECT在這幾種情形下的用法

BULK COLLECT的注意事項

  1. BULK COLLECT INTO 的目標對象必須是集合類型。
  2. 只能在服務器端的程序中使用BULK COLLECT，如果在客戶端使用，就會產生一個不支持這個特性的錯誤。
  3. 不能對使用字符串類型作鍵的關聯數組使用BULK COLLECT子句。
  4. 復合目標(如對象類型)不能在RETURNING INTO子句中使用。
  5. 如果有多個隱式的數據類型轉換的情況存在，多重復合目標就不能在BULK COLLECT INTO子句中使用。
  6. 如果有一個隱式的數據類型轉換，復合目標的集合(如對象類型集合)就不能用于BULK COLLECTINTO子句中
 DECLARE
CURSOR cur IS
select * from NEWLOG4 t where TO_CHAR(t.autudt,'YYYY-MM-DD') = '2021-03-09'and pushstate='3';
TYPE rec IS TABLE OF NEWLOG4%ROWTYPE;
recs rec;
BEGIN
OPEN cur;
WHILE (TRUE) LOOP
FETCH cur BULK COLLECT
INTO recs LIMIT 5000;
FORALL i IN 1 .. recs.COUNT
INSERT INTO NEWLOG4_202103 VALUES recs (i);COMMIT;EXIT WHEN cur%NOTFOUND;END LOOP;CLOSE cur;END;INSERT/*+parallel(10)*/  INTO NEWLOG4_202103 select * from NEWLOG4 t where TO_CHAR(t.autudt,'YYYY-MM-DD') = '2021-03-09'and pushstate='3';delete /*+parallel(10)*/  from NEWLOG4  nologging  where TO_CHAR(autudt,'YYYY-MM-DD') = '2021-03-09'and pushstate='3';INSERT/*+parallel(10)*/  INTO NEWLOG4_202103 select * from NEWLOG4 t where TO_CHAR(t.autudt,'YYYY-MM-DD') = '2021-03-09'and pushstate='3';alter session enable parallel dml; DECLARE
CURSOR cur IS
select/*+parallel(8)*/ rowid from NEWLOG4 t where TO_CHAR(t.autudt,'YYYY-MM-DD') = '2021-03-03'and pushstate='3';
--TYPE rec IS TABLE OF NEWLOG4%ROWTYPE;
TYPE rowid_table_type IS TABLE OF rowid index  by  pls_integer;v_rowid rowid_table_type;
--recs rec;
BEGIN
OPEN cur;
WHILE (TRUE) LOOP
FETCH cur BULK COLLECT
INTO v_rowid  LIMIT 1000;
EXIT WHEN v_rowid.count=0;
FORALL i IN 1 .. v_rowid.COUNT
---delete NEWLOG4 where current of recs (i);
delete/*+parallel(8)*/ from NEWLOG4 nologging where rowid=v_rowid (i);COMMIT;EXIT WHEN cur%NOTFOUND;END LOOP;CLOSE cur;END;DECLARE
v_exists  NUMBER (10, 0);
v_exists1  NUMBER (10, 0);
--recs rec;
BEGIN
select /*+parallel(12)*/ count(1)into v_exists from NEWLOG4 t where TO_CHAR(t.autudt,'YYYY-MM-dd') = '2021-03-06'and pushstate='3';
WHILE (TRUE) LOOPdelete /*+parallel(12)*/  from NEWLOG4  nologging  where TO_CHAR(autudt,'YYYY-MM-DD') = '2021-03-06'and pushstate='3';EXIT WHEN v_exists1=v_exists+1;v_exists1:= v_exists1+1;if (v_exists=10000) thenCOMMIT;end if;END LOOP;COMMIT;END;select count(1) from NEWLOG4 t where TO_CHAR(t.autudt,'YYYY-MM-DD') = '2021-03-01'


select b.sid, b.username, b.serial#, a.spid, b.paddr, c.sql_text, b.machinefrom v$process a, v$session b, v$sqlarea cwhere a.addr = b.paddrand b.sql_hash_value = c.hash_value;
CREATE OR REPLACE PROCEDURE NEWLOG4_SUB_TABLE2 (delete_date in varchar2)
IS-- table_name1              VARCHAR2(50);-- create_table_sql         VARCHAR2(4000);
--  insert_data_sql      VARCHAR2(4000);delete_data_sql      VARCHAR2(4000);
--  v_exists INT:=0;--  v_exists  NUMBER (10, 0);CURSOR cur IS
select * from NEWLOG4 t where TO_CHAR(t.autudt,'YYYY-MM-DD') = delete_date and pushstate='3';
TYPE rec IS TABLE OF NEWLOG4%ROWTYPE;
recs rec;
BEGIN--將FATHER_TABLE表中取上月記錄 添加到新創建的分表中。-- insert_data_sql := 'INSERT INTO ' || table_name1 || ' SELECT * FROM NEWLOG4 WHERE autudt <(systimestamp - NUMTODSINTERVAL(30,''day'')) and pushstate=''3''';-- EXECUTE IMMEDIATE insert_data_sql;
OPEN cur;
WHILE (TRUE) LOOP
FETCH cur BULK COLLECT
INTO recs LIMIT 5000;
FORALL i IN 1 .. recs.COUNT
INSERT INTO NEWLOG4_202103_10 VALUES recs (i);COMMIT;EXIT WHEN cur%NOTFOUND;END LOOP;CLOSE cur;--刪除FATHER_TABLE表中時間在上個月范圍內的所有數據--delete_data_sql := 'DELETE FROM NEWLOG4 WHERE autudt <(systimestamp-NUMTODSINTERVAL(30,''day''))and pushstate=''3''';delete_data_sql :='delete /*+parallel(10)*/  from NEWLOG4  nologging  where TO_CHAR(t.autudt,''YYYY-MM-DD'') = delete_date and pushstate=''3''';EXECUTE IMMEDIATE delete_data_sql;COMMIT;--EXCEPTION--WHEN OTHERS THEN--   ROLLBACK;
END NEWLOG4_SUB_TABLE2;

CREATE OR REPLACE PROCEDURE NEWLOG4_SUB_TABLE
IStable_name1              VARCHAR2(50);create_table_sql         VARCHAR2(4000);insert_data_sql      VARCHAR2(4000);delete_data_sql      VARCHAR2(4000);
--  v_exists INT:=0;v_exists  NUMBER (10, 0);
BEGINSELECT 'NEWLOG4_' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYYMM') INTO table_name1 FROM DUAL;select count(1) into v_exists from user_tables where table_name=UPPER(table_name1);--dbms_output.put_line(sname);--dbms_output.put_line(table_name1);if (v_exists <1)then-- dbms_output.put_line(sname);create_table_sql := 'create table ' || table_name1 || ' (autudt          TIMESTAMP(6),authentype      VARCHAR2(2000),userid          VARCHAR2(2000),orgid           VARCHAR2(2000),org2id          VARCHAR2(2000),realname        VARCHAR2(2000),success         VARCHAR2(2000),idpname         VARCHAR2(2000),idpip           VARCHAR2(2000),vistorip        VARCHAR2(2000),vistorbrowser   VARCHAR2(2000),spid            VARCHAR2(2000),spurl           VARCHAR2(2000),info            VARCHAR2(2000),autdesc         VARCHAR2(2000),taketime        VARCHAR2(2000),orgnamefullpath VARCHAR2(2000),ines            INTEGER default 0,logid           VARCHAR2(32),inputaccount    VARCHAR2(2000),channel         NUMBER(32),pushstate       NUMBER(2) default 0,appid           VARCHAR2(50))';EXECUTE IMMEDIATE create_table_sql;commit;end if;--將FATHER_TABLE表中取上月記錄 添加到新創建的分表中。insert_data_sql := 'INSERT INTO ' || table_name1 || ' SELECT * FROM NEWLOG4 WHERE autudt <(systimestamp - NUMTODSINTERVAL(30,''day'')) and pushstate=''3''';EXECUTE IMMEDIATE insert_data_sql;--刪除FATHER_TABLE表中時間在上個月范圍內的所有數據delete_data_sql := 'DELETE FROM NEWLOG4 WHERE autudt <(systimestamp-NUMTODSINTERVAL(30,''day''))and pushstate=''3''';EXECUTE IMMEDIATE delete_data_sql;COMMIT;--EXCEPTION--WHEN OTHERS THEN--   ROLLBACK;
END NEWLOG4_SUB_TABLE;

CREATE OR REPLACE PROCEDURE NEWLOG4_day_TABLE (delete_date in varchar2)
ISinsert_data_sql      VARCHAR2(4000);delete_data_sql      VARCHAR2(4000);CURSOR cur IS
select * from NEWLOG4 t where TO_CHAR(t.autudt,'YYYY-MM-DD') = delete_date and pushstate='3';
TYPE rec IS TABLE OF NEWLOG4%ROWTYPE;
recs rec;
BEGIN
OPEN cur;
WHILE (TRUE) LOOP
FETCH cur BULK COLLECT
INTO recs LIMIT 5000;
FORALL i IN 1 .. recs.COUNT
--EXECUTE IMMEDIATE insert_data_sql;
INSERT INTO NEWLOG4_DAY_INTERVAL_PARTITION VALUES recs (i);COMMIT;EXIT WHEN cur%NOTFOUND;END LOOP;CLOSE cur;--EXCEPTION--WHEN OTHERS THEN--   ROLLBACK;
END NEWLOG4_day_TABLE;

?

?

?

?

?

?

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/news/535348.shtml
繁體地址，請注明出處：http://hk.pswp.cn/news/535348.shtml
英文地址，請注明出處：http://en.pswp.cn/news/535348.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

oracle 中DATETIME與TIMESTAMP區別

oracle 中DATETIME與TIMESTAMP區別

1.DATETIME的日期范圍是1001——9999年&#xff0c;TIMESTAMP的時間范圍是1970——2038年。 2.DATETIME存儲時間與時區無關&#xff0c;TIMESTAMP存儲時間與時區有關&#xff0c;顯示的值也依賴于時區。在mysql服務器&#xff0c;操作系統以及客戶端連接都有時區的設置。 3.DAT…
閱讀更多...
PARALLEL（并行）

PARALLEL（并行）

在Oracle中&#xff0c;PARALLEL&#xff08;并行&#xff09;方式最大化調用計算機資源來成倍提高數據分析效率。 1&#xff0e; 用途 強行啟用并行度來執行當前SQL。這個在Oracle 9i之后的版本可以使用&#xff0c;之前的版本現在沒有環境進行測試。也就是說&#xff0c;加…
閱讀更多...
Oracle數據庫查詢優化

Oracle數據庫查詢優化

1.對查詢進行優化&#xff0c;應盡量避免全表掃描&#xff0c;首先應考慮在 where 及 order by 涉及的列上建立索引。 2.應盡量避免在 where 子句中對字段進行 null 值判斷&#xff0c;否則將導致引擎放棄使用索引而進行全表掃描&#xff0c;如&#xff1a; select id from t w…
閱讀更多...
redis-full-check

redis-full-check

https://github.com/alibaba/RedisFullCheck/releases redis-full-check是阿里云Redis&MongoDB團隊開源的用于校驗2個redis數據是否一致的工具。 ??redis-full-check通過全量對比源端和目的端的redis中的數據的方式來進行數據校驗&#xff0c;其比較方式通過多輪次比較&a…
閱讀更多...
2021-06-22

2021-06-22

服務器信息 [rootiZs7z01dz0z12dyttz9zn5Z cluster]# /app/redis/redis-3.2.1/src/redis-cli -c -h 10.252.120.9 -p 8003 10.252.120.9:8003> cluster nodes b1f543d646c5c97a70b0635439a44a72f8a143b1 10.252.120.10:8004 master - 0 1624349601417 7 connected 0-5460 1…
閱讀更多...
Docker目錄掛載

Docker目錄掛載

Docker容器啟動的時候&#xff0c;如果要掛載宿主機的一個目錄&#xff0c;可以用-v參數指定。 譬如我要啟動一個centos容器&#xff0c;宿主機的/test目錄掛載到容器的/soft目錄&#xff0c;可通過以下方式指定&#xff1a; # docker run -it -v /test:/soft centos /bin/ba…
閱讀更多...
Redis主從復制原理學習

Redis主從復制原理學習

Redis主從復制原理學習總結 - 運維筆記 和Mysql主從復制的原因一樣&#xff0c;Redis雖然讀取寫入的速度都特別快&#xff0c;但是也會產生讀壓力特別大的情況。為了分擔讀壓力&#xff0c;Redis支持主從復制&#xff0c;Redis的主從結構可以采用一主多從或者級聯結構&#xff…
閱讀更多...
redis數據恢復

redis數據恢復

公司線上一個項目數據存儲采用MySQL&#xff0c;共分為10個庫&#xff0c;分布在4臺機器上&#xff0c;每個庫數據量約為10G&#xff0c;各機器均采用RAID5加速磁盤訪問&#xff1b; 當同時在線人數達高峰期&#xff08;10w&#xff09;&#xff0c;DB磁盤IO壓力巨大&#xff0…
閱讀更多...
Redis哨兵模式（sentinel）學習總結及部署記錄（主從復制、讀寫分離、主從切換）

Redis哨兵模式（sentinel）學習總結及部署記錄（主從復制、讀寫分離、主從切換）

Redis的集群方案大致有三種&#xff1a;1&#xff09;redis cluster集群方案&#xff1b;2&#xff09;master/slave主從方案&#xff1b;3&#xff09;哨兵模式來進行主從替換以及故障恢復。 一、sentinel哨兵模式介紹 Sentinel(哨兵)是用于監控redis集群中Master狀態的工具&…
閱讀更多...
Redis之Redis內存模型

Redis之Redis內存模型

Redis是目前最火爆的內存數據庫之一&#xff0c;通過在內存中讀寫數據&#xff0c;大大提高了讀寫速度&#xff0c;可以說Redis是實現網站高并發不可或缺的一部分。 我們使用Redis時&#xff0c;會接觸Redis的5種對象類型&#xff08;字符串、哈希、列表、集合、有序集合&…
閱讀更多...
MySQL 數據庫誤刪除后的數據恢復操作說明

MySQL 數據庫誤刪除后的數據恢復操作說明

在日常運維工作中&#xff0c;對mysql數據庫的備份是萬分重要的&#xff0c;以防在數據庫表丟失或損壞情況出現&#xff0c;可以及時恢復數據。 線上數據庫備份場景&#xff1a; 每周日執行一次全量備份&#xff0c;然后每天下午1點執行MySQLdump增量備份. 下面對這種備份方案…
閱讀更多...
MySQL 之binlog日志說明及利用binlog日志恢復數據操作記錄

MySQL 之binlog日志說明及利用binlog日志恢復數據操作記錄

眾所周知&#xff0c;binlog日志對于mysql數據庫來說是十分重要的。在數據丟失的緊急情況下&#xff0c;我們往往會想到用binlog日志功能進行數據恢復&#xff08;定時全備份binlog日志恢復增量數據部分&#xff09;&#xff0c;化險為夷&#xff01; 一、簡單了解binlog MySQ…
閱讀更多...
zabbix巡檢腳本

zabbix巡檢腳本

#!/bin/bash BIN/usr/local/zabbix/binpasswort() { name$2 while read line do ipecho $line|awk -F {print $1} timeecho $line|awk -F {print $2} echo -e "${name}passport${ip}探活時間\t $time" done <$1 }for i in 100.245.160.113 100.245.160.141 1…
閱讀更多...
mysqldump備份（全量+增量）

mysqldump備份（全量+增量）

在日常運維工作中&#xff0c;對mysql數據庫的備份是萬分重要的&#xff0c;以防在數據庫表丟失或損壞情況出現&#xff0c;可以及時恢復數據。 線上數據庫備份場景&#xff1a; 每周日執行一次全量備份&#xff0c;然后每天下午1點執行MySQLdump增量備份. 下面對這種備份方案…
閱讀更多...
查找指定日期數據所在分區數據

查找指定日期數據所在分區數據

select a.subobject_namefrom dba_objects a join (select dbms_rowid.rowid_object(rowid) object_idfrom NEWLOG4 where TO_CHAR(autudt,YYYY-MM-DD) 2021-06-22) b on a.object_id b.object_id and object_name UPPER(NEWLOG4) group by a.subobject_name
閱讀更多...
統計內存使用率shell

統計內存使用率shell

#!/bin/bashdatedate "%Y-%m-%d %H:%M:%S"#顯示消耗資源內存最高的進程名firstps aux | grep -v "grep" | grep -v "USER" | sort -rn -k 4 | head -4 | awk -F {print $13} | sed -n 1pSecondps aux | grep -v "grep" | grep -v &q…
閱讀更多...
Oracle 11g系統自動收集統計信息

Oracle 11g系統自動收集統計信息

從Oracle Database 10g開始&#xff0c;Oracle在建庫后就默認創建了一個名為GATHER_STATS_JOB的定時任務&#xff0c;用于自動收集CBO的統計信息&#xff0c;調用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集統計信息。該過程首先檢測統計信息缺失和陳舊的對象。然后確定優先…
閱讀更多...
Redis監控指標

Redis監控指標

監控指標 ?性能指標&#xff1a;Performance?內存指標: Memory?基本活動指標&#xff1a;Basic activity?持久性指標: Persistence?錯誤指標&#xff1a;Error 性能指標&#xff1a;Performance NameDescriptionlatencyRedis響應一個請求的時間instantaneous_ops_per_s…
閱讀更多...
innobackupex參數說明

innobackupex參數說明

1、備份&#xff1a; #常用參數 　　　　--user&#xff1a;該選項表示備份賬號。 　　　　--password&#xff1a;該選項表示備份的密碼。 　　　　--port&#xff1a;該選項表示備份數據庫的端口。 　　　　--host&#xff1a;該選項表示備份數據庫的地址。 　　　　--socket…
閱讀更多...
innobackupex遠程備份腳本

innobackupex遠程備份腳本

#!/bin/sh #備份主機 remote_ip10.2.142.161 Master_ip10.2.142.148 VIP103.2.132.136 #備份用戶 userroot #密碼 password123456 # 返回年月日 backup_datedate %F # 返回時分秒 backup_timedate %H-%M-%S # 返回今天是這周的第幾天 backup_week_daydate %u backup_ok0 #備份目…
閱讀更多...
最新文章

Copyright @ 2022~2023