评估一张表的大小

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

点击(此处)折叠或打开

1. a. 计算表的行数
   
2.  SQL> select count(*) from SCOTT.emp1;
   
3. 
   
4.   COUNT(*)
   
5. ----------
   
6.       3584
   
7.     b. 计算前15位rowid相同的行数，即一块中存储的行数
   
8. select rowid from SCOTT.emp1;
   
9. ------------------
   
10. AAACYSAABAAAG8fACX
    
11. AAACYSAABAAAG8fACY
    
12. AAACYSAABAAAG8fACZ
    
13. AAACYSAABAAAG8fACa
    
14. AAACYSAABAAAG8fACb
    
15. AAACYSAABAAAG8fACc
    
16. AAACYSAABAAAG8fACd
    
17. AAACYSAABAAAG8fACe
    
18. AAACYSAABAAAG8fACf
    
19. ……
    
20. SQL> select count(*) from SCOTT.emp1 where substr(rowid,1,15) = \'AAACYSAABAAAG8f\';
    
21. 
    
22.   COUNT(*)
    
23. ----------
    
24.        160
    
25.     c. 查看标准块的大小，即标准块大小为8K
    
26.     SQL> show parameter cache;
    
27.     
    
28.     NAME TYPE VALUE
    
29.     ------------------------------------ ----------- ------------------------------
    
30.     db_16k_cache_size big integer 0
    
31.     db_2k_cache_size big integer 0
    
32.     db_32k_cache_size big integer 0
    
33.     db_4k_cache_size big integer 0
    
34.     db_8k_cache_size big integer 0
    
35.     db_cache_advice string ON
    
36.     db_cache_size big integer 0
    
37.     db_keep_cache_size big integer 0
    
38.     db_recycle_cache_size big integer 0
    
39.     object_cache_max_size_percent integer 10
    
40.     object_cache_optimal_size integer 102400
    
41.     d. 计算1G中有多少块
    
42.     
    
43.     SQL> select (1024*1024*1024/8) from dual;

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

转载于:http://blog.itpub.net/29598413/viewspace-1140978/