高水位线对全表扫描的影响

最新推荐文章于 2021-08-30 13:12:12 发布

转载最新推荐文章于 2021-08-30 13:12:12 发布 · 101 阅读

SQL> create table tt as select * from all_objects;

表已创建。

SQL> analyze table tt compute statistics;

表已分析。

SQL> col blocks for 9999
SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='TT';

BLOCKS EMPTY_BLOCKS   NUM_ROWS
------ ------------ ----------
1032          120      68341

SQL> select count(1) FROM TT;

COUNT(1)
----------
     68341

执行计划
----------------------------------------------------------
Plan hash value: 3133740314

-------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   282   (1)| 00:00:04 |
|   1 | SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TT   | 68341 |   282   (1)| 00:00:04 |
-------------------------------------------------------------------

统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
       1011 consistent gets
          0 physical reads
          0 redo size
        421 bytes sent via SQL*Net to client
        416 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

    SQL> VAR TOTAL_BLOCKS NUMBER
SQL> VAR TOTAL_BYTES NUMBER
SQL> VAR UNUSED_BLOCKS NUMBER
SQL> VAR UNUSED_BYTES NUMBER
SQL> VAR LAST_USED_EXTENT_FILE_ID NUMBER
SQL> VAR LAST_USED_EXTENT_BLOCK_ID NUMBER
SQL> VAR LAST_USED_BLOCK NUMBER
SQL> EXEC DBMS_SPACE.UNUSED_SPACE('SCOTT','TT','TABLE',:TOTAL_BLOCKS,:TOTAL_BYTES,:UNUSED_BLOCKS,:UNUSED_BYTES,:LAST_USED_EXTENT_FILE_ID,:LAST_USED_EXTENT_BLOCK_ID,:LAST_USED_BLOCK);

PL/SQL 过程已成功完成。

SQL> PRINT TOTAL_BLOCKS

TOTAL_BLOCKS
------------
        1152

SQL> PRINT TOTAL_BYTES

TOTAL_BYTES
-----------
    9437184

SQL> PRINT UNUSED_BLOCKS

UNUSED_BLOCKS
-------------
          120

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25989950/viewspace-710728/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25989950/viewspace-710728/