锐客网

  1. 首页 > 生活百科 > it技术 > >

oracle怎么重建索引 oracle重建索引会丢失数据吗

索引IT技术

oracle 数据库如何建立索引 如何用索引?创建索引语法：
CREATE [UNIQUE] | [BITMAP] INDEX index_name
--unique表示唯一索引
ON table_name([column1 [ASC|DESC],column2
--bitmap ， 创建位图索引
[ASC|DESC],…] | [express])[TABLESPACE tablespace_name][PCTFREE n1]
--指定索引在数据块中空闲空间
[STORAGE (INITIAL n2)][NOLOGGING]
--表示创建和重建索引时允许对表做DML操作，默认情况下不应该使用
[NOLINE][NOSORT];
--表示创建索引时不进行排序，默认不适用，如果数据已经是按照该索引顺序排列的可以使用
扩展资料：
1、如果有两个或者以上的索引 ， 其中有一个唯一性索引 ， 而其他是非唯一，这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引
2、至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）
3、小表不要简历索引
4、对于基数大的列适合建立B树索引 ， 对于基数小的列适合简历位图索引
5、列中有很多空值，但经常查询该列上非空记录时应该建立索引
6、经常进行连接查询的列应该创建索引
7、使用create index时要将最常查询的列放在最前面
8、LONG（可变长字符串数据 ， 最长2G）和LONG RAW（可变长二进制数据，最长2G）列不能创建索引
9、限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候 ， 索引也要动态的维护，降低了数据的维护速度）
在Oracle数据库中按用户名重建索引的方法 如果你管理的Oracle数据库下某些应用项目有大量的修改删除操作 数据索引是需要周期性的重建的
它不仅可以提高查询性能 还能增加索引表空间空闲空间大小
在ORACLE里大量删除记录后 表和索引里占用的数据块空间并没有释放
重建索引可以释放已删除记录索引占用的数据块空间
转移数据 重命名的方法可以重新组织表里的数据
下面是可以按ORACLE用户名生成重建索引的SQL脚本
SET ECHO OFF; SET FEEDBACK OFF; SET VERIFY OFF; SET PAGESIZE ; SET TERMOUT ON; SET HEADING OFF; ACCEPT username CHAR PROMPT Enter the index username: ; spool /oracle/rebuild_username sql; SELECT REM|| chr( ) || REM | INDEX NAME : || owner || || segment_name || lpad( | (length(owner)length(segment_name)) ) || chr( ) || REM | BYTES : || bytes || lpad ( | (length(bytes)) ) || chr( ) || REM | EXTENTS : || extents || lpad ( | (length(extents)) ) || chr( ) || REM|| chr( ) || ALTER INDEX || owner || || segment_name || chr( ) || REBUILD || chr( ) || TABLESPACE || tablespace_name || chr( ) || STORAGE ( || chr( ) || INITIAL || initial_extent || chr( ) || NEXT || next_extent || chr( ) || MINEXTENTS || min_extents || chr( ) || MAXEXTENTS || max_extents || chr( ) || PCTINCREASE || pct_increase || chr( ) || ); || chr( ) || chr( ) FROM dba_segments WHERE segment_type = INDEX AND owner= username ORDER BY owner bytes DESC; spool off;
如果你用的是WINDOWS系统 想改变输出文件的存放目录 修改spool后面的路径成
spool c oraclerebuild_username sql
如果你只想对大于max_bytes的索引重建索引 可以修改上面的SQL语句
在AND owner= username 后面加个限制条件 AND bytes max_bytes
如果你想修改索引的存储参数 在重建索引rebuild_username sql里改也可以
比如把pctincrease不等于零的值改成是零
生成的rebuild_username sql文件我们需要来分析一下 它们是否到了需要重建的程度
分析索引 看是否碎片严重 SQLANALYZE INDEX index_name VALIDATE STRUCTURE; col name heading Index Name format a col del_lf_rows heading Deleted|Leaf Rows format col lf_rows_used heading Used|Leaf Rows format col ratio heading % Deleted|Leaf Rows format SELECT name del_lf_rows lf_rows del_lf_rows lf_rows_used to_char(del_lf_rows / (lf_rows)* ) ratio FROM index_stats where name = upper( index_name );
当删除的比率大于 % 时 肯定是需要索引重建的
经过删改后的rebuild_username sql文件我们可以放到ORACLE的定时作业里
比如一个月或者两个月在非繁忙时间运行
如果遇到ORA 错误 表示索引在的表上有锁信息 不能重建索引
那就忽略这个错误 看下次是否成功
对那些特别忙的表要区别对待 不能用这里介绍的方法
lishixinzhi/Article/program/Oracle/201311/19038
在PL-SQL中如何给oracle数据库重建索引？重建索引有多种方式，如drop and re-create、rebuild、rebuild online等 。下面简单比较这几种方式异同以及优缺点：
首先建立测试表及数据：
SQL CREATE TABLE TEST AS SELECT CITYCODE C1 FROM CITIZENINFO2;
Table created
SQL ALTER TABLE TEST MODIFY C1 NOT NULL;
Table altered
SQL SELECT COUNT(1) FROM TEST;
COUNT(1)
----------
16000000
一、drop and re-create和rebuild
首先看看正常建立索引时，对表的加锁情况 。
suk@ORACLE9I @show_sid
SID
----------
14
suk@ORACLE9I CREATE INDEX IDX_TEST_C1 ON TEST(C1);
索引已创建 。
SQL SELECT OBJECT_NAME,LMODE FROM V$LOCK L,DBA_OBJECTS O WHERE O.OBJECT_ID=L.ID1 AND L.TYPE='TM' AND SID=14;
OBJECT_NAME LMODE
------------------------------ ----------
OBJ$ 3
TEST 4
可见，普通情况下建立索引时，oracle会对基表加share锁，由于share锁和 row-X是不兼容的 ， 也就是说，在建立索引期间，无法对基表进行DML操作 。
对于删除重建索引的方法就不介绍了，它与上面的描述是一样的，下面我们看看用rebuild的方式建立索引有什么特别 。
suk@ORACLE9I ALTER INDEX IDX_TEST_C1 REBUILD;
索引已更改 。
另开一个会话，查询此时test的加锁情况：
SQL SELECT OBJECT_NAME,LMODE FROM V$LOCK L,DBA_OBJECTS O WHERE O.OBJECT_ID=L.ID1 AND L.TYPE='TM' AND SID=14;
OBJECT_NAME LMODE
------------------------------ ----------
TEST 4
可见，rebuild的方式对基表的加锁方式与CREATE时是一样的 。
另开一个会话，在索引正在rebuild时，执行如下SQL：
suk@ORACLE9I SET AUTOTRACE TRACE
suk@ORACLE9I SELECT /*INDEX(TEST) */ COUNT(1) FROM TEST WHERE ROWNUM10;
执行计划
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=26 Card=1)
1 0 SORT (AGGREGATE)
2 1 COUNT (STOPKEY)
3 2 INDEX (FULL SCAN) OF 'IDX_TEST_C1' (NON-UNIQUE) (Cost=
26 Card=1986621)
可以看到索引在重建时，查询仍然可以使用旧索引 。实际上 ， oracle在rebuild时，在创建新索引过程中，并不会删除旧索引，直到新索引rebuild成功 。
从这点可以知道rebuild比删除重建的一个好处是不会影响原有的SQL查询，但也正由于此，用rebuild方式建立索引需要相应表空间的空闲空间是删除重建方式的2倍 。
重建索引有多种方式 ， 如drop and re-create、rebuild、rebuild online等 。下面简单比较这几种方式异同以及优缺点：

    推荐阅读


    上一篇：网络培训go语言，go语言培训班哪个好

    下一篇：服务器显示黄灯 服务器黄灯暂停怎么回事