oracle索引大小暴增_Oracle:如何计算索引的大小

上次因为创建索引失败，原因是TEMP临时表空间满，经过测试，索引创建需要的临时表空间大概是索引的大小，所以在执行alter index

index_name rebuild online nologging parallel

5语句前，要计算出创建索引需要的临时段的大小。实验如下：

SQL> select * from v$version;

BANNER

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

Oracle

Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release

10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for Linux:

Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 -

Production

SQL> create table test (name

varchar2(4000));

Table created.

SQL> begin

2 for i in 1..10000 loop

3 insert

into test values(i);

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> select sum(bytes)/1024 kb from user_extents

2

where segment_name='TEST';

KB

----------

192

SQL> select sum(bytes)/1024 kb from user_extents

2

where segment_name='IX_NAME';

KB

----------

256

索引比表大！

实验到了这里不得不说一下rowid这个伪列。

SQL> select rowid from test where rownum=1;

ROWID

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

AAACgSAAEAAAABUAAA

An extended rowid has a four-piece format, OOOOOOFFFBBBBBBRRR:

OOOOOO: The data object

number that identifies the database segment (AAAAao in the example). Schema objects in the same

segment, such as a cluster of tables, have the same data object number.

FFF: The tablespace-relative datafile number of the datafile that contains the row (file

AAT in the example).

BBBBBB: The data

block that contains the row (block AAABrX in the example). Block numbers are relative to

their datafile, not tablespace. Therefore, two rows with

identical block numbers could reside in two different datafiles of the same

tablespace.

RRR: The row in

the block.

SQL> select dbms_rowid.rowid_object('AAACgSAAEAAAABUAAA') from

dual;

DBMS_ROWID.ROWID_OBJECT('AAACGSAAEAAAABUAAA')

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

10258

SQL> col object_name for a30

SQL> r

1* select

object_name,object_type from user_objects where object_id=10258

OBJECT_NAME

OBJECT_TYPE

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

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

TEST TABLE

对，就是这样做的，让我们继续查看其他部分的内容

SQL> l

1 select dbms_rowid.rowid_object(rowid)

object_id,

2 dbms_rowid.rowid_relative_fno(rowid) file_id,

3

dbms_rowid.rowid_block_number(rowid) block_id ,

4

dbms_rowid.rowid_row_number(rowid) num

5* from test where

rownum<=5

SQL> /

OBJECT_ID FILE_ID BLOCK_ID NUM

----------

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

10258 4 84

0

10258 4 84 1

10258

4 84 2

10258 4 84 3

10258 4 84 4

所以，通过rowid就可以准备地找到每行数据存储在哪个oracle

block上，索引就是保存了rowid和索引列值之间的对应关系并按照顺序排列，提前查询时候的要将不用全表扫描，能快速找到查询关键字对应的rowid是提高查询速度的关键。那么我们应该明白了这个索引为什么比表还要大的原因——索引中保存了rowid的内容，而表是不保存这个值的。