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

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

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、限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）

相关问答

Q1: 在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

Q2: 在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

----------

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等。下面简单比较这几种方式异同以及优缺点：

oracle重建索引（一）

二、rebuild 和rebuild online

首先我们跟踪一下rebuild online的过程。

另开一个会话查看锁的信息：

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

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

SYS_JOURNAL_10499 4

TEST 2

SQL INSERT INTO TEST VALUES(11);

1 row inserted

SQL COMMIT;

Commit complete

可以看到，在rebuild online期间，oracle对基表加的是RS所，此时我们可以对基表进行DML操作。但奇怪的话在相同的session中有一个SYS_JOURNAL_10499表被加SHARE锁，这个表是干什么用的呢？

我们看看trace文件，有这样的信息：

create table "SUK"."SYS_JOURNAL_10499" (C0 NUMBER(6,0), opcode char(1),

partno number, rid rowid, primary key( C0 , rid )) organization index

TABLESPACE "TEST"

CREATE UNIQUE INDEX "SUK"."SYS_IOT_TOP_10605" on

"SUK"."SYS_JOURNAL_10499"("C0","RID") INDEX ONLY TOPLEVEL TABLESPACE "TEST"

NOPARALLEL

drop table "SUK"."SYS_JOURNAL_10499"

我们在查查10499是什么东西：

SQL SELECT OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_ID=10499;

OBJECT_NAME OBJECT_TYPE

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

IDX_TEST_C1 INDEX

从这些信息可以推测：表SYS_JOURNAL_10499就是实现在重建索引时不阻塞DML操作而设计的，它存储的是在索引重建期间发生在基表的数据变化。可以推测，CREATE INDEX .... ONLINE应该也有一张类似的表。

实际上，oracle之所以在创建索引时锁表阻止DML操作就是为了防止不能索引新变化的数据，在online方式重建时，有了临时表SYS_JOURNAL_XXXX，oracle就可以放心大胆地让用户操作了，因为所有重建索引期间的数据变化信息都会保留在SYS_JOURNAL_XXX表中，当索引重建完后再加上SYS_JOURNAL_XXX记录的数据，就不会漏索引数据了。（XXX是被重建的索引对应的OBJECT_ID)

导读：

重建索引有多种方式，如drop and re-create、rebuild、rebuild online等。下面简单比较这几种方式异同以及优缺点：

oracle重建索引（一）

oracle重建索引（二）

三、rebuild和rebuild online的数据源

网上一直有这样一个说法：重建索引是以原索引作为数据源的。那么，这种说法是否准确呢？我们做实验来验证一下：

suk@ORACLE9I COL SEGMENT_NAME FORMAT A30

--首先看看表和索引的大小

suk@ORACLE9I SELECT SEGMENT_NAME,BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ('TEST','IDX_TEST_C1');

SEGMENT_NAME BYTES

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

TEST 201326592

IDX_TEST_C1 293601280

suk@ORACLE9I EXPLAIN PLAN FOR ALTER INDEX IDX_TEST_C1 REBUILD;

已解释。

suk@ORACLE9I SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT

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

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

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

| 0 | ALTER INDEX STATEMENT | | | | |

| 2 | SORT CREATE INDEX | | | | |

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

Note: rule based optimization

已选择11行。

--从执行计划可以看出，当索引比表大时，rebuild索引用的数据源是基表。

suk@ORACLE9I EXPLAIN PLAN FOR ALTER INDEX IDX_TEST_C1 REBUILD ONLINE;

已解释。

suk@ORACLE9I SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT

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

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

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

| 0 | ALTER INDEX STATEMENT | | | | |

| 2 | SORT CREATE INDEX | | | | |

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

Note: rule based optimization

已选择11行。

--从执行计划可以看出，当索引比表大时，rebuild online索引用的数据源是基表。

--我们为TEST添加一列，使得表比索引大

suk@ORACLE9I ALTER TABLE TEST ADD(C2 CHAR(30) DEFAULT '1');

表已更改。

suk@ORACLE9I SELECT SEGMENT_NAME,BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ('TEST','IDX_TEST_C

1');

SEGMENT_NAME BYTES

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

TEST 1476395008

IDX_TEST_C1 293601280

suk@ORACLE9I EXPLAIN PLAN FOR ALTER INDEX IDX_TEST_C1 REBUILD;

已解释。

suk@ORACLE9I SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT

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

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

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

| 0 | ALTER INDEX STATEMENT | | | | |

| 2 | SORT CREATE INDEX | | | | |

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

Note: rule based optimization

已选择11行。

--从执行计划可以看出，当表比索引大时，执行计划已经改变，rebuild索引是以索引作为数据源的。

suk@ORACLE9I EXPLAIN PLAN FOR ALTER INDEX IDX_TEST_C1 REBUILD ONLINE;

已解释。

suk@ORACLE9I SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT

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

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

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

| 0 | ALTER INDEX STATEMENT | | | | |

| 2 | SORT CREATE INDEX | | | | |

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

Note: rule based optimization

已选择11行。

--从执行计划可以看出，当表比索引大时，rebuild online仍然以基表作为数据源。

rebuild模式下，因为表数据不会产生变化，oracle主要考虑性能问题，把更快扫描完成的段作为数据源。在上面的例子中，我们并没有对表进行分析，故oracle应该根据数据段的大小来决定那个作为数据源的。一般索引字段比较多，或者对索引字段的DML操作较多，可能会导致索引比表大，这时oracle就会使用基表作为新索引的数据源进行rebuild了。

而在rebuild online模式下，因为允许DML操作，而表数据变化的同时索引也会跟着变化，为了索引与基表数据的一致性，比如采用基表数据作为数据源，而不能用原索引数据作为数据源。

我们用反证法证明不能用原索引作为新索引的数据源。

例如：

T1发出rebuild online命令

T2删除某条数据，删数据的同时，oracle会自动维护了旧索引

T3扫描经过T2数据所在索引节点

T4插入一条记录，新记录对应的索引节点刚好重用了T2删除的数据对应的索引节点空间

如果是这样的话，新建的索引将不包含T4插入的记录的信息。所以，rebuild online情况下新索引的数据源不能是原索引。

rebuild online情况下，如果非用原索引作为新索引的数据源的话，用中间表记录索引变化的方法应该是可以实现的，但由于数据变化会同时引起索引变化的特定决定了这种方法将异常复杂及效率底下，所以oracle不考虑旧索引作为新索引的数据源是有道理的。

结论：

1、rebuild会阻塞对基表的DML操作，但不会影响rebuild期间查询对原有索引的使用。

2、rebuild的数据源可能是基表，也可能是原索引。取决于基表和原索引的大小，那个小，rebuild时就会用那个作为数据源。这也说明了网上盛传的rebuild以原索引作为数据库的说法是不完全正确的。

3、rebuild online运行用户在索引重建期间执行DML操作。

4、rebuild online的数据源是基表

Q3: oracle 重建索引

1、重新收集统计信息

analyze table 表名 compute statistics for table for all indexes for all indexed columns

analyze index 索引名 compute statistics

2、把表现不同的sql及其执行计划发上来看看

Q4: oracle数据库如何重建索引

当索引的碎片过多时，会影响执行查询的速度，从而影响到我们的工作效率。这时候采取的最有利的措施莫过于重建索引了。本文主要介绍了Oracle数据库中检查索引碎片并重建索引的过程，接下来我们就开始介绍这一过程。重建索引的步骤如下： 1. 确认基本信息登入数据库，找到专门存放index 的tablespace，并且这个tablespace下所有index的owner都是tax.将index专门存放在一个独立的tablespace, 与数据表的tablespace分离，是常用的数据库设计方法。 2. 查找哪些index需要重建通过anlyze index .... validate structure命令可以分析单个指定的index，并且将单个index 分析的结果存放到 index_stats试图下。一般判断的依据是： height 4 pct_used 50% del_lf_rows / lf_rows +0.001 0.03 g ) 3. google上下载了遍历所有index脚本发现anlyze index .... validate structure只能填充单个index分析信息，于是google了下，从网上下了个Loop 脚本，遍历索引空间下所有的索引名字，并且可以把所有index的分析信息存放到自己建立的一个用户表中。 4. anlyze index 锁定index 发现下载的脚本不好用，应为anlyze index在分析索引前要争取独占锁，锁住index，很明显有些index正在被应用系统的使用，所以运行anlyze失败。这里吸取的教训是，尽量晚上做这种事。但是本人比较喜欢准时回家，所以在语句中添加Exception Handler，抛出anlyze index执行失败的那些index 名称，使脚本正常运行完毕。并且根据打印到前台的index name手动执行那些index分析。 5. 总结虽然发现522个index中有160个符合上面的判断的依据。但是发现索引都不大，而那些拥有百万leaf的索引又没有符合上面的判断条件，所以结论是无需index rebuild online. 没有啥碎片。 6.什么时候可以rebuild index呢? rebuild index online，对那些有大量DML操作的大索引是有益的。可以每个月季度做一次针对较大索引的rebuild。

Q5: 请教：oracle中如何重新创建索引？

------索引名不能重复，如果要重新建同名索引，必须把以前的索引删掉在建索引

-----删除索引

drop index 索引名;

-----创建索引

create index 索引名 on 表名 (表中的字段,表中的字段)

tablespace 指定的表空间

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64K

minextents 1

maxextents unlimited

);

关于oracle怎么重建索引和oracle重建索引会丢失数据吗的介绍到此就结束了，不知道你从中找到你需要的信息了吗？如果你还想了解更多这方面的信息，记得收藏关注本站。

正文

oracle 数据库如何建立索引如何用索引?

相关问答

相关阅读

JQueryinput对象名称，jquery input name

jquery修改当前行name值，jquery修改内容

js中name属性怎么找，js中name和id

html5+a标签name，html5source标签

jquery得到当前name，jquery获取当前对象

c语言中min1是什么意思，c语言中的name

jquery获取当前输入框的值，jquery获取input的name属性

jquery通过name选择器，jquery根据name选择

目录[+]

oracle 数据库如何建立索引 如何用索引?

相关问答

相关阅读

JQueryinput对象名称，jquery input name

jquery修改当前行name值，jquery修改内容

js中name属性怎么找，js中name和id

html5+a标签name，html5source标签

jquery得到当前name，jquery获取当前对象

c语言中min1是什么意思，c语言中的name

jquery获取当前输入框的值，jquery获取input的name属性

jquery通过name选择器，jquery根据name选择

目录[+]

oracle 数据库如何建立索引如何用索引?