Oracle用户名重建索引方法探究 (1)
- 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。
每个DBA都应该知道数据索引的重要性。特别是管理Oracle的DBA,很多应用项目会涉及到大量的修改删除操作,数据索引是需要周期性的重建的。这里介绍的根据Oracle用户名重建索引的方法可以帮助我们提高查询性能。
更重要的是,它不仅可以提高查询性能,还能增加索引表空间空闲空间大小。在Oracle里大量删除记录后,表和索引里占用的数据块空间并没有释放。重建索引可以释放已删除记录索引占用的数据块空间。
下面是可以按Oracle用户名生成重建索引的SQL脚本:
1.SET ECHO OFF;
2.SET FEEDBACK OFF;
3.SET VERIFY OFF;
4.SET PAGESIZE 0;
5.SET TERMOUT ON;
6.SET HEADING OFF;
7.ACCEPT username CHAR PROMPT 'Enter the index username: ';
8.spool /oracle/rebuild_&username.sql;
9.SELECT
10.'REM +-----------------------------------------------+' || chr(10) ||
11.'REM | INDEX NAME : ' || owner || '.' || segment_name
12.|| lpad('|', 33 - (length(owner) + length(segment_name)) )
13.|| chr(10) ||
14.'REM | BYTES : ' || bytes
15.|| lpad ('|', 34-(length(bytes)) ) || chr(10) ||
16.'REM | EXTENTS : ' || extents
17.|| lpad ('|', 34-(length(extents)) ) || chr(10) ||
18.'REM +-----------------------------------------------+' || chr(10) ||
19.'ALTER INDEX ' || owner || '.' || segment_name || chr(10) ||
20.'REBUILD ' || chr(10) ||
21.'TABLESPACE ' || tablespace_name || chr(10) ||
22.'STORAGE ( ' || chr(10) ||
23.' INITIAL ' || initial_extent || chr(10) ||
24.' NEXT ' || next_extent || chr(10) ||
25.' MINEXTENTS ' || min_extents || chr(10) ||
26.' MAXEXTENTS ' || max_extents || chr(10) ||
27.' PCTINCREASE ' || pct_increase || chr(10) ||
28.');' || chr(10) || chr(10)
29.FROM dba_segments
30.WHERE segment_type = 'INDEX'
31.AND owner='&username'
32.ORDER BY owner, bytes DESC;
33.spool off;
如果你用的是Windows系统,想改变输出文件的存放目录,修改spool后面的路径成:
spool c:\oracle\rebuild_&username.sql;
如果你只想对大于max_bytes的索引重建索引,可以修改上面的SQL语句:在AND owner='&username' 后面加个限制条件 AND bytes> &max_bytes
如果你想修改索引的存储参数,在重建索引rebuild_&username.sql里改也可以。比如把pctincrease不等于零的值改成是零。
生成的rebuild_&username.sql文件我们需要来分析一下,它们是否到了需要重建的程度:分析索引,看是否碎片严重。
1.SQL>ANALYZE INDEX &index_name VALIDATE STRUCTURE;
2.col name heading 'Index Name' format a30
3.col del_lf_rows heading 'Deleted|Leaf Rows' format
4.col lf_rows_used heading 'Used|Leaf Rows' format
5.col ratio heading '% Deleted|Leaf Rows' format 999.99999
6.SELECT name,
7.del_lf_rows,
8.
9.lf_rows - del_lf_rows lf_rows_used,
10.to_char(del_lf_rows / (lf_rows)*100,'999.99999') ratio
11.FROM index_stats where name = upper('&index_name');
当删除的比率大于15 - 20% 时,肯定是需要索引重建的。
经过删改后的rebuild_&username.sql文件我们可以放到Oracle的定时作业里:
比如一个月或者两个月在非繁忙时间运行。如果遇到ORA-00054错误,表示索引在的表上有锁信息,不能根据Oracle用户名来重建索引。
那就忽略这个错误,看下次是否成功。对那些特别忙的表要区别对待,不能用这里介绍的方法,还要把它们的索引从rebuild_&username.sql里删去。
文章来源:网络编辑:联动北方技术论坛
(如有侵权请及时联络以便删除)