SQL> create table t1 as select * from dba_tables;
Table created.
SQL> create index idx_t1 on t1(owner,table_name) nosegment;
Index created.
SQL> analyze table t1 compute statistics;
Table analyzed.
SQL> analyze index idx_t1 compute statistics;
Index analyzed.
SQL> select segment_name, segment_type, bytes
2 from dba_segments
3 where segment_name = 'IDX_T1';
no rows selected
SQL> select object_name, object_type, status
2 from dba_objects
3 where object_name ='IDX_T1';
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE STATUS
------------------ -------
IDX_T1
INDEX VALID
SQL> select index_name, index_type, status
2 from dba_indexes
3 where index_name ='IDX_T1';
no rows selected
SQL> set autot traceonly;
SQL> select owner,table_name,num_rows
2 from t1
3 where owner='TEST' and table_name='T1';
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=21)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=8 Card=1 Bytes=21)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
45 consistent gets
0 physical reads
0 redo size
335 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> alter session set "_use_nosegment_indexes" = true;
Session altered.
SQL> select owner,table_name,num_rows
2 from t1
3 where owner='TEST' and table_name='T1';
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=21)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1 Bytes
=21)
2 1 INDEX (RANGE SCAN) OF 'IDX_T1' (NON-UNIQUE) (Cost=1 Card
=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
45 consistent gets
0 physical reads
0 redo size
335 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> set autot off;
SQL> alter index idx_t1 rebuild;
alter index idx_t1 rebuild
*
ERROR at line 1:
ORA-08114: can not alter a fake index
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/45259/viewspace-134876/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/45259/viewspace-134876/