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的内容，而表是不保存这个值的。