估算大概值可以分析执行计划信息
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'
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'