Oracle中大批量删除数据的方法

Blender学习笔记——制作向量场 #下载Blender - UE4 - FGA Vector Field Editor插件的压缩包,Github上的下载地址如下:https://github.com/isathar/Blender_UE4_VectorFieldEditor #下载并解压后将FGA VectorFields文件夹复制到C:\Program Files\Blender Foundation\Blender 2.83\2.83\scripts\addons目录下,此步骤可根据blender安装的位置自行调整,只要放入addon 阅读详情

批量删除海量数据通常都是很复杂及缓慢的,方法也很多,但是通常的概念是:分批删除,逐次提交。
下面是我的删除过程,我的数据表可以通过主键删除,测试过Delete和For all两种方法,for all在这里并没有带来性能提高,所以仍然选择了批量直接删除。

首先创建一下过程,使用自制事务进行处理:


create or replace procedure delBigTab ( p_TableName in varchar2, p_Condition in varchar2, p_Count in varchar2 ) as pragma autonomous_transaction; n_delete number:=0; begin while 1=1 loop EXECUTE IMMEDIATE 'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn' USING p_Count; if SQL%NOTFOUND then exit; else n_delete:=n_delete + SQL%ROWCOUNT; end if; commit; end loop; commit; DBMS_OUTPUT.PUT_LINE('Finished!'); DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!'); end;

 

以下是删除过程及时间:

SQL> create or replace procedure delBigTab 2 ( 3 p_TableName in varchar2, 4 p_Condition in varchar2, 5 p_Count in varchar2 6 ) 7 as 8 pragma autonomous_transaction; 9 n_delete number:=0; 10 begin 11 while 1=1 loop 12 EXECUTE IMMEDIATE 13 'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn' 14 USING p_Count; 15 if SQL%NOTFOUND then 16 exit; 17 else 18 n_delete:=n_delete + SQL%ROWCOUNT; 19 end if; 20 commit; 21 end loop; 22 commit; 23 DBMS_OUTPUT.PUT_LINE('Finished!'); 24 DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!'); 25 end; 26 / Procedure created. SQL> set timing on SQL> select min(NUMDLFLOGGUID) from HS_DLF_DOWNLOG_HISTORY; MIN(NUMDLFLOGGUID) ------------------ 11000000 Elapsed: 00:00:00.23 SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11100000','10000'); PL/SQL procedure successfully completed. Elapsed: 00:00:18.54 SQL> select min(NUMDLFLOGGUID) from HS_DLF_DOWNLOG_HISTORY; MIN(NUMDLFLOGGUID) ------------------ 11100000 Elapsed: 00:00:00.18 SQL> set serveroutput on SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11200000','10000'); Finished! Totally 96936 records deleted! PL/SQL procedure successfully completed. Elapsed: 00:00:18.61 10万记录大约19s SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11300000','10000'); Finished! Totally 100000 records deleted! PL/SQL procedure successfully completed. Elapsed: 00:00:18.62 SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11400000','10000'); Finished! Totally 100000 records deleted! PL/SQL procedure successfully completed. Elapsed: 00:00:18.85 SQL> SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 13000000','10000'); Finished! Totally 1000000 records deleted! PL/SQL procedure successfully completed. Elapsed: 00:03:13.87 100万记录大约3分钟 SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 20000000','10000'); Finished! Totally 6999977 records deleted! PL/SQL procedure successfully completed. Elapsed: 00:27:24.69 700万大约27分钟

以上过程仅供参考.
DCDC--控制方式(Control Mode) DCDC反馈控制方式 阅读详情

相关推荐

SAP入门技术分享四:模块化程序

SAP入门技术分享三:模块化程序

ITxiaoangzai的博客 1152

Oracle中大批量删除数据的方法 作者:eygle

批量删除海量数据通常都是很复杂及缓慢的,方法也很多,但是通常的概念是:分批删除,逐次提交。下面是我的删除过程,我的数据表可以通过主键删除,测试过Delete和For all两种方法,for all在这里并没有带来性能提高,所以仍然选择了批量直接删除。首先创建一下过程,使用自制事务进行处理:create or replace procedure delBigTab(p_T

884

SpringBoot与锐浪报表服务器深度整合实战指南

本文详细介绍了SpringBoot与锐浪报表服务器的深度整合实战指南,包括环境准备、Windows/Linux系统配置、核心功能实现代码解析及常见问题解决方案。通过具体案例和代码示例,帮助开发者高效完成报表系统的集成与部署,提升企业级应用的报表处理能力。

weixin_29238147的博客 370

Oracle 使用存储过程批量删除

Oracle 使用存储过程批量删除

weixin_43721102的博客 3299

机器学习发展历史及现状(重要的期刊,会议,大牛总结)

1 引言 机器学习是指通过计算机学习数据中的内在规律性信息,获得新的经验和知识,以提高计算机的智能性,使计算机能够像人那样去决策。随着各行业对数据的需求量增多,对处理和分析数据的效率要求变高,一系列机器学习算法应运而生。机器学习算法主要是指通过数学及统计方法求解最优化问题的步骤和过程。针对不同的数据和不同模型需求,选择和使用适当的的机器学习算法可以更高效地解决一些实际问题。本文将从机器学习的起源...

Oscar2018的博客 5万+

运算放大器电路仿真实践:从零实现完整示例

通过一个完整的运算放大器电路仿真示例,深入掌握电路仿真的关键步骤与技巧,帮助电子爱好者和工程师快速上手实际项目设计。

weixin_34725745的博客 1319

oracle快速批量删除数据

--在数据量非常大的表中快速删除记录的方法: --设置每次删除100条记录: SET ROWCOUNT 100 WHILE 1 = 1 BEGIN   DELETE FROM [需要删除的表名称]   WHERE [删除条件]   IF @@rowcount END SET ROWCOUNT 0 --收缩数据库(如果需要): DBCC SHRINKDATABASE

婴年早肥的基地 1021

STM32HAL库 基于外部Flash+FatFs模拟U盘方式进行固件拷贝升级(详解)

STM32HAL库 基于外部Flash+FatFs模拟U盘方式进行固件拷贝升级

qq_36075612的博客 1万+

ORACLE删除数据保留前100条数据

delete from debug_log a where a.rowid in ( select rowid from debug_log minus select rowid from debug_log where rownum <10000 ) 保留10000条,如果有10000条记录的话 参考链接: oracle 删除数据保留前100条_yangzhiyong77-优快云博客

依夏Summer 3236

告别纯代码:用Visual Studio为LabVIEW封装一个‘即插即用’的海康VisionMaster控件

本文介绍如何利用Visual Studio为LabVIEW封装一个‘即插即用’的海康VisionMaster控件,解决传统集成方式中的代码臃肿和维护困难问题。通过.NET控件封装核心功能,实现LabVIEW与VisionMaster的高效对接,显著提升工业自动化开发效率。

weixin_33744854的博客 505

VIIRS-NPP夜间灯光遥感数据下载和预处理

右侧搜索框搜索“按掩膜提取”,提取出夜光影像中国区域。将其转换为Albers积投影和重采样到400m。点击右上角Export,选择shp格式。最后点export导出就行。点击复制按钮,来到这个。,ctrl + v粘贴。将小于0.3的值置为0。下载好月合成产品后,

热爱分享,热爱记录 1万+

oracle 快速删除大批量数据方法(全部删除,条件删除,删除大量重复记录)

oracle 快速删除大批量数据方法(全部删除,条件删除,删除大量重复记录) 分类: ORACLE 数据库 2011-05-24 16:39 8427人阅读 评论(2) 收藏 举报 oracledeletetablesqllogginginsert 全部删除 如果是删除某个表的所有数据,并且不需要回滚,使用 TRUNCATE 就ok了。关于Trancate 参见这里http...

dianchantuo7921的博客 1917

Linux ARM平台开发系列讲解(GMSL摄像头篇)1.2 MAX9296 GMSL链路配置

串行器 +解串器组成,软件需要做的就是写这三部分的驱动,让摄像头数据能到达。3. 配置解串器和串行器的GMSL链路。串行器和解串器上电成功之后,就可以配置。软件配置(注意软件只可以配置速率,2. 9296上电,使能芯片。的版本和速率,比如版本是配置。配置成功之后,就会出现串行器。的链路,其它同类型的。设备地址,我设置的是。...

DSMGUOGUO的博客 1万+

Oracle中大批量删除数据的方法(通过主键删除)

批量删除海量数据通常都是很复杂及缓慢的,方法也很多,但是通常的概念是:分批删除,逐次提交。 参见http://blog.youkuaiyun.com/robbie1314520/article/details/7816006 下面是另一种删除过程,我的数据表可以通过主键删除,测试过Delete和For all两种方法,for all在这里并没有带来性能提高,所以仍然选择了批量直接删除。 首先

robbie的专栏 6734

假人有限元模型

假人模型,车辆碰撞假人有限元模型,适合做乘员安全的研究者利用

深度学习工具包 DeepLearningToolbox

深度学习工具包Deprecation notice.-----This toolbox is outdated and no longer maintained.There are much better tools available for deep learning than this toolbox, e.g. [Theano](http://deeplearning.net/software/theano/), [torch](http://torch.ch/) or [tensorflow](http://www.tensorflow.org/)I would suggest you use one of the tools mentioned above rather than use this toolbox.Best, Rasmus.DeepLearnToolbox================A Matlab toolbox for Deep Learning.Deep Learning is a new subfield of machine learning that focuses on learning deep hierarchical models of data.It is inspired by the human brain's apparent deep (layered, hierarchical) architecture.A good overview of the theory of Deep Learning theory is[Learning Deep Architectures for AI](http://www.iro.umontreal.ca/~bengioy/papers/ftml_book.pdf)For a more informal introduction, see the following videos by Geoffrey Hinton and Andrew Ng.* [The Next Generation of Neural Networks](http://www.youtube.com/watch?v=AyzOUbkUf3M) (Hinton, 2007)* [Recent Developments in Deep Learning](http://www.youtube.com/watch?v=VdIURAu1-aU) (Hinton, 2010)* [Unsupervised Feature Learning and Deep Learning](http://www.youtube.com/watch?v=ZmNOAtZIgIk) (Ng, 2011)If you use this toolbox in your research please cite [Prediction as a candidate for learning deep hierarchical models of data](http://www2.imm.dtu.dk/pubdb/views/publication_details.php?id=6284)```@MASTERSTHESIS\{IMM2012-06284, author = "R. B. Palm", title = "Prediction as a candidate for learning deep hierarchical models of data", year = "2012",}```Contact: rasmusbergpalm at gmail dot comDirectories included in the toolbox-----------------------------------`NN/` - A library for Feedforward Backpropagation Neural Networks`CNN/` - A library for Convolutional Neural Networks`DBN/` - A library for Deep Belief Networks`SAE/` - A library for Stacked Auto-Encoders`CAE/` - A library for Convolutional Auto-Encoders`util/` - Utility functions used by the libraries`data/` - Data used by the examples`tests/` - unit tests to verify toolbox is workingFor references on each library check REFS.mdSetup-----1. Download.2. addpath(genpath('DeepLearnToolbox'));Example: Deep Belief Network---------------------```matlabfunction test_example_DBNload mnist_uint8;train_x = double(train_x) / 255;test_x = double(test_x) / 255;train_y = double(train_y);test_y = double(test_y);%% ex1 train a 100 hidden unit RBM and visualize its weightsrand('state',0)dbn.sizes = [100];opts.numepochs = 1;opts.batchsize = 100;opts.momentum = 0;opts.alpha = 1;dbn = dbnsetup(dbn, train_x, opts);dbn = dbntrain(dbn, train_x, opts);figure; visualize(dbn.rbm{1}.W'); % Visualize the RBM weights%% ex2 train a 100-100 hidden unit DBN and use its weights to initialize a NNrand('state',0)%train dbndbn.sizes = [100 100];opts.numepochs = 1;opts.batchsize = 100;opts.momentum = 0;opts.alpha = 1;dbn = dbnsetup(dbn, train_x, opts);dbn = dbntrain(dbn, train_x, opts);%unfold dbn to nnnn = dbnunfoldtonn(dbn, 10);nn.activation_function = 'sigm';%train nnopts.numepochs = 1;opts.batchsize = 100;nn = nntrain(nn, train_x, train_y, opts);[er, bad] = nntest(nn, test_x, test_y);assert(er < 0.10, 'Too big error');```Example: Stacked Auto-Encoders---------------------```matlabfunction test_example_SAEload mnist_uint8;train_x = double(train_x)/255;test_x = double(test_x)/255;train_y = double(train_y);test_y = double(test_y);%% ex1 train a 100 hidden unit SDAE and use it to initialize a FFNN% Setup and train a stacked denoising autoencoder (SDAE)rand('state',0)sae = saesetup([784 100]);sae.ae{1}.activation_function = 'sigm';sae.ae{1}.learningRate = 1;sae.ae{1}.inputZeroMaskedFraction = 0.5;opts.numepochs = 1;opts.batchsize = 100;sae = saetrain(sae, train_x, opts);visualize(sae.ae{1}.W{1}(:,2:end)')% Use the SDAE to initialize a FFNNnn = nnsetup([784 100 10]);nn.activation_function = 'sigm';nn.learningRate = 1;nn.W{1} = sae.ae{1}.W{1};% Train the FFNNopts.numepochs = 1;opts.batchsize = 100;nn = nntrain(nn, train_x, train_y, opts);[er, bad] = nntest(nn, test_x, test_y);assert(er < 0.16, 'Too big error');```Example: Convolutional Neural Nets---------------------```matlabfunction test_example_CNNload mnist_uint8;train_x = double(reshape(train_x',28,28,60000))/255;test_x = double(reshape(test_x',28,28,10000))/255;train_y = double(train_y');test_y = double(test_y');%% ex1 Train a 6c-2s-12c-2s Convolutional neural network %will run 1 epoch in about 200 second and get around 11% error. %With 100 epochs you'll get around 1.2% errorrand('state',0)cnn.layers = { struct('type', 'i') %input layer struct('type', 'c', 'outputmaps', 6, 'kernelsize', 5) %convolution layer struct('type', 's', 'scale', 2) %sub sampling layer struct('type', 'c', 'outputmaps', 12, 'kernelsize', 5) %convolution layer struct('type', 's', 'scale', 2) %subsampling layer};cnn = cnnsetup(cnn, train_x, train_y);opts.alpha = 1;opts.batchsize = 50;opts.numepochs = 1;cnn = cnntrain(cnn, train_x, train_y, opts);[er, bad] = cnntest(cnn, test_x, test_y);%plot mean squared errorfigure; plot(cnn.rL);assert(er<0.12, 'Too big error');```Example: Neural Networks---------------------```matlabfunction test_example_NNload mnist_uint8;train_x = double(train_x) / 255;test_x = double(test_x) / 255;train_y = double(train_y);test_y = double(test_y);% normalize[train_x, mu, sigma] = zscore(train_x);test_x = normalize(test_x, mu, sigma);%% ex1 vanilla neural netrand('state',0)nn = nnsetup([784 100 10]);opts.numepochs = 1; % Number of full sweeps through dataopts.batchsize = 100; % Take a mean gradient step over this many samples[nn, L] = nntrain(nn, train_x, train_y, opts);[er, bad] = nntest(nn, test_x, test_y);assert(er < 0.08, 'Too big error');%% ex2 neural net with L2 weight decayrand('state',0)nn = nnsetup([784 100 10]);nn.weightPenaltyL2 = 1e-4; % L2 weight decayopts.numepochs = 1; % Number of full sweeps through dataopts.batchsize = 100; % Take a mean gradient step over this many samplesnn = nntrain(nn, train_x, train_y, opts);[er, bad] = nntest(nn, test_x, test_y);assert(er < 0.1, 'Too big error');%% ex3 neural net with dropoutrand('state',0)nn = nnsetup([784 100 10]);nn.dropoutFraction = 0.5; % Dropout fraction opts.numepochs = 1; % Number of full sweeps through dataopts.batchsize = 100; % Take a mean gradient step over this many samplesnn = nntrain(nn, train_x, train_y, opts);[er, bad] = nntest(nn, test_x, test_y);assert(er < 0.1, 'Too big error');%% ex4 neural net with sigmoid activation functionrand('state',0)nn = nnsetup([784 100 10]);nn.activation_function = 'sigm'; % Sigmoid activation functionnn.learningRate = 1; % Sigm require a lower learning rateopts.numepochs = 1; % Number of full sweeps through dataopts.batchsize = 100; % Take a mean gradient step over this many samplesnn = nntrain(nn, train_x, train_y, opts);[er, bad] = nntest(nn, test_x, test_y);assert(er < 0.1, 'Too big error');%% ex5 plotting functionalityrand('state',0)nn = nnsetup([784 20 10]);opts.numepochs = 5; % Number of full sweeps through datann.output = 'softmax'; % use softmax outputopts.batchsize = 1000; % Take a mean gradient step over this many samplesopts.plot = 1; % enable plottingnn = nntrain(nn, train_x, train_y, opts);[er, bad] = nntest(nn, test_x, test_y);assert(er < 0.1, 'Too big error');%% ex6 neural net with sigmoid activation and plotting of validation and training error% split training data into training and validation datavx = train_x(1:10000,:);tx = train_x(10001:end,:);vy = train_y(1:10000,:);ty = train_y(10001:end,:);rand('state',0)nn = nnsetup([784 20 10]); nn.output = 'softmax'; % use softmax outputopts.numepochs = 5; % Number of full sweeps through dataopts.batchsize = 1000; % Take a mean gradient step over this many samplesopts.plot = 1; % enable plottingnn = nntrain(nn, tx, ty, opts, vx, vy); % nntrain takes validation set as last two arguments (optionally)[er, bad] = nntest(nn, test_x, test_y);assert(er < 0.1, 'Too big error');```[![Bitdeli Badge](https://d2weczhvl823v0.cloudfront.net/rasmusbergpalm/deeplearntoolbox/trend.png)](https://bitdeli.com/free "Bitdeli Badge")

Oracle大批量删除数据方法

批量删除海量数据通常都是很复杂及缓慢的,方法也很多,但是通常的概念是:分批删除,逐次提交。   下面是我的删除过程,我的数据表可以通过主键删除,测试过Delete和For all两种方法,for all在这里并没有带来性能提高,所以仍然选择了批量直接删除。  首先创建一下过程,使用自制事务进行处理: create or replace procedure delBigTab(p_Tab

消逝的地平线 808

迈瑞BS系列全自动生化分析仪LIS协议接口手册(内含HL7和ASTM)

迈瑞BS系列全自动生化分析仪LIS协议接口手册,内含HL7和ASTM)

plsql删除大量数据_Oracle中大批量删除数据的方法

批量删除海量数据通常都是很复杂及缓慢的,方法也很多,但是通常的概念是:分批删除,逐次提交。下面是我的删除过程,我的数据表可以通过主键删除,测试过Delete和For all两种方法,for all在这里并没有带来性能提高,所以仍然选择了批量直接删除。首先创建一下过程,使用自制事务进行处理:create or replace procedure delBigTab(p_TableName ...

weixin_29532367的博客 1368

VB摄像头监控

该VB模块可对摄像头捕获的画面进行录像等,可在创建的窗口进行控制

上一篇: 查看oracle 系统临时表空间、undo表空间、SGA和PGA大小
下一篇: suse下查看和修改计算机名
junmail
博客等级 码龄22年 159粉丝 452原创
评论 1
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值