最初由 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 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
----------------------------------------------------------
0SELECT STATEMENT Optimizer=ALL_ROWS (Cost=12 Card=5998 B
=155948)
10 PARTITION RANGE (ALL) (Cost=12 Card=5998 Bytes=155948)
21 PARTITION HASH (ALL) (Cost=12 Card=5998 Bytes=155948
32 TABLE ACCESS (FULL) OF 'A' (TABLE) (Cost=12 Card=5
Bytes=155948)
Statistics
----------------------------------------------------------
168recursive calls
0db block gets
599consistent gets
10physical reads
0redo size
188843bytes sent via SQL*Net to client
4884bytes received via SQL*Net from client
401SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
5998rows processed
SQL> select * from a where a=2;
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes
10 PARTITION RANGE (SINGLE) (Cost=1 Card=1 Bytes=26)
21 PARTITION HASH (SINGLE) (Cost=1 Card=1 Bytes=26)
32 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'A' (TABLE)
st=1 Card=1 Bytes=26)
43 INDEX (RANGE SCAN) OF 'I' (INDEX) (Cost=1 Card=1
Statistics
----------------------------------------------------------
13recursive calls
0db block gets
23consistent gets
0physical reads
0redo size
395bytes sent via SQL*Net to client
495bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows 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行。
|