高分求优化SQL语句(200分)

J

jetcoyuan

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-31

• #1

select count(*) from dic_mat_type a ,pro_fin_inventory b,dic_um c,dic_wh_type d
where a.code(+) = b.mat_type and c.code(+)=b.unit and d.code= b.wh_code
and a.role_no in (select role_no from ctl_user_right where userid ='0011')
and d.code in (select wh_code from dic_region_inv_role where role_no
in (select role_no from ctl_user_right where userid ='0011'))

执行上述语句10000条记录要将近1分钟，如何优化

 

Z

zhoujg

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-31

• #2

改用临时表或者用存储过程

 

风

风中流云

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-31

• #3

oracle:
select count(rowid) form ....................

 

Z

zitai

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-31

• #4

将where 条件中的in中的（select。。。。）去掉。那个条件放在第一个中.如
select count(*) from dic_mat_type a ,pro_fin_inventory b,dic_um c,dic_wh_type d,ctl_user_right e,dic_region_inv_role f
where a.code(+) = b.mat_type and c.code(+)=b.unit and d.code= b.wh_code and (e. =) and (userid ='0011')
and (a.roleid=e.roleid)...........最好将这几个表的关系图给我！！！

 

天

天真

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-31

• #5

用左链接或右链接！

 

美

美国提子

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-31

• #6

用存储过程

 

U

ugvanxk

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-31

• #7

改为存储过程

 

S

samxsy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-31

• #8

不曉得你要的是什么樣的結果，下面的只是做個參考，如果用得著左連接的話，建義用一下左連接
select
count(*)
from
dic_mat_type a ,
pro_fin_inventory b,
dic_um c,
dic_wh_type d,
(select role_no from ctl_user_right where userid ='0011') e,
(select wh_code,role_no from dic_region_inv_role) f
where
a.code(+) = b.mat_type
and c.code(+)=b.unit
and d.code= b.wh_code
and a.role_no = e.role_no
and e.role_no = f.role_no
and d.code = f.wh_no

 

J

jetcoyuan

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-31

• #9

请给出具体语句

 

代

代天

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-02-01

• #10

要想更快，最好用临时表或存储过程

 

L

llh_lily

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-02-01

• #11

可用存储过程实现：
select count(*)
from (select Tole_no from dic_mat_type
where role_no in
(select role_no from ctl_user_right where userid ='0011')) as a ,
pro_fin_inventory b,
dic_um c,
(select code from dic_wh_type
where code in
(select wh_code from dic_region_inv_role where role_no
in (select role_no from ctl_user_right where userid ='0011'))) as d
where a.code(+) = b.mat_type
and c.code(+)=b.unit
and d.code= b.wh_code

 

J

jetcoyuan

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-02-26

• #12

接受答案了.