有多大数据量呀?
你可以通过建索引,然后在查询语句中使用索引的方法来查询数据的呀,如:按专卖店查询
SELECT /*+RULE*/ TEMP.GDSID, ---这里就用了RULE这个索引
CUSTOMER.NAME as NAME,
SUM(SALEREPORTBODY.QTY) as QTY,
SUM(SALEREPORTBODY.AMOUNT) AS AMOUNT (金额可选)
FROM SALEREPORTHEAD,SALEREPORTBODY ,CUSTOMER,
(SELECT GDSINID,SUBSTR(GDSID,1,2) AS GDSID
FROM GOODS
WHERE GDSID LIKE '%%') TEMP
你可以通过建索引,然后在查询语句中使用索引的方法来查询数据的呀,如:按专卖店查询
SELECT /*+RULE*/ TEMP.GDSID, ---这里就用了RULE这个索引
CUSTOMER.NAME as NAME,
SUM(SALEREPORTBODY.QTY) as QTY,
SUM(SALEREPORTBODY.AMOUNT) AS AMOUNT (金额可选)
FROM SALEREPORTHEAD,SALEREPORTBODY ,CUSTOMER,
(SELECT GDSINID,SUBSTR(GDSID,1,2) AS GDSID
FROM GOODS
WHERE GDSID LIKE '%%') TEMP
用关联查询代替子查询吧,结合索引的使用,应该会很快。
select ia_name inst_name,ia_type inst_type,sum(co) total,sum(subject1) subject1,sum(subject2) subject2
from (
Select ia_name,ia_type,decode(fnum,'0',co,0) co,decode(fnum,'subject1',co,0) subject1,decode(fnum,'subject2',co,0) subject2
from (
select distinct b.ia_name,b.ia_type,'0' as fnum,count(*) as co
from (select * from article where cite=1 and core_flag=1 and year='2002') a,v_org_inst_1 b
where a.internal_id = b.internal_parent_id
group by b.ia_name,b.ia_type,'0'
union all
select distinct b.ia_name,b.ia_type,'subject1' as fnum,count(*) as co
from (select * from article where cite=1 and core_flag=1 and year='2002' and (';'||classi_num like '%;o1%' or ';'||classi_num like '%;o2%')) a,v_org_inst_1 b
where a.internal_id = b.internal_parent_id
group by b.ia_name,b.ia_type,'subject1'
union all
select distinct b.ia_name,b.ia_type,'subject2' as fnum,count(*) as co
from (select * from article where cite=1 and core_flag=1 and year='2002' and (';'||classi_num like '%;o3%' or ';'||classi_num like '%;o4%' or ';'||classi_num like '%;o5%')) a,v_org_inst_1 b
where a.internal_id = b.internal_parent_id
group by b.ia_name,b.ia_type,'subject2'
)
) where ia_name in ('北京大学','北京大学分校') group by ia_name,ia_type
其中视图v_org_inst_1为:
create or replace view v_org_inst_1 as
select distinct author.internal_parent_id,nvl(ia.n1,author.organization) ia_name,ia.ia_type
from author,
(select inst_auth.ia_name n1,inst_auth.ia_type,inst.i_name n2
from inst,inst_auth
where inst.ia_id = inst_auth.ia_id
union
select inst_auth.ia_name n1,inst_auth.ia_type,inst_auth.ia_name n2
from inst_auth) ia
where ia.n2 (+)= author.organization and author.author_sno='1'
union
select distinct author2.internal_parent_id,nvl(ia.n1,author2.organization) ia_name,ia.ia_type
from author2,
(select inst_auth.ia_name n1,inst_auth.ia_type,inst.i_name n2
from inst,inst_auth
where inst.ia_id = inst_auth.ia_id
union
select inst_auth.ia_name n1,inst_auth.ia_type,inst_auth.ia_name n2
from inst_auth) ia
where ia.n2 (+)= author2.organization and author2.author_sno='1'现将视图中的
select inst_auth.ia_name n1,inst_auth.ia_type,inst.i_name n2
from inst,inst_auth
where inst.ia_id = inst_auth.ia_id
union
select inst_auth.ia_name n1,inst_auth.ia_type,inst_auth.ia_name n2
from inst_auth
数据(大约20万条)插入一个表中,再为这个表的列i_name上建上索引,整个SQL查询语句性能就快了,由原来的40分钟变成了2分钟.
你说TEMP.GDSIDSELECT 是rule表的索引名吗?你说的是不是在子查询语句的结果集,中再产生索引还是只要在源表中建立索引就行了?我的子查询语句还得与别的表进行关联。你能不能再看一下,我发上来的查询语句。再次谢谢。
在select distinct b.ia_name,b.ia_type,'0' as fnum,count(*) as co
from (select * from article where cite=1 and core_flag=1 and year='2002') a,v_org_inst_1 b
where a.internal_id = b.internal_parent_id
group by b.ia_name,b.ia_type,'0'
片段中:
1、distinct 是没有必要的,有了group by ...,b.ia_name,b.ia_type就已经是distinct的了.
2、(select * from article where cite=1 and core_flag=1 and year='2002')
这个子查询没有必要使用,直接查询就可以了,如果在连接的字段建了索引,可以大大加快速度,而如果用子查询,那么a.internal_id字段上的索引用不上.
对你的这段修改如下:
select b.ia_name,b.ia_type,'0' as fnum,count(*) as co
from article a,v_org_inst_1 b
where a.internal_id = b.internal_parent_id and
a.cite=1 and a.core_flag=1 and a.year='2002'
group by b.ia_name,b.ia_type,'0'
b表作为主表;依次类推,
若SQL实在太大,可以把SQL分解,然后通过存储过程,触发器等来实现功能。
条件 ';'||classi_num like '%;o1%'应该等价于条件substr(classi_num,1,2)='o1',也就是说classi_num的前两位字符是'o1'。那么你的两句sql可以简化成为:
SELECT b.ia_name, b.ia_type, COUNT (*),
SUM (DECODE (SUBSTR (classi_num, 1, 2), 'o1', 1, 'o2', 1, 0)),
SUM (DECODE (SUBSTR (classi_num, 1, 2),
'o3', 1,
'o4', 1,
'o5', 1,
0
))
FROM article a, v_org_inst_1 b
WHERE a.internal_id = b.internal_parent_id
AND a.cite = 1
AND core_flag = 1
AND YEAR = '2002'
AND ia_name IN ('北京大学','北京大学分校')
GROUP BY b.ia_name, b.ia_type;CREATE OR REPLACE VIEW v_org_inst_1
AS
SELECT x.internal_parent_id, NVL (ia.n1, x.ORGANIZATION) ia_name,
ia.ia_type
FROM (SELECT c.internal_parent_id, ORGANIZATION
FROM author c
WHERE author_sno = '1'
UNION
SELECT c.internal_parent_id, ORGANIZATION
FROM author2 c
WHERE author_sno = '1') x,
(SELECT a.ia_name n1, a.ia_type, NVL (b.i_name, a.ia_name) n2
FROM inst b, inst_auth a
WHERE a.ia_id = b.ia_id(+)) ia
WHERE x.ORGANIZATION = ia.n2(+);优化基本原则就是尽量减少对表的访问,而你的语句中出现了多次重复,效率不会好。