我写了一条查询语句,为什么运行很慢,等了2分钟,也没反应
select c.issuetime,
m.mchntname,
t.terminalName,
ct.cardname,
c.cardvalue,
count(*) downcount
from m_cards c, m_cardtype ct, m_merchant m, m_terminal t
where c.cardtype = ct.cardtype
and m.MchntID = c.MchntID
and c.TerminalID = t.TerminalID
and c.issuetag = 1
and m.mchntname like '%鸿%'
or t.terminalName like '%鸿%'
or ct.cardname like '%鸿%'
or c.cardvalue like '%鸿%'
or m.mchntid like '%鸿%'
or t.terminalId like '%鸿%'
or c.cardtype like '%鸿%'
group by c.issuetime,
m.mchntname,
t.terminalName,
m.mchntid,
ct.cardname,
c.cardvalue
having 1 = 1
union
select h.issuetime,
m.mchntname,
t.terminalName,
ct.cardname,
h.cardvalue,
count(*) downcount
from m_Issue h, m_cardtype ct, m_merchant m, m_terminal t
where h.cardtype = ct.cardtype
and m.MchntID = h.MchntID
and h.TerminalID = t.TerminalID
and m.mchntname like '%鸿%'
or t.terminalName like '%鸿%'
or ct.cardname like '%鸿%'
or h.cardvalue like '%鸿%'
or m.mchntid like '%鸿%'
or t.terminalId like '%鸿%'
or h.cardtype like '%鸿%'
or h.datasoure like '%鸿%'
group by h.issuetime,
m.mchntname,
t.terminalName,
ct.cardname,
h.cardvalue
having 1 = 1
请大家帮我看看
select c.issuetime,
m.mchntname,
t.terminalName,
ct.cardname,
c.cardvalue,
count(*) downcount
from m_cards c, m_cardtype ct, m_merchant m, m_terminal t
where c.cardtype = ct.cardtype
and m.MchntID = c.MchntID
and c.TerminalID = t.TerminalID
and c.issuetag = 1
and m.mchntname like '%鸿%'
or t.terminalName like '%鸿%'
or ct.cardname like '%鸿%'
or c.cardvalue like '%鸿%'
or m.mchntid like '%鸿%'
or t.terminalId like '%鸿%'
or c.cardtype like '%鸿%'
group by c.issuetime,
m.mchntname,
t.terminalName,
m.mchntid,
ct.cardname,
c.cardvalue
having 1 = 1
union
select h.issuetime,
m.mchntname,
t.terminalName,
ct.cardname,
h.cardvalue,
count(*) downcount
from m_Issue h, m_cardtype ct, m_merchant m, m_terminal t
where h.cardtype = ct.cardtype
and m.MchntID = h.MchntID
and h.TerminalID = t.TerminalID
and m.mchntname like '%鸿%'
or t.terminalName like '%鸿%'
or ct.cardname like '%鸿%'
or h.cardvalue like '%鸿%'
or m.mchntid like '%鸿%'
or t.terminalId like '%鸿%'
or h.cardtype like '%鸿%'
or h.datasoure like '%鸿%'
group by h.issuetime,
m.mchntname,
t.terminalName,
ct.cardname,
h.cardvalue
having 1 = 1
请大家帮我看看
换成
union all
楼主写个having 1=1有什么用?
感觉这个sql没啥可优的啦
是不是在alert log里可以看到? 忘记了。
看到底是解析后哪里花时间较长,或者Toad应该可以看到一条sql源码编译和具体执行时间吧?呵呵,我是新手。
可能不是你所期望的结果。 and (
m.mchntname like '%鸿%'
or t.terminalName like '%鸿%'
or ct.cardname like '%鸿%'
or h.cardvalue like '%鸿%'
or m.mchntid like '%鸿%'
or t.terminalId like '%鸿%'
or h.cardtype like '%鸿%'
or h.datasoure like '%鸿%'
)