Oracle复合分区索引,如何确定复合分区索引的状态？

最初由 Toms_zhang 发布

[B]

不正常，已经失效了，需要rebuild

否则会报错的 [/B]

u think so?

C:\Documents and Settings\Roger>sqlplus roger/maggie@ras

SQL*Plus: Release 9.2.0.4.0 - Production on 星期日 3月 5 11:12:04

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved

连接到:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc

With the Partitioning, OLAP and Data Mining options

SQL> select index_name from user_ind_subpartitions where rownum<2;

INDEX_NAME

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

I

SQL> select table_name,status from user_indexes where index_name='

TABLE_NAME                     STATUS

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

A                              N/A

SQL> set autot trace exp stat

SQL> select * from a where a>2;

已选择5998行。

Execution Plan

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

0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=12 Card=5998 B

=155948)

1    0   PARTITION RANGE (ALL) (Cost=12 Card=5998 Bytes=155948)

2    1     PARTITION HASH (ALL) (Cost=12 Card=5998 Bytes=155948

3    2       TABLE ACCESS (FULL) OF 'A' (TABLE) (Cost=12 Card=5

Bytes=155948)

Statistics

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

168  recursive calls

0  db block gets

599  consistent gets

10  physical reads

0  redo size

188843  bytes sent via SQL*Net to client

4884  bytes received via SQL*Net from client

401  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

5998  rows processed

SQL> select * from a where a=2;

Execution Plan

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

0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes

1    0   PARTITION RANGE (SINGLE) (Cost=1 Card=1 Bytes=26)

2    1     PARTITION HASH (SINGLE) (Cost=1 Card=1 Bytes=26)

3    2       TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'A' (TABLE)

st=1 Card=1 Bytes=26)

4    3         INDEX (RANGE SCAN) OF 'I' (INDEX) (Cost=1 Card=1

Statistics

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

13  recursive calls

0  db block gets

23  consistent gets

0  physical reads

0  redo size

395  bytes sent via SQL*Net to client

495  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> set autot off

SQL> select subpartition_name from user_tab_subpartitions where table_name='A';

SUBPARTITION_NAME

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

SYS_SUBP21

SYS_SUBP22

SYS_SUBP23

SYS_SUBP24

SYS_SUBP25

SYS_SUBP26

SYS_SUBP27

SYS_SUBP28

已选择8行。

SQL> alter table a move subpartition SYS_SUBP21;

表已更改。

SQL> select table_name,status from user_indexes where index_name='I';

TABLE_NAME                     STATUS

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

A                              N/A

SQL> select index_name,status from user_ind_subpartitions where index_name='I';

INDEX_NAME                     STATUS

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

I                              UNUSABLE

I                              USABLE

I                              USABLE

I                              USABLE

I                              USABLE

I                              USABLE

I                              USABLE

I                              USABLE

已选择8行。