数据库为Oracle 9 ,该人口表大概有60万条记录。人口表ACCOUNTMEMBER 部分字段:个人编码 区域码 记录状态 删除日期 家庭编码 个人姓名
person_id orgcode statusno person_df househoulderid name 当person_id = househoulderid 时为户主
statusno =0 以及person_df 为null 的记录为可操作记录。现想查询该人口库中的所有户主记录。我写的SQL语句是:
select b.* ,a.count from (
select t.householderid,count(*) count
from ACCOUNTMEMBER t
where ORGCODE like '5101810403%'
and t.statusno = '0'
and t.person_df is null
group by t.householderid ) a,
ACCOUNTMEMBER b
where b .person_id = a.householderid
order by b.name
这个语句执行起来很慢,在数据库中就要21秒左右。导致使用该语句的模块要好几份钟才能执行完。请问各位大侠有没有效率高一些的方法呢,多谢啦!注:表中已有三个索引为: 索引名 字段
IND_NAME NAME
IND_ORGCODE ORGCODE
PK_ACCOUNTMEMBER PERSON_ID
person_id orgcode statusno person_df househoulderid name 当person_id = househoulderid 时为户主
statusno =0 以及person_df 为null 的记录为可操作记录。现想查询该人口库中的所有户主记录。我写的SQL语句是:
select b.* ,a.count from (
select t.householderid,count(*) count
from ACCOUNTMEMBER t
where ORGCODE like '5101810403%'
and t.statusno = '0'
and t.person_df is null
group by t.householderid ) a,
ACCOUNTMEMBER b
where b .person_id = a.householderid
order by b.name
这个语句执行起来很慢,在数据库中就要21秒左右。导致使用该语句的模块要好几份钟才能执行完。请问各位大侠有没有效率高一些的方法呢,多谢啦!注:表中已有三个索引为: 索引名 字段
IND_NAME NAME
IND_ORGCODE ORGCODE
PK_ACCOUNTMEMBER PERSON_ID
a.*,count(b.householderid) as count
from
ACCOUNTMEMBER a,
ACCOUNTMEMBER b
where
a.person_id=b.householderid
and
b.orgcode like '5101810403%'
and
b.statusno = '0'
and
b.person_df is null
group by
a.name
a.*,count(b.householderid) as count
from
ACCOUNTMEMBER a,
ACCOUNTMEMBER b
where
a.person_id=b.householderid
and
b.orgcode like '5101810403%'
and
b.statusno = '0'
and
b.person_df is null
group by
a.name,a.....
但是你的sql语句有问题。
1、count(b.householderid) as count ----提示 ora-00937 非单组分组函数错误
2、group by a.name ----- 提示 ora-00979 不是 GROUP BY 表达式
START WITH...CONNECT BY手头没有环境测试,你查查看.
from ACCOUNTMEMBER a
where a.person_id = a.householderid
and a.statusno = '0'
and a.person_df is null
and a.orgcode like '5101810403%';
得到数据一直是1,不知道为什么?
另外,楼主,能否把你的执行计划帖出来让大家看看?另外.b .person_id = a.householderid
这两个列是什么关系?
liuyi8903(ocp->ocm)b .person_id = a.householderid
是为了定位户主记录。当person_id = househoulderid 时为户主至于你说的执行计划,指的是什么啦?
小弟愚昧。
person_id orgcode statusno person_df househoulderid name 当person_id = househoulderid 时为户主
statusno =0 以及person_df 为null 的记录为可操作记录。
现想查询该人口库中的所有户主记录。select * from ACCOUNTMEMBER t where t.person_id = t.househoulderid;就可以了。
select * from table(dbms_xplan.display());生成的结果就是了.
orgcode,
statusno,
person_df,
househoulderid,
name,
count(*)over(partition by househoulderid) "count"
from ACCOUNTMEMBER
where statusn='0'
and person_df is null
and person_id=househoulderid
order by name