Oracle 删除大量表记录操作分析总结

目录
  • 删除表数据操作
  • 释放表空间
  • 重建索引
  • 分析表

删除表数据操作

清空所有表记录：

TRUNCATE TABLE your_table_name;

或者批量删除满足条件的表记录：

BEGIN
  LOOP
    DELETE FROM your_table_name WHERE rownum <= 50000;
      EXIT WHEN SQL%ROWCOUNT = 0;
   COMMIT;
 END LOOP;
END;

释放表空间

存放大数据量的表，其表空间占用也比较大，删除数据后并不会自动释放这些记录占用的表空间，所以，即便表里面数据量很少，查询效率依旧很慢，所以，需要释放表空间。

-- 查询数据表占用的表空间大小
SELECT sum(bytes)/(1024*1024) FROM user_segments WHERE segment_name='YOUR_TABLE_NAME'; --注意，表名必须大写

说明：sum(bytes)/(1024*1024) 数据统计单位由Byte转为GB

--整理碎片，释放已删除记录占用的表空间
ALTER TABLE your_table_name MOVE;

重建索引

释放了表空间以后表的ROWID会发生变化，基于ROWID的索引会失效，此时就需要重建索引

--重建非分区索引
ALTER TABLE your_table_index REBUILD [ONLINE] [NOLOGGING];

--重建分区索引
--针对分区索引-非组合索引
ALTER INDEX your_table_index REBUILD PARTITION your_partition_name [ONLINE] [NOLOGGING];

注意：

  • 设置日志级别为NOLOGGING 意味尽量减少日志，可以加速索引重建
  • ONLINE，NOLOGGING 两者位置顺序可以对调，不影响
  • 普通情况下建立索引或者REBUILD索引时，oracle会对基表加共享锁，在这期间，无法对表进行DML操作。如果希望避免这种情况，需要加ONLINE选项
  • 对索引进行REBUILD时，如果不加ONLINE选项，则Oracle直接读取原索引的数据，否则直接扫描表中的数据 ，索引在重建时，查询仍然可以使用旧索引。实际上，Oracle在REBUILD索引的过程中，并不会删除旧索引，直到新索引重建成功，这就是相对删除索引然后重建索引的一个好处：不会影响原有的SQL查询。但也正由于此，用REBUILD方式建立索引需要相应表空间的空闲空间是删除重建方式的2倍。
  • 不能直接REBUILD整个分区索引
    • 对于非组合索引，需要REBUILD每个分区（partition）
    • 对于组合索引，需要REBUILD每个子分区（subpartition）
    • 分区、子分区较多的情况下，可以使用下面的SQL可以生成相应的REBUILD语句
--重建分区索引-非组合索引
SELECT 'ALTER INDEX ' || index_owner || '.' ||index_name ||' REBUILD PARTITION ' || partition_name || ' NOLOGGING;'
FROM dba_ind_partitions
WHERE index_owner = 'INDEX_OWNER_NAME'  --可选查询条件，注意，如果指定该条件，索引拥有者必须大写
AND index_name = 'YOUR_INDEX_NAME'; --注意，索引名称必须大写 

--重建分区索引-组合索引
SELECT 'ALTER INDEX ' || index_owner || '.' ||index_name ||' REBUILD SUBPARTITION ' || subpartition_name || ' NOLOGGING;'
FROM dba_ind_subpartitions
WHERE index_owner = 'INDEX_OWNER_NAME' --可选查询条件，注意，如果指定该条件，索引拥有者必须大写
AND index_name = 'YOUR_INDEX_NAME'; --注意，索引名称必须大写

针对非分区索引，如果清理的表比较多，或者不知道被清理的表拥有哪些索引，可以使用以下SQL查询并生成对应的重建索引SQL

SELECT concat(concat('ALTER INDEX ', INDEX_NAME), ' REBUILD;')
FROM all_indexes
WHERE owner='INDEX_OWNER_NAME' --可选查询条件，注意，如果指定该条件，索引拥有者必须大写
AND table_name IN('TABLE_NAME1 ','TABLE_NAME2', '...', 'TABLE_NAMEN'）--注意，表名必须大写

分析表

分析表，是为了使基于CBO的执行计划更加准确，在一定程度上能带来一些性能提升

ANALYZE TABLE table_name COMPUTE STATISTICS;

--等价于
ANALYZE TABLE table_name COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL COLUMNS;

说明：生成的统计信息的存放位置：

  • FOR TABLE的统计信息存在于视图：USER_TABLES 、ALL_TABLES、DBA_TABLES
  • FOR ALL INDEXES的统计信息存在于视图： USER_INDEXES 、ALL_INDEXES、DBA_INDEXES
  • FOR ALL COLUMNS的统计信息存在于试图：USER_TAB_COLUMNS、ALL_TAB_COLUMNS、DBA_TAB_COLUMNS

到此这篇关于Oracle 删除大量表记录操作分析总结的文章就介绍到这了,更多相关Oracle 删除大量表 内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • Oracle删除表及查看表空间的实例详解

    Oracle常用的基本命令 --1.用户下表中注释模糊查询: 例如查询与优惠券关联的表 SELECT * FROM user_tab_comments t WHERE t.comments LIKE '%优惠券%'; ![这里写图片描述](http://img.blog.csdn.net/20170321112728053?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvdTAxMDQyNzkzNQ==/font/5a6L5L2T/fontsize/40

  • navicat删除oracle表的操作方法

    第一步,打开Navicat. 第二步,右击oracle数据库,然后点击"打开连接". 相关推荐:<Navicat for mysql使用图文教程> 第三步,点击"其它",然后点击"数据库链接",此时显示oracle数据库中所有的数据库链接. 第四步,双击要删除的数据库链接,核对数据库链接的内容,确保无误. 第五步,右击要删除的数据库链接,选择"删除数据库链接". 第六步,勾选"确定",然后点击&

  • Oracle删除表、字段之前判断表、字段是否存在

    在Oracle中若删除一个不存在的表,如 "DROP TABLE tableName",则会提示: ORA-00942:表或视图不存在 若在程序中执行该语句则会报异常,这就需要我们在删除表前先判断该表是否存在,若存在则删除. DECLARE num NUMBER; BEGIN SELECT COUNT(1) INTO num FROM USER_TABLES WHERE TABLE_NAME = UPPER('tableName'); IF num > 0 THEN EXECUT

  • Oracle 删除用户和表空间详细介绍

    Oracle 删除用户和表空间 Oracle 使用时间长了, 新增了许多user 和tablespace. 需要清理一下 对于单个user和tablespace 来说, 可以使用如下命令来完成.  步骤一:  删除user drop user ×× cascade 说明: 删除了user,只是删除了该user下的schema objects,是不会删除相应的tablespace的. 步骤二: 删除tablespace DROP TABLESPACE tablespace_name INCLUDI

  • Linux下Oracle删除用户和表空间的方法

    本文实例讲述了Linux下Oracle删除用户和表空间的方法.分享给大家供大家参考,具体如下: 1.删除某个用户 SQL> conn /as sysdba Connected. SQL> drop user userName cascade; 用户已删除 如果用户无法删除,并报错: ERROR at line 1: ORA-01940: cannot drop a user that is currently connected 通过查看用户的进行,并kill用户进程,然后删除用户. SQL&

  • oracle delete误删除表数据后如何恢复

    oracle delete误删除表数据后如何恢复

    1.根据时间进行恢复 此种方式需要我们大致知道执行delete语句的时间. 查询系统当前时间:select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’) from dual; 假设在2022-04-02 16:27:11分钟,执行了删除语句delete from demo ; 此时已经表中不能查询到数据了.我们知道delete执行的时间,往前推1分钟(delete执行时间之前都可以,越小越好,本例以1分钟为例),执行如下语句 select * from DEM

  • oracle 批量删除表数据的几种方法

    1.情景展示 情景一: 删除PRIMARY_INDEX_TEST表中,MINDEX_ID字段为空的数据 情景二: 删除VIRTUAL_CARD_TEST表中的脏数据 2.解决方案 情景一的解决方案: DELETE FROM PRIMARY_INDEX_TEST WHERE MINDEX_ID IS NULL 情景二的解决方案: 方案1:使用快速游标法(删除一次提交一次): --快速游标法 BEGIN FOR TEMP_CURSOR IN (SELECT ID FROM VIRTUAL_CARD3

  • Oracle误删除表数据后的数据恢复详解

    Oracle误删除表数据后的恢复详解   测试环境: SYSTEM:IBM AIX 5L                         Oracle Version:10gR2 1. undo_retention参数的查询与修改 使用show parameter undo命令查看当前的数据库参数undo_retention设置. 显示如下: SQL> show parameter undo NAME                                 TYPE        VAL

  • Oracle中多表关联批量插入批量更新与批量删除操作

    该文章会分为三部分 1.多表关联批量插入 2.多表关联批量更新 3.多表关联批量删除 首先要明白一点,为什么会有批量这一个概念,无非就是数据太多了,在java端把数据查出来然后在按照100-300的批次进行更新太耗性能了,而且写出来的代码会非常的臃肿,所谓好的实现是用最少的,最精简的代码实现需求,代码越少,留给自己犯错误的机会更少. 还有一个知识点就是多表关联,对于查询肯定是可以多表关联的,其实对于除了查询之外也是可以进行多表关联过滤数据的,从而达到在Oracle中查到目标数据即可更新,从而规避

  • Oracle 删除大量表记录操作分析总结

    目录 删除表数据操作 释放表空间 重建索引 分析表 删除表数据操作 清空所有表记录: TRUNCATE TABLE your_table_name; 或者批量删除满足条件的表记录: BEGIN LOOP DELETE FROM your_table_name WHERE rownum <= 50000; EXIT WHEN SQL%ROWCOUNT = 0; COMMIT; END LOOP; END; 释放表空间 存放大数据量的表,其表空间占用也比较大,删除数据后并不会自动释放这些记录占用的表

  • mysql支持跨表delete删除多表记录

    前几天写了Mysql跨表更新的一篇总结,今天我们看下跨表删除. 在Mysql4.0之后,mysql开始支持跨表delete. Mysql可以在一个sql语句中同时删除多表记录,也可以根据多个表之间的关系来删除某一个表中的记录. 假定我们有两张表:Product表和ProductPrice表.前者存在Product的基本信息,后者存在Product的价格. 第一种跨表删除的方式是不用join,在delete时指定用半角逗号分隔多个表来删除,如下sql语句: 复制代码 代码如下: DELETE p.

  • oracle快速删除重复的记录

    正在看的ORACLE教程是:oracle快速删除重复的记录.做项目的时候,一位同事导数据的时候,不小心把一个表中的数据全都搞重了,也就是说,这个表里所有的记录都有一条重复的.这个表的数据是千万级的,而且是生产系统.也就是说,不能把所有的记录都删除,而且必须快速的把重复记录删掉. 对此,总结了一下删除重复记录的方法,以及每种方法的优缺点. 为了陈诉方便,假设表名为Tbl,表中有三列col1,col2,col3,其中col1,col2是主键,并且,col1,col2上加了索引. 1.通过创建临时表

  • Oracle数据库丢失表排查思路实战记录

    Oracle数据库丢失表排查思路实战记录

    目录 说明: 写在最后: 总结 说明: 由于系统采用ID取模分表法进行Oracle数据存储,某日发现Oracle数据库中缺少对应的几张业务数据表,遂进行相关问题查询,简单记录一下排查思路: 由于我们代码中实现思路是判断如果没有对应的表会自动创建,所以首先需要查询一下缺失数据库表的创建时间 SELECT * FROM dba_objects where OBJECT_NAME LIKE 'LOG_5%' AND owner = 'Geoff'; 通过查询Oracle执行SQL历史记录,数据库表的删

  • access下如何恢复已经删除的记录；如何恢复已经删除的表、窗体等等对象

    问题: 如何恢复已经删除的记录:如何恢复已经删除的表.窗体等等对象 1.我用 DELETE FROM TABLE 删除了一些记录,现在发现误删除了,该如何恢复? 2.我直接手动删除或者用 DROP TABLE 删除了一个表,现在发现是误删除了,该如何恢复? 3.我手动删除了一个窗体,该如何恢复? 4.我删除了记录,可是数据库体积并没有减小,那么是否能找回记录呢? 回答: 1.已经删除的记录是无法恢复的,ACCESS 不是 FOXPRO,MDB 格式不是 DBF 格式,没有逻辑删除和物理删除的概念

  • 解析Oracle中多表级联删除的方法

    表间的关系比较复杂,数据量又比较多,一个个删绝对会出大问题.于是实验了几种解决的办法,现小结一下. 方法一:创建约束时设定级联删除(但一般由于各种原因或出于各种考虑在创建数据库时没有设定级联删除)SQL语句: 复制代码 代码如下: CREATE TABLE "U_WEN_BOOK"."FARTAB" ("FAR_ID" NUMBER(10) NOT NULL,"FAR_NAME" VARCHAR2(10), "OTH

  • shell脚本操作oracle删除表空间、创建表空间、删除用户

    oracle下表空间的导出,用户的删除,表空间删除,用户新建,表空间新建,数据导入的shell使用非oracle用户执行该脚本参数说名$1:base表空间的用户名$2:同步表空间的用户名使用场景测试用,base表空间用于升级建立一些固化数据.同步表空间用于测试用,每次去和base表空间拉平数据 复制代码 代码如下: #!/bin/shoraclehome=$ORACLE_HOMEecho $oraclehomelocaldir="/oracle/data"echo $localdir#

随机推荐