确定表是否存在高水位问题

ゝ一世哀愁。 2022-03-07 11:24 225阅读 0赞

How to determine the high water mark

\------------------------------------

To view the high water mark of a particular table::

ANALYZE TABLE <tablename> ESTIMATE/COMPUTE STATISTICS;

This will update the table statistics. After generating the statistics,

to determine the high water mark:

SELECT blocks, empty\_blocks, num\_rows

FROM   user\_tables

WHERE table\_name = <tablename>;

BLOCKS represents the number of blocks 'ever' used by the segment.

EMPTY\_BLOCKS represents only the number of blocks above the 'HIGH WATER MARK'

Deleting records doesn't lower the high water mark. Therefore, deleting

records doesn't raise the EMPTY\_BLOCKS figure.

Let us take the following example based on table BIG\_EMP1 which

has 28672 rows (Oracle 8.0.6):

SQL> connect system/manager

Connected.

SQL> SELECT segment\_name,segment\_type,blocks

2> FROM dba\_segments

3> WHERE segment\_name='BIG\_EMP1';

SEGMENT\_NAME                  SEGMENT\_TYPE      BLOCKS      EXTENTS

\----------------------------- ----------------- ----------  -------

BIG\_EMP1                      TABLE                   1024      2

1 row selected.

SQL> connect scott/tiger

SQL> ANALYZE TABLE big\_emp1 ESTIMATE STATISTICS;

Statement processed.

SQL> SELECT table\_name,num\_rows,blocks,empty\_blocks

2> FROM user\_tables

3> WHERE table\_name='BIG\_EMP1';

TABLE\_NAME                     NUM\_ROWS   BLOCKS     EMPTY\_BLOCKS

\------------------------------ ---------- ---------- ------------

BIG\_EMP1                            28672        700        323

1 row selected.

Note: BLOCKS + EMPTY\_BLOCKS (700+323=1023) is one block less than

DBA\_SEGMENTS.BLOCKS. This is because one block is reserved for the

segment header. DBA\_SEGMENTS.BLOCKS holds the total number of blocks

allocated to the table. USER\_TABLES.BLOCKS holds the total number of

blocks allocated for data.

SQL> SELECT COUNT (DISTINCT

2>          DBMS\_ROWID.ROWID\_BLOCK\_NUMBER(rowid)||

3>          DBMS\_ROWID.ROWID\_RELATIVE\_FNO(rowid)) "Used"

4> FROM big\_emp1;

Used

\----------

700

1 row selected.

SQL> DELETE from big\_emp1;

28672 rows processed.

SQL> commit;

Statement processed.

SQL> ANALYZE TABLE big\_emp1 ESTIMATE STATISTICS;

Statement processed.

SQL> SELECT table\_name,num\_rows,blocks,empty\_blocks

2> FROM user\_tables

3> WHERE table\_name='BIG\_EMP1';

TABLE\_NAME                     NUM\_ROWS   BLOCKS     EMPTY\_BLOCKS

\------------------------------ ---------- ---------- ------------

BIG\_EMP1                                0        700        323

1 row selected.

SQL> SELECT COUNT (DISTINCT

2>          DBMS\_ROWID.ROWID\_BLOCK\_NUMBER(rowid)||

3>          DBMS\_ROWID.ROWID\_RELATIVE\_FNO(rowid)) "Used"

4> FROM big\_emp1;

Used

\----------

1 row selected.

SQL> TRUNCATE TABLE big\_emp1;

Statement processed.

SQL> ANALYZE TABLE big\_emp1 ESTIMATE STATISTICS;

Statement processed.

SQL> SELECT table\_name,num\_rows,blocks,empty\_blocks

2> FROM user\_tables

3> WHERE table\_name='BIG\_EMP1';

TABLE\_NAME                     NUM\_ROWS   BLOCKS     EMPTY\_BLOCKS

\------------------------------ ---------- ---------- ------------

BIG\_EMP1                                0          0        511

1 row selected.

SQL> connect system/manager

Connected.

SQL> SELECT segment\_name,segment\_type,blocks

2> FROM dba\_segments

3> WHERE segment\_name='BIG\_EMP1';

SEGMENT\_NAME                  SEGMENT\_TYPE      BLOCKS      EXTENTS

\----------------------------- ----------------- ----------  -------

BIG\_EMP1                      TABLE                   512      1

1 row selected.

Note: TRUNCATE has also deallocated the space from the deleted rows.

To retain the space from the deleted rows allocated to the table use:

TRUNCATE TABLE big\_emp1 REUSE STORAGE

确定表是否存在高水位问题

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Mybatis检测mysql表是否存在

相关 mysql查询表是否存在

相关 sqlite判断表是否存在

相关 Oracle表碎片查询以及整理（高水位线）

相关 sql判断表、字段是否存在

相关高水位线和全表扫描

相关高水位线和全表扫描

相关确定表是否存在高水位问题

相关 mysql 中判断表是否存在以及表存在则删除

相关判断临时表/正式表是否存在？