select username
from (select distinct substr(c.circuitname,
0,
(instr(c.circuitname, '_', 1, 1)) - 1) username
from node n, userinfo u, device d, portinfo p, circuit c
where u.netuserid = 'admin'
and n.nodefullcode like '%' || u.nodecode || '%'
and d.nodecode = n.nodecode
and d.changetype = 0
and c.changetype = 0
and p.deviceid = d.deviceid
and ((c.adeviceid = d.deviceid and c.aintdescr = p.portdescr) or
(c.bdeviceid = d.deviceid and c.bintdescr = p.portdescr)))
where username is not null//加了这句后比不加慢了几十倍,请教各位高手,谢谢
from (select distinct substr(c.circuitname,
0,
(instr(c.circuitname, '_', 1, 1)) - 1) username
from node n, userinfo u, device d, portinfo p, circuit c
where u.netuserid = 'admin'
and n.nodefullcode like '%' || u.nodecode || '%'
and d.nodecode = n.nodecode
and d.changetype = 0
and c.changetype = 0
and p.deviceid = d.deviceid
and ((c.adeviceid = d.deviceid and c.aintdescr = p.portdescr) or
(c.bdeviceid = d.deviceid and c.bintdescr = p.portdescr)))
where username is not null//加了这句后比不加慢了几十倍,请教各位高手,谢谢
先把所有为空的数据刷成‘0’
然后加上where username > '0'
这样就可以临时处理了后期的话,在check里给这个username附一个默认值,保证非空就可以了
要两个两个的拼接,,这样效率应该可以提高很多