在ORACLE里按用户名重建索引的方法

最新推荐文章于 2022-10-09 15:40:26 发布
最新推荐文章于 2022-10-09 15:40:26 发布
阅读量2.8k 收藏
点赞数
分类专栏： ORACLE技术 文章标签： oracle windows 存储 sql c

 


        ---------------------------------------------------------------------------
        SET ECHO      OFF;
        SET FEEDBACK  OFF;
        SET VERIFY    OFF;
        SET PAGESIZE  0;
        SET TERMOUT   ON;
        SET HEADING   OFF;
        ACCEPT username CHAR PROMPT 'Enter the index username: ';
        spool /oracle/rebuild_&username.sql;
       
        SELECT
                  'REM +-----------------------------------------------+' || chr(10) ||
                  'REM | INDEX NAME : ' || owner   || '.' || segment_name
                 || lpad('|', 33 - (length(owner) + length(segment_name)) )
                 || chr(10) ||
                  'REM | BYTES      : ' || bytes   
                 || lpad ('|', 34-(length(bytes)) ) || chr(10) ||
                  'REM | EXTENTS    : ' || extents
                 || lpad ('|', 34-(length(extents)) ) || chr(10) ||
                  'REM +-----------------------------------------------+' || chr(10) ||
          'ALTER INDEX ' || owner || '.' || segment_name || chr(10) ||
          'REBUILD ' || chr(10) ||
          'TABLESPACE ' || tablespace_name || chr(10) ||
          'STORAGE ( ' || chr(10) ||
          '  INITIAL     ' || initial_extent || chr(10) ||
          '  NEXT        ' || next_extent || chr(10) ||
          '  MINEXTENTS  ' || min_extents || chr(10) ||
          '  MAXEXTENTS  ' || max_extents || chr(10) ||
          '  PCTINCREASE ' || pct_increase || chr(10) ||
          ');' || chr(10) || chr(10)
        FROM   dba_segments
        WHERE  segment_type = 'INDEX'
          AND  owner='&username'
        ORDER BY owner, bytes DESC;
       
        spool off;

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

        如果你用的是 WINDOWS 系统 , 想改变输出文件的存放目录 , 修改 spool 后面的路径成 :
        spool c:/oracle/rebuild_&username.sql;

        如果你只想对大于 max_bytes 的索引重建索引 , 可以修改上面的 SQL 语句 :       
        在 AND  owner='&username' 后面加个限制条件 AND  bytes> &max_bytes

        如果你想修改索引的存储参数 , 在重建索引 rebuild_&username.sql 里改也可以 .
        比如把 pctincrease 不等于零的值改成是零 .

        如果你想把 index 从一个 TABLESPACE 转移到另外一个 TABLESPACE ，也可以修改其中的 tablespace_name
 
huanghui22
关注
专栏目录
Oracle_索引重建—优化索引碎片
tworain
05-02 675
– 查询是否需要重建索引： – 分析索引的数据块是否有坏块，以及根据分析得到的数据（存放在index_stats）來判断索引是否需要重新建立。 SQL> analyze index 索引名称validate structure;validate structure有二中模式：  online ：（默认）会对表加一个4级別的锁（表共享），对run系統可能造成一定的影响。   offline ：没有表lock的影响，但当以online模式分析时， 在视图index_stats没有统计信息。 从.
oracle 修索引改空间_【迁移】Oracle分区表及索引迁移表空间
weixin_34540432的博客
01-28 999
近期计划使用XTTS方式迁移某库，在进行自包含检查时发现，该库有部分数据(分区表、索引)存放于SYSTEM表空间中，需要先将这部分数据移动到要迁移的表空间中。一、环境说明操作系统：CentOS 5.7数据库版本：11.2.0.3二、准备工作1、表空间自包含检查execute sys.dbms_tts.transport_set_check('USERS,XXXX', true);select * ...
ORACLE重建索引总结
12-13
一、重建索引的前提 1、表上频繁发生update,delete操作； 2、表上发生了alter table ..move操作（move操作导致了rowid变化）。 二、重建索引的标准 1、索引重建是否有必要，一般看索引是否倾斜的严重，是否浪费了空间， 那应该如何才可以判断索引是否倾斜的严重，是否浪费了空间， 对索引进行结构分析（如下）： SQL>Analyze index index_name validate structure; 2、在执行步骤1的session中查询index_stats表，不要到别的session去查询。 SQL>select height,DEL_LF_ROWS/LF_ROWS from index_stats; 说明：当 查询出来的 height>=4 或者 DEL_LF_ROWS/LF_ROWS>0.2 的场合 ， 该索引考虑重建 。 举例： (t_gl_assistbalance 26 万多条信息 ) SQL> select count(*) from t_gl_assistbalance ; 输出结果： COUNT(*) ---------- 265788 SQL> Analyze index IX_GL_ASSTBAL_1 validate structure; Index analyzed SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats; 输出结果： HEIGHT DEL_LF_ROWS/LF_ROWS ---------- ------------------- 4 1 三、重建索引的方式 1、drop 原来的索引，然后再创建索引; 举例： 删除索引：drop index IX_PM_USERGROUP; 创建索引：create index IX_PM_USERGROUP on T_PM_USER (fgroupid); 说明：此方式耗时间，无法在24*7环境中实现，不建议使用。 2 、直接重建： 举例： alter index indexname rebuild; 或alter index indexname rebuild online; 说明：此方式比较快，可以在24*7环境中实现，建议使用此方式。 四、alter index rebuild 内部过程和注意点 alter index rebuild 和alter index rebuil online的区别 1、扫描方式不同 1.1、Rebuild以index fast full scan（or table full scan） 方式读取原索引中的数据来构建一个新的索引，有排序的操作; 1.2、rebuild online 执行表扫描获取数据，有排序的操作; 说明：Rebuild 方式 (index fast full scan or table full scan 取决于统计信息的cost) 举例1 SQL> explain plan for alter index IX_GL_ASSTBAL_1 rebuild; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------- | 0 | ALTER INDEX STATEMENT | | 999K| 4882K| 3219 | | 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID2 | | | | | 2 | SORT CREATE INDEX | | 999K| 4882K| | | 3 | INDEX FAST FULL SCAN | IDX_POLICY_ID2 | 999K| 4882K| | --------------------------------------------------------------------- 举例2 SQL> explain plan for alter index idx_policy_id rebuild; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------- | 0 | ALTER INDEX STATEMENT | | 2072K| 9M| 461 | | 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID | | | | | 2 | SORT CREATE INDEX | | 2072K| 9M| | | 3 | TABLE ACCESS FULL | TEST_INDEX | 2072K| 9M| 461 | 举例3 ( 注意和 举例1 比较 ) Rebuil online 方式 ： SQL> explain plan for alter index idx_policy_id2 rebuild online; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------------------| 0 | ALTER INDEX STATEMENT | | 999K| 4882K| 3219 | | 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID2 | | | | | 2 | SORT CREATE INDEX | | 999K| 4882K| | | 3 | TABLE ACCESS FULL | TEST_INDEX2 | 999K| 4882K| 3219 | 2 、rebuild 会阻塞 dml 操作 ，rebuild online 不会阻塞 dml 操作 ； 3 、rebuild online 时系统会产生一个 SYS_JOURNAL_xxx 的 IOT 类型的系统临时日志表 ， 所有 rebuild online 时索引的变化都记录在这个表中 ， 当新的索引创建完成后 ， 把这个表的记录维护到新的索引中去 ， 然后 drop 掉旧的索引 ，rebuild online 就完成了。 注意点： 1、 执行rebuild操作时，需要检查表空间是否足够； 2、虽然说rebuild online操作允许dml操作，但是还是建议在业务不繁忙时间段进行； Rebuild操作会产生大量redo log ； 五、重建分区表上的分区索引 重建分区索引方法： Alter index indexname rebuild partition paritionname tablespace tablespacename; Alter index indexname rebuild subpartition partitioname tablespace tablespacename; Partition name 可以从user_ind_partitions查找 Tablepace 参数允许alter index操作更改索引的存储空间； 六、索引状态描述 在数据字典中查看索引状态，发现有三种： valid:当前索引有效 N/A :分区索引 有效 unusable:索引失效 七、术语 1、高基数：简单理解就是表中列的不同值多。 2、低基数：建单理解就是表中的列的不同值少。 3、以删除的叶节点数量：指得是数据行的delete操作从逻辑上删除的索引节点 的数量，要记住oracle在删除数据行后，将 “ 死 “ 节点保留在索引中，这样做可以加快sql删除操作的速度，因此oracle删除数据行后可以不必重新平衡索引。 4、索引高度：索引高度是指由于数据行的插入操作而产生的索引层数，当表中添加大量数据时，oracle将生成索引的新层次以适应加入的数据行，因此，oracle索引可能有4层，但是这只会出现在索引数中产生大量插入操作的区域。Oracle索引的三层结构可以支持数百万的项目，而具备4层或是更多层的需要重建。 5、每次索引访问的读取数：是指利用索引读取一数据行时所需要的逻辑I/O操作数，逻辑读取不必是物理读取，因为索引的许多内容已经保存在数据缓冲区，然而，任何数据大于10的索引都需要重建。 6、什么时候重建呢？ 察看 dba_indexes 中的 blevel 。这列是说明索引从根块到叶快的级别，或是深度。如果级别大于等于4。则需要重建， 如下 ：Select index_name,blevel from dba_indexes where blevel>=4. 另一个从重建中受益的指标显然是当该索引中的被删除项占总的项数的百分比。如果在20%以上时，也应当重建，如下 SQL>analyze index index_name validate structure SQL>select (del_lf_rows_len/lf_rows_len)*100 from index_stats where name= ’ index_name ’ 就能看到是否这个索引被删除的百分比。 7、什么样的重建方式更好？ （1）、建索引的办法： 1.1、删除并从头开始建立索引。 1.2 、 使用 alter index index_name rebuild 命令重建索引。 1.3 、 使用 alter index index_name coalesce 命令重建索引。 （2）、下面讨论一下这三种方法的优缺点： 2.1、删除并从头开始建索引：方法是最慢的，最耗时的。一般不建议。 2.2、Alter index index_name rebuild 快速重建索引的一种有效的办法，因为使用现有索引项来重建新索引，如果客户操作时有其他用户在对这个表操作，尽量使用带online参数来最大限度的减少索引重建时将会出现的任何加锁问题，alter index index_name rebuild online。 但是,由于新旧索引在建立时同时存在，因此，使用这种技巧则需要有额外的磁盘空间可临时使用，当索引建完后把老索引删除，如果没有成功，也不会影响原来的索引。利用这种办法可以用来将一个索引移到新的表空间。 Alter index index_name rebuild tablespace tablespace_name 。 这个命令的执行步骤如下： 首先，逐一读取现有索引，以获取索引的关键字。 其次，按新的结构填写临时数据段。 最后，一旦操作成功，删除原有索引树，降临时数据段重命名为新的索引。 需要注意的是alter index index_name rebuild 命令中必须使用tablespace字句，以保证重建工作是在现有索引相同的表空间进行。 2.3、alter index index_name coalesce 使用带有coalesce参数时重建期间不需要额外空间，它只是在重建索引时将处于同一个索引分支内的叶块拼合起来，这最大限度的减少了与查询过程中相关的潜在的加锁问题，但是，coalesce选项不能用来将一个索引转移到其他表空间。 八、其他 1、truncate 分区操作和truncate 普通表的区别？ 1.1、Truncate 分区操作会导致全局索引失效; truncate 普通表对索引没有影响； 1.2、Truncate 分区操作不会释放全局索引中的空间，而truncate 普通表会释放索引所占空间； 2、rename 表名操作对索引没有影响，因为rename操作只是更改了数据字典，表中数据行的rowid并没有发生变化 总结： 1、判断是否需要重建索引： SQL>analyze index index_name validate structure; SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats; （ 或 Select index_name,blevel from dba_indexes where blevel>=4 ）; 说明 ： 当查询出来的 height>=4 或者 DEL_LF_ROWS/LF_ROWS>0.2 的场合 ， 该索引考虑重建 ； 2 、重建索引方法 ： 方法一、 Alter index index_name rebuild tablespace tablespace_name; 优点：是快速重建索引的一种有效的办法，可以用来将一个索引移到新的表空间。 缺点：重建期间需要额外空间。 方法二、 alter index index_name coalesce; 优点：重建期间不需要额外空间。 缺点：coalesce选项不能用来将一个索引转移到其他表空间。
ORACLE里按用户名重建索引的方法
hakunamatata2008的专栏
04-17 490
         如果你管理的ORACLE数据库下某些应用项目有大量的修改删除操作, 数据索引是需要周期性的重建的.                它不仅可以提高查询性能, 还能增加索引表空间空闲空间大小.                 在ORACLE里大量删除记录后, 表和索引里占用的数据块空间并没有释放.                 重建索引可以释放已删除记录索引占用的数据块空间. 
Oracle按用户批量重建索引
yudehui的专栏
10-22 2132
按用户批量重建索引： 按用户将此用户下面非临时表上面的索引全部重建，此过程建议在SYS用户下面执行： Sql代码 CREATE OR REPLACE PROCEDURE BATCH_REBUILD_INDEX(USER_NAME IN VARCHAR2) IS S_SQL VARCHAR2(500); ACCOUNT NUMBER := 0; BEGI
在Oracle数据库中按用户名重建索引的方法
03-24 309
假如你治理的据库下某些使用项目有很多的修正删除操作， 数据索引是需求周期性的重建的。 它不只可以进步查询功能， 还能添加索引表空间闲暇空间巨细。 在很多删除记载后， 表和索引里占用的数据块空间并没有释放。 重建索引可以释放已删除记载索引占用的数据块空间。 转移数据， 重定名的办法可以从新组织内外的数据。 下面是可以按户名生成重建索引的SQL剧本： SET ECH
Oracle索引重构脚本与优化策略：提高查询性能
Oracle重建索引是数据库管理中的一项重要任务，它能够显著提升查询性能，因为索引如同图书馆的目录，帮助快速定位数据。然而，随着数据的频繁插入、更新和删除操作，索引可能会出现碎片和B树高度增加等问题，这可能...
Oracle笔记十：索引
Mavey__的博客
08-19 1267
索引的创建，索引的分类，索引的维护
Oracle数据库索引失效解决方案：重建、优化与维护
[Oracle数据库索引失效解决方案：重建、优化与维护](https://help-static-aliyun-doc.aliyuncs.com/assets/img/zh-CN/0537141761/p536336.png) # 1. Oracle数据库索引简介** 索引是Oracle数据库中一种重要的数据...
[转贴]在ORACLE里按用户名重建索引的方法
唐僧的专栏
04-18 823
                                              如果你管理的ORACLE数据库下某些应用项目有大量的修改删除操作, 数据索引是需要周期性的重建的.                它不仅可以提高查询性能, 还能增加索引表空间空闲空间大小.                 在ORACLE里大量删除记录后, 表和索引里占用的数据块空间并没有释放.      
Oracle修改用户、表、索引所在的表空间
by0920的博客
11-18 2663
修改表所在的表空间 alter table [表名称] move tablespace [表空间名称] -- 在当前用户下执行 SELECT 'ALTER TABLE ' || TABLE_NAME ||' MOVE TABLESPACE [表空间名称];' FROM USER_TABLES ; 如果表里含有clob字段，则需要特殊处理。 ​-- 在当前用户下执行 ALTER TAB...
对于非SYS用户索引建在SYS下面的删除与重建；
N
09-10 1205
事情是这样，在整理索引碎片的时候提示没有找到指到的索引，查看dba_indexes才行owner是属于SYS,于是删除在sys用户下的索引， 并重建索引（针对单列非组合索引） 1.sys用户执行删除 select 'drop index '||owner||'.'||index_name||';' from dba_indexes where table_owner like 'R
Oracle索引详解（索引的原理，创建索引，删除索引，修改索引等）
xinliucs的博客
10-09 2万+
Oracle索引的介绍，索引的创建，索引的删除和修改等
索引，索引的建立、修改、删除
腾飞FLY
12-27 2460
索引，索引的建立、修改、删除  转自：http://www.cnblogs.com/djcsch2001/articles/1823459.html   索引索引是关系数据库中用于存放每一条记录的一种对象，主要目的是加快数据的读取速度和完整性检查。建立索引是一项技术性要求高的工作。一般在数据库设计阶段的与数据库结构一道考虑。应用系统的性能直接与索引的合理直接有关。下面给出建立索引的方法和要点...
Oracle重建用户下同一表空间的所有索引
iteye_19498的博客
01-05 432
declare STR VARCHAR2(400); begin -- 重建ORACLE索引- FOR TMP_IDX IN (SELECT TABLESPACE_NAME, TABLE_NAME, INDEX_NAME FROM user_indexes WHERE temporary = ...
ORACLE重建索引
sayyy的专栏
04-23 1万+
重建索引 alter index 索引名称 rebuild tablespace DELL_SPACE online; 索引分析 analyze index 索引名称 validate structure; select (del_lf_rows_len/lf_rows_len) from index_stats where name='索引名称';
oracle 修改索引的名称,Oracle中查询索引名称，批量修改索引名称语句
weixin_39588419的博客
04-05 313
在Oralce数据库数据优化过程中，对源数据表处理，原则上是做更名备份，作为被查或回退使用，所以，有修改数据表名后重新建表的操作，这样，往往也需要修改索引、主键、外键名称，方便重建，为了方便、快速生成处理数据脚本，采用批量处理方式，如第4、5段例句，拼接字符串，生成批量处理脚本。 一、依据DBA视图查询，涉及到的视图有：user_ind_columns、user_indexes、user_con...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值