SQL> select count(distinct b) from
2 (select dbms_rowid.rowid_block_number(rowid) b from t);
COUNT(DISTINCTB)
----------------
80
SQL> select object_id,object_name from t where object_id=6318;
OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
6318
T2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=9 Card=1 Bytes=19)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=1 Bytes=19)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
80 consistent gets
0 physical reads
0 redo size
443 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> delete from t where rownum<3000; --删除3000条记录 总共6174条记录
2999 rows deleted.
sql>commit
SQL> select object_id,object_name from t where object_id=6318;
OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
6318
T2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=9 Card=1 Bytes=19)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=9 Card=1 Bytes=19)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
80 consistent gets --删除后全表扫描还是和原来一样大
0 physical reads
0 redo size
443 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter tablespace tools read write;
Tablespace altered.
SQL> alter table t move tablespace tools; 把表t移到另外个表空间tools 重新组织块
Table altered.
SQL> select count(distinct b) from
2 (select dbms_rowid.rowid_block_number(rowid) b from t);
COUNT(DISTINCTB)
----------------
40
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'T'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
44 consistent gets --一致性读降低
40 physical reads
60 redo size
387 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed