有:psbw_j_mph 记录数:888740, mphid是主键
psbw_dwcsjbxx 记录数:33808, fwid是主键
psbw_fwjbxx 记录数:820618, fwid是主键,mphid创建了索引。执行:SELECT ROWNUM rn, a.dwcsid, b.mphmc, a.mc, (SELECT mc
FROM psbw_d_dwcslb
WHERE dm = a.lb1) || ' ' || (SELECT mc
FROM psbw_d_dwcslb
WHERE dm = a.lb2) || ' ' || (SELECT mc
FROM psbw_d_dwcslb
WHERE dm = a.lb3) || ' ' || (SELECT mc
FROM psbw_d_dwcslb
WHERE dm = a.lb4) lb1_sc, (SELECT mc
FROM psbw_d_dm
WHERE lxbm = '34'
AND dm = a.lx) lx_sc, decode(a.bs, '0', '有效', '1', '停业', '2',
'注销') bs_sc, (SELECT xm
FROM psbw_j_yh
WHERE psbw_j_yh.dm = a.djrdm) djrmc
FROM psbw_dwcsjbxx a, psbw_fwjbxx b, psbw_j_mph c
WHERE a.fwid = b.fwid
AND b.mphid = c.mphid
AND a.bs <> '2'
AND b.bs = 'y'
AND c.bs = 'y'
AND (a.lb1 IN ('A101', 'A199', 'A202', 'A302', 'B101', 'B102', 'B104',
'B105', 'B106')
OR a.lb2 IN ('A101', 'A199', 'A202', 'A302', 'B101', 'B102', 'B104',
'B105', 'B106')
OR a.lb3 IN ('A101', 'A199', 'A202', 'A302', 'B101', 'B102', 'B104',
'B105', 'B106')
OR a.lb4 IN ('A101', 'A199', 'A202', 'A302', 'B101', 'B102', 'B104',
'B105', 'B106'))速度非常慢,把 AND (a.lb1 IN ('A101', 'A199', 'A202', 'A302', 'B101', 'B102', 'B104',
'B105', 'B106')
OR a.lb2 IN ('A101', 'A199', 'A202', 'A302', 'B101', 'B102', 'B104',
'B105', 'B106')
OR a.lb3 IN ('A101', 'A199', 'A202', 'A302', 'B101', 'B102', 'B104',
'B105', 'B106')
OR a.lb4 IN ('A101', 'A199', 'A202', 'A302', 'B101', 'B102', 'B104',
'B105', 'B106'))
查询条件拿掉,查询也还是非常慢。
这是执行计划图:
SELECT STATEMENT, GOAL = CHOOSE
COUNT
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL 对象所有者=PSBW_GZ 对象名称=PSBW_DWCSJBXX
TABLE ACCESS BY INDEX ROWID 对象所有者=PSBW_GZ 对象名称=PSBW_FWJBXX
INDEX UNIQUE SCAN 对象所有者=PSBW_GZ 对象名称=SYS_C003921
TABLE ACCESS BY INDEX ROWID 对象所有者=PSBW_GZ 对象名称=PSBW_J_MPH
INDEX UNIQUE SCAN 对象所有者=PSBW_GZ 对象名称=SYS_C004047
psbw_dwcsjbxx 记录数:33808, fwid是主键
psbw_fwjbxx 记录数:820618, fwid是主键,mphid创建了索引。执行:SELECT ROWNUM rn, a.dwcsid, b.mphmc, a.mc, (SELECT mc
FROM psbw_d_dwcslb
WHERE dm = a.lb1) || ' ' || (SELECT mc
FROM psbw_d_dwcslb
WHERE dm = a.lb2) || ' ' || (SELECT mc
FROM psbw_d_dwcslb
WHERE dm = a.lb3) || ' ' || (SELECT mc
FROM psbw_d_dwcslb
WHERE dm = a.lb4) lb1_sc, (SELECT mc
FROM psbw_d_dm
WHERE lxbm = '34'
AND dm = a.lx) lx_sc, decode(a.bs, '0', '有效', '1', '停业', '2',
'注销') bs_sc, (SELECT xm
FROM psbw_j_yh
WHERE psbw_j_yh.dm = a.djrdm) djrmc
FROM psbw_dwcsjbxx a, psbw_fwjbxx b, psbw_j_mph c
WHERE a.fwid = b.fwid
AND b.mphid = c.mphid
AND a.bs <> '2'
AND b.bs = 'y'
AND c.bs = 'y'
AND (a.lb1 IN ('A101', 'A199', 'A202', 'A302', 'B101', 'B102', 'B104',
'B105', 'B106')
OR a.lb2 IN ('A101', 'A199', 'A202', 'A302', 'B101', 'B102', 'B104',
'B105', 'B106')
OR a.lb3 IN ('A101', 'A199', 'A202', 'A302', 'B101', 'B102', 'B104',
'B105', 'B106')
OR a.lb4 IN ('A101', 'A199', 'A202', 'A302', 'B101', 'B102', 'B104',
'B105', 'B106'))速度非常慢,把 AND (a.lb1 IN ('A101', 'A199', 'A202', 'A302', 'B101', 'B102', 'B104',
'B105', 'B106')
OR a.lb2 IN ('A101', 'A199', 'A202', 'A302', 'B101', 'B102', 'B104',
'B105', 'B106')
OR a.lb3 IN ('A101', 'A199', 'A202', 'A302', 'B101', 'B102', 'B104',
'B105', 'B106')
OR a.lb4 IN ('A101', 'A199', 'A202', 'A302', 'B101', 'B102', 'B104',
'B105', 'B106'))
查询条件拿掉,查询也还是非常慢。
这是执行计划图:
SELECT STATEMENT, GOAL = CHOOSE
COUNT
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL 对象所有者=PSBW_GZ 对象名称=PSBW_DWCSJBXX
TABLE ACCESS BY INDEX ROWID 对象所有者=PSBW_GZ 对象名称=PSBW_FWJBXX
INDEX UNIQUE SCAN 对象所有者=PSBW_GZ 对象名称=SYS_C003921
TABLE ACCESS BY INDEX ROWID 对象所有者=PSBW_GZ 对象名称=PSBW_J_MPH
INDEX UNIQUE SCAN 对象所有者=PSBW_GZ 对象名称=SYS_C004047
解决方案 »
- eclipse连不上toad?
- 请教一个关于count的语句
- 想给Oracle打补丁,opatch apply问题
- 大家好,关于Oracle中系统权限unlimited tablespace授予给自己创建的角色问题?
- 一个困惑我好多天, 关于事务的问题,
- 在用PLSQL链接Oracle数据库操作过程中,经常提示“TNS 13535,操作超时”
- 一个oracle的使用问题
- 修改系统时间,急!!!
- 为什么我在Oracle8.17中以internal身份登陆时不需要密码?
- oracle触发器的问题
- 字符串“7.2085E+10”如何转化为number
- 推荐给ORACLE板块退问的所有人,关于如何提问题,请参考itpub上的:提问的智慧!
FROM psbw_d_dwcslb
WHERE dm = a.lb1) ¦¦ ' ' ¦¦ (SELECT mc
FROM psbw_d_dwcslb
WHERE dm = a.lb2) ¦¦ ' ' ¦¦ (SELECT mc
FROM psbw_d_dwcslb
WHERE dm = a.lb3) ¦¦ ' ' ¦¦ (SELECT mc
FROM psbw_d_dwcslb
WHERE dm = a.lb4) lb1_sc,
(SELECT mc
FROM psbw_d_dm
WHERE lxbm = '34'
AND dm = a.lx) lx_sc
和in没多大联系,关键是这里耗时间
试试直接表连接:
from ... ,
psbw_d_dwcslb lb1,psbw_d_dwcslb lb2,psbw_d_dwcslb lb3,psbw_d_dwcslb lb4,psbw_d_dm dm
where a.lb1=lb1.dm and a.lb2=lb2.dm and a.lb3=lb3.dm and ...
SELECT ROWNUM rn,
a.dwcsid,
b.mphmc,
a.mc,
---upd
d1.mc || ' ' || d2.mc || ' ' || d3.mc || ' ' || d14.mc as lb1_sc,
e1.mc as lx_sc,
---
decode(a.bs, '0', '有效', '1', '停业', '2', '注销') bs_sc,
---upd
f1.xm as djrmc
FROM psbw_dwcsjbxx a,
---upd
(select * from psbw_fwjbxx where bs = 'y') b,
(select * from psbw_j_mph where bs = 'y') c
---add
,
psbw_d_dwcslb d1,
psbw_d_dwcslb d2,
psbw_d_dwcslb d3,
psbw_d_dwcslb d4,
(select * from psbw_d_dm where lxbm = '34') e1,
psbw_j_yh f1
where a.fwid = b.fwid
AND b.mphid = c.mphid
AND a.bs <> '2'
--AND b.bs = 'y'
--AND c.bs = 'y'
AND (a.lb1 IN ('A101', 'A199', 'A202', 'A302', 'B101', 'B102', 'B104',
'B105', 'B106') OR a.lb2 IN ('A101', 'A199', 'A202', 'A302', 'B101',
'B102', 'B104', 'B105', 'B106') OR
a.lb3 IN ('A101', 'A199', 'A202', 'A302', 'B101', 'B102', 'B104',
'B105', 'B106') OR a.lb4 IN ('A101', 'A199', 'A202', 'A302', 'B101',
'B102', 'B104', 'B105', 'B106'))
---add
and a.lb1 = d1.dm(+)
and a.lb2 = d2.dm(+)
and a.lb3 = d3.dm(+)
and a.lb4 = d4.dm(+)
and a.lx = e1.dm(+)
and a.djrdm = f1.dm(+)
a.dwcsid,
b.mphmc,
a.mc,
---upd
d1.mc ¦ ¦ ' ' ¦ ¦ d2.mc ¦ ¦ ' ' ¦ ¦ d3.mc ¦ ¦ ' ' ¦ ¦ d14.mc as lb1_sc,
e1.mc as lx_sc,
---
decode(a.bs, '0', '有效', '1', '停业', '2', '注销') bs_sc,
---upd
f1.xm as djrmc
FROM psbw_dwcsjbxx a,
---upd
(select * from psbw_fwjbxx where bs = 'y') b,
(select * from psbw_j_mph where bs = 'y') c
---add
,
psbw_d_dwcslb d1,
psbw_d_dwcslb d2,
psbw_d_dwcslb d3,
psbw_d_dwcslb d4,
(select * from psbw_d_dm where lxbm = '34') e1,
psbw_j_yh f1
where a.fwid = b.fwid
AND b.mphid = c.mphid
AND a.bs <> '2'
--AND b.bs = 'y'
--AND c.bs = 'y'
AND (a.lb1 IN ('A101', 'A199', 'A202', 'A302', 'B101', 'B102', 'B104',
'B105', 'B106') OR a.lb2 IN ('A101', 'A199', 'A202', 'A302', 'B101',
'B102', 'B104', 'B105', 'B106') OR
a.lb3 IN ('A101', 'A199', 'A202', 'A302', 'B101', 'B102', 'B104',
'B105', 'B106') OR a.lb4 IN ('A101', 'A199', 'A202', 'A302', 'B101',
'B102', 'B104', 'B105', 'B106'))
---add
and a.lb1 = d1.dm(+)
and a.lb2 = d2.dm(+)
and a.lb3 = d3.dm(+)
and a.lb4 = d4.dm(+)
and a.lx = e1.dm(+)
and a.djrdm = f1.dm(+)更加慢了
(select * from psbw_j_mph where bs = 'y') c (select * from psbw_d_dm where lxbm = '34') e1
SELECT /*+ ROWID(A) ORDERED USE_NL(B) INDEX(B USR_CAR_IDX1) */SELECT /*+ ROWID(A) ORDERED USE_NL(C) INDEX(C USR_LAST_PK) USE_NL(B) INDEX(B USR_HIST_IDX1) */ 还可上网查下关于追加SQL语句的优化方法。