从翟老师那学到的评估一张表大小的小技巧

点击(此处)折叠或打开

a. 计算表的行数
SQL> select count(*) from SCOTT.emp1;
COUNT(*)
----------
3584
b. 计算前15位rowid相同的行数，即一块中存储的行数
select rowid from SCOTT.emp1;
------------------
AAACYSAABAAAG8fACX
AAACYSAABAAAG8fACY
AAACYSAABAAAG8fACZ
AAACYSAABAAAG8fACa
AAACYSAABAAAG8fACb
AAACYSAABAAAG8fACc
AAACYSAABAAAG8fACd
AAACYSAABAAAG8fACe
AAACYSAABAAAG8fACf
……
SQL> select count(*) from SCOTT.emp1 where substr(rowid,1,15) = \'AAACYSAABAAAG8f\';
COUNT(*)
----------
160
c. 查看标准块的大小，即标准块大小为8K
SQL> show parameter cache;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_advice string ON
db_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
d. 计算1G中有多少块
SQL> select (1024*1024*1024/8) from dual;

ITPub博客

评估一张表的大小