海量数据查询快速估计总数方法

估算大概值可以分析执行计划信息
sql：
EXPLAIN PLAN FOR
select * 
from xx t where 1=1  
and  date1 >= to_date('2011-12-09 00:00:15','yyyy-MM-dd hh24:mi:ss')
and   date2 <= to_date('2011-12-19 00:00:40','yyyy-MM-dd hh24:mi:ss');

select * from table(DBMS_XPLAN.DISPLAY);



***********************************************************************

java方法：

/**
*
* 快速估算大约总记录数
* @param 查询sql
* @return 大约总数记录数
*/
public int getComputeTotal(String sql) {
Statement st = null;
ResultSet reset = null;
int computeTotal=0;
try {
String sqlexplan = "EXPLAIN PLAN FOR "+sql;
Connection newconnect = getConnection(true);
st = newconnect.createStatement();
        st.execute(sqlexplan);
st = newconnect.createStatement();
reset = st.executeQuery("select t.plan_table_output from table(DBMS_XPLAN.DISPLAY) t");
String rowString="";
int i=0;
while (reset.next()) {
if(i==5){
rowString=reset.getString("plan_table_output");
break;
}
i++;
}
if(rowString!=null&&!"".equals(rowString)){
rowString=rowString.replaceAll(" ", "");
String rows[]=rowString.split("\\|");
if(rows[4].endsWith("K")){
rows[4]=rows[4].substring(0, rows[4].length()-1);
computeTotal=Integer.valueOf(rows[4])*1000;
}else if(rows[4].endsWith("M")){
rows[4]=rows[4].substring(0, rows[4].length()-1);
computeTotal=Integer.valueOf(rows[4])*1000*1000;
}else if(rows[4].endsWith("T")){
rows[4]=rows[4].substring(0, rows[4].length()-1);
computeTotal=Integer.valueOf(rows[4])*1000*1000*1000;
}else{
computeTotal=Integer.valueOf(rows[4]);
}
}
reset.close();
} catch (Exception e) {
logger.error(e);
} finally {
this.close(this.connect, st, reset);
}
return computeTotal;
}
*******************************************************
分页处需要修改：

int computeTotal=getComputeTotal(sqlall);
if(computeTotal<100000){
String countsql = "select count(*)  from xx t where 1=1  "+sqllike+"";
int count = 0;
st = getConnection().createStatement();
reset = st.executeQuery(countsql);
while(reset.next()){
count = reset.getInt(1);
}
pager.setTotal(count);
pager.setPageTotal(count/pager.getPageSize());
}else{
pager.setTotal(computeTotal);
pager.setPageTotal(computeTotal/pager.getPageSize());
}

已经在3亿数据量数据库做过测试

附快速统计某个表总记录数sql：
select table_name,num_rows from dba_tables where table_name ='XX'