更快速統計資料筆數的語法

[tim]

轉貼自 http://delphi.ktop.com.tw/TOPIC.ASP?TOPIC_ID=27391

有關資料庫資料筆數的快速計算, 確實是有顯著的幫助, 請參考,
解決使用 count(*) 的效能較低落的狀況



Sybase :
select O.name,rowcnt(I.doampg) RecCnt from sysobjects O,sysindexes I
where I.id=O.id and I.indid=1 and O.type='U'


MSSQL :
select O.name,I.rowcnt from sysobjects O,sysindexes I
where I.id=O.id and I.indid=1 and O.type='U'

Oracle :

A. Compute statistics collection :
1. use table characterics :
create table with 'monitoring' option. and using PL/SQL
"analyze table [TableName] compute statistics;
2. use dbms_stats packages :
EXECUTE dbms_stats.gather_schema_stats (user);
EXECUTE dbms_stats.gather_table_stats (user,'[TableName]');

B. Get Number of rows from all_tables view.
select table_name,num_rows from all_tables where owner=user;