oracle11g 创建索引,ORACLE11G，系统分区表的本地索引，表没数据还，索引尺寸却很大？...

ORACLE11203，LINUX64

1  系统分区表，2个分区，

SQL>select partitioning_type from user_part_tables where table_name='SYS_CUSTPRICE_20140429';

PARTITION

---------

SYSTEM

Elapsed: 00:00:00.11

SQL>select partition_name from user_tab_partitions where table_name='SYS_CUSTPRICE_20140429';

PARTITION_NAME

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

PART_TODAY

PART_YESTERDAY

2 rows selected.

Elapsed: 00:00:00.18

2  表上有一本地索引，

SQL>select index_name, partitioning_type, locality from user_part_indexes where table_name='SYS_CUSTPRICE_20140429';

INDEX_NAME                     PARTITION LOCALI

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

IDX_CUSTPRICE2_1055            SYSTEM    LOCAL

Elapsed: 00:00:00.19

3  某个分区的数据量为0

SQL>select count(*) from sys_custprice_20140429 partition (part_yesterday);

COUNT(*)

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

0

Elapsed: 00:00:00.11

4  该分区对应的本地分区索引，状态不可用。

SQL>select a.index_name,a.partition_name,a.status from user_ind_partitions a, user_indexes b

2               where a.index_name=b.index_name and b.table_name='SYS_CUSTPRICE_20140429' and a.partition_name='PART_YESTERDAY';

INDEX_NAME                     PARTITION_NAME                 STATUS

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

IDX_CUSTPRICE2_1055            PART_YESTERDAY                 UNUSABLE

Elapsed: 00:00:00.23

5  于是重建该分区的本地索引，却发现，耗时良久，很纳闷？不是没数据吗，怎么这么耗时？

SQL>alter index IDX_CUSTPRICE2_1055 rebuild partition PART_YESTERDAY;

Index altered.

Elapsed: 00:11:23.76

6  查看本地分区索引尺寸，发现个头很大，问：既然该分区没数据，为何对应的本地分区索引还这么大(好像有数据)？

SQL>select bytes/1024/1024 from user_segments where segment_name='IDX_CUSTPRICE2_1055' and partition_name='PART_YESTERDAY';

BYTES/1024/1024

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

4133

Elapsed: 00:00:00.16