问题:删除表数据不会导致高水位线下降,不会使用查询效率,
模拟:
创建表,分析表
SQL> create table TEST as select * from TESTT1; Table created. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('c##sapr3',tabname =>'TEST',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>4,cascade=>TRUE); PL/SQL procedure successfully completed. SQL> select num_rows,blocks,empty_blocks from dba_tables where table_name='TEST'; NUM_ROWS BLOCKS EMPTY_BLOCKS ---------- ---------- ------------ 1236277 6947 0 SQL> set autotrace traceonly
SQL> select * from TEST;
1236277 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1236K| 41M| 2235 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 1236K| 41M| 2235 (1)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
88809 consistent gets
6829 physical reads
0 redo size
43475416 bytes sent via SQL*Net to client
906978 bytes received via SQL*Net from client
82420 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1236277 rows processed
SQL> set autotrace off
删除部分数据:
SQL> DELETE from TEST where MANDT='000'; 1113248 rows deleted. SQL> commit; Commit complete. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('c##sapr3',tabname =>'TEST',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>4,cascade=>TRUE); PL/SQL procedure successfully completed. SQL> select num_rows,blocks,empty_blocks from dba_tables where table_name='TEST'; NUM_ROWS BLOCKS EMPTY_BLOCKS ---------- ---------- ------------ 123029 6947 0
可以看到NUM_ROWS 减少,但BLOCKS仍然和原来的一样是6947
rows processed 1236277 -->123029
consistent gets 逻辑读 88809 -->14995
SQL> set autotrace traceonly SQL> select * from TEST; 123029 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 123K| 4325K| 2225 (1)| 00:00:01 | | 1 | TABLE ACCESS FULL| TEST | 123K| 4325K| 2225 (1)| 00:00:01 | -------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 14995 consistent gets 0 physical reads 0 redo size 3828471 bytes sent via SQL*Net to client 90591 bytes received via SQL*Net from client 8203 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 123029 rows processed SQL> set autotrace off
收缩表
SQL> alter table TEST enable row movement; Table altered. SQL> alter table TEST shrink space cascade; Table altered. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('c##sapr3',tabname =>'TEST',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>4,cascade=>TRUE); PL/SQL procedure successfully completed. SQL> select num_rows,blocks,empty_blocks from dba_tables where table_name='TEST'; NUM_ROWS BLOCKS EMPTY_BLOCKS ---------- ---------- ------------ 123029 704 0
可以看到表中的BLOCKS由6947 减少到704
继续压缩
SQL> ALTER TABLE TEST move compress; Table altered. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('c##sapr3',tabname =>'TEST',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>4,cascade=>TRUE); PL/SQL procedure successfully completed. SQL> select num_rows,blocks,empty_blocks from dba_tables where table_name='TEST'; NUM_ROWS BLOCKS EMPTY_BLOCKS ---------- ---------- ------------ 123029 289 0
可以看到表中的BLOCKS由704减少到289
再次进行查询
SQL> set autotrace traceonly SQL> select * from TEST; 123029 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 123K| 4325K| 95 (2)| 00:00:01 | | 1 | TABLE ACCESS FULL| TEST | 123K| 4325K| 95 (2)| 00:00:01 | -------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 8465 consistent gets 0 physical reads 0 redo size 3841745 bytes sent via SQL*Net to client 90591 bytes received via SQL*Net from client 8203 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 123029 rows processed
可以看到 consistent 由 14995减少到 8465