请教高手：表连接出现笛卡尔积，执行速度太慢的问题

[crystal_chen1002]

请教高手
我的sql涉及到6各表，
再加一个子查询
子查询的目的是要去掉重复记录

查询的时候速度有一点点慢
但是还可以接受，2分钟左右出结果

当我给子查询追加一个条件后
执行了十几分钟还没有结果
据toad显示,出现了笛卡尔积
请教高手
这种问题一般怎么解决
怎么会出现这么奇怪的问题

附：数据库是oracle 9i
我用的表，最大的数据量就三十几万

[iqlife]

什么条件？

[碧水幽幽泉]

你的6表查询语句贴出来看看！

[ngx20080110]

是不是条件加错地方了？

[冰山客]

贴出语句才好分析啊

[njlywy]

无数据无真相…

[cooltyx]

建议，获取sql语句的执行计划，然后分析其中cost较高的步骤，就知道问题出在哪了，查询慢一般可能的原因有发生了全表扫描、关联字段上没有索引而数据量又比较大等。

[crystal_chen1002]

这是我的sql语句
子查询中加上 and WW_BXB.JZXH ='SRM-CD419CED-TS1' 后
sql就执行不了了
toad显示应该是WX_WXXGDB表和BM_SCX表笛卡尔积

SELECT "WX_GZDM"."GZLBBM",
"WX_GZDM"."GZLB",
"WX_GZDM"."GZYYBM",
"WX_GZDM"."GZYYFX",
"WX_GZDM"."GZYYBM_1",
"WX_GZDM"."GZYYFX_1",
"WX_GZDM"."ZRBM" ,
count("F"."JZXH") AS defect_quantity
FROM "BM_CPLX",
"JZB",
"BM_SCX",
"WX_GZDM",
"DHB_CPRK",
(SELECT distinct "WW_BXB"."JZXH" AS JZXH,
"WW_BXB"."ZZBH" AS ZZBH,
"WX_WXXGDB"."GZLBBM" AS GZLBBM,
"WX_WXXGDB"."GZYYBM" AS GZYYBM,
"WX_WXXGDB"."GZYYBM_1" AS GZYYBM_1
FROM "WW_BXB",
"WX_WXXGDB"
WHERE ( "WW_BXB"."BXDBH" = "WX_WXXGDB"."WXDBH" )
and WX_WXXGDB.WXRQ >= to_date('2009-10-27','yyyy/mm/dd')
and WX_WXXGDB.WXRQ <= to_date('2010-10-27','yyyy/mm/dd')
and WW_BXB.JZXH ='SRM-CD419CED-TS1' )F
WHERE ( BM_CPLX.CODE = JZB.BS )
and ( JZB.JZM = F.JZXH )
and ( WX_GZDM.GZLBBM = F.GZLBBM )
and ( F.GZYYBM = WX_GZDM.GZYYBM )
and ( F.GZYYBM_1 = WX_GZDM.GZYYBM_1 )
and ( DHB_CPRK.CPXH = F.JZXH )
and ( DHB_CPRK.ZZFH = F.ZZBH )
and ( BM_SCX.CODE = DHB_CPRK.SCX )
and ( BM_CPLX.CODE not like '7%')
and ( BM_CPLX.CODE <> '8')
and ( DHB_CPRK.BS = 's')
and ( BM_SCX.ASSEMBLY_LINE_BS = '0' )
and DHB_CPRK.RKSJ >= to_date('2009-10-27','yyyy/mm/dd')
and DHB_CPRK.RKSJ <= to_date('2010-10-27','yyyy/mm/dd')
GROUP BY WX_GZDM.GZLBBM, WX_GZDM.GZLB, WX_GZDM.GZYYBM, WX_GZDM.GZYYFX, WX_GZDM.GZYYBM_1, WX_GZDM.GZYYFX_1,WX_GZDM.ZRBM
ORDER BY WX_GZDM.GZLBBM, WX_GZDM.GZYYBM, WX_GZDM.GZYYBM_1,WX_GZDM.ZRBM

[crystal_chen1002]

toad中的提示

Plan
SELECT STATEMENT CHOOSECost: 11 Bytes: 93 Cardinality: 1
23 SORT GROUP BY Cost: 11 Bytes: 93 Cardinality: 1
22 VIEW SYS. Cost: 9 Bytes: 93 Cardinality: 1
21 SORT UNIQUE Cost: 9 Bytes: 212 Cardinality: 1
20 TABLE ACCESS BY INDEX ROWID DSCMISADM.WX_GZDM Cost: 1 Bytes: 64 Cardinality: 1
19 NESTED LOOPS Cost: 7 Bytes: 212 Cardinality: 1
17 NESTED LOOPS Cost: 6 Bytes: 148 Cardinality: 1
14 MERGE JOIN CARTESIAN Cost: 5 Bytes: 115 Cardinality: 1
10 NESTED LOOPS Cost: 4 Bytes: 85 Cardinality: 1
7 NESTED LOOPS Cost: 3 Bytes: 73 Cardinality: 1
4 NESTED LOOPS Cost: 2 Bytes: 33 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID DSCMISADM.JZB Cost: 1 Bytes: 23 Cardinality: 1
1 INDEX UNIQUE SCAN UNIQUE DSCMISADM.PK_JZB Cost: 1 Cardinality: 1
3 INDEX UNIQUE SCAN UNIQUE DSCMISADM.P_238 Bytes: 10 Cardinality: 1
6 TABLE ACCESS BY INDEX ROWID DSCMISADM.DHB_CPRK Cost: 1 Bytes: 40 Cardinality: 1
5 INDEX RANGE SCAN NON-UNIQUE DSCMISADM.IDX_DHB_CPRK_CPXH_RKSJ Cost: 2 Cardinality: 1
9 TABLE ACCESS BY INDEX ROWID DSCMISADM.BM_SCX Cost: 1 Bytes: 12 Cardinality: 1
8 INDEX UNIQUE SCAN UNIQUE DSCMISADM.SYS_C003487 Cardinality: 1
13 BUFFER SORT Cost: 4 Bytes: 3,330 Cardinality: 111
12 TABLE ACCESS BY INDEX ROWID DSCMISADM.WX_WXXGDB Cost: 1 Bytes: 3,330 Cardinality: 111
11 INDEX RANGE SCAN NON-UNIQUE QXFP.IDX_WXXGDB_WXRQ Cost: 1 Cardinality: 112
16 TABLE ACCESS BY INDEX ROWID DSCMISADM.WW_BXB Cost: 1 Bytes: 33 Cardinality: 1
15 INDEX UNIQUE SCAN UNIQUE DSCMISADM.PK_WW_BXB Cost: 1 Cardinality: 1
18 INDEX RANGE SCAN NON-UNIQUE QXFP.IDX_BM_GZDM_GZLBBM Cost: 1 Cardinality: 22

[iqlife]

(SELECT distinct "WW_BXB"."JZXH" AS JZXH,   
 "WW_BXB"."ZZBH" AS ZZBH,   
 "WX_WXXGDB"."GZLBBM" AS GZLBBM,   
 "WX_WXXGDB"."GZYYBM" AS GZYYBM,   
 "WX_WXXGDB"."GZYYBM_1" AS GZYYBM_1   
  FROM "WW_BXB",   
  "WX_WXXGDB"   
 WHERE ( "WW_BXB"."BXDBH" = "WX_WXXGDB"."WXDBH" )   
  and WX_WXXGDB.WXRQ >= to_date('2009-10-27','yyyy/mm/dd')  
  and WX_WXXGDB.WXRQ <= to_date('2010-10-27','yyyy/mm/dd')  
  and WW_BXB.JZXH ='SRM-CD419CED-TS1' )F  

这个单独执行下，看有无问题？

如果有问题
把这个条件加到表里去试试，如下

(SELECT distinct "WW_BXB"."JZXH" AS JZXH,   
 "WW_BXB"."ZZBH" AS ZZBH,   
 "WX_WXXGDB"."GZLBBM" AS GZLBBM,   
 "WX_WXXGDB"."GZYYBM" AS GZYYBM,   
 "WX_WXXGDB"."GZYYBM_1" AS GZYYBM_1   
  FROM （select * from "WW_BXB" where WW_BXB.JZXH ='SRM-CD419CED-TS1' ）"WW_BXB",   
  "WX_WXXGDB"   
 WHERE ( "WW_BXB"."BXDBH" = "WX_WXXGDB"."WXDBH" )   
  and WX_WXXGDB.WXRQ >= to_date('2009-10-27','yyyy/mm/dd')  
  and WX_WXXGDB.WXRQ <= to_date('2010-10-27','yyyy/mm/dd')  
  )F  

[Dream_1986]

状况不明。。
给些具体内容吧。。

[心中的彩虹]

[Quote=引用 9 楼 crystal_chen1002 的回复:]
toad中的提示

Plan
SELECT STATEMENT CHOOSECost: 11 Bytes: 93 Cardinality: 1
23 SORT GROUP BY Cost: 11 Bytes: 93 Cardinality: 1
22 VIEW SYS. Cost: 9 Bytes: 93 Cardinality: 1
21 SORT UNIQUE……
[/Quote]
TABLE ACCESS BY INDEX ROWID DSCMISADM.WX_GZDM Cost: 1 Bytes: 64 Cardinality: 1

2 TABLE ACCESS BY INDEX ROWID DSCMISADM.JZB Cost: 1 Bytes: 23 Cardinality: 1
1 INDEX UNIQUE SCAN UNIQUE DSCMISADM.PK_JZB Cost: 1 Cardinality: 1
3 INDEX UNIQUE SCAN UNIQUE DSCMISADM.P_238 Bytes: 10 Cardinality: 1
6 TABLE ACCESS BY INDEX ROWID DSCMISADM.DHB_CPRK Cost: 1 Bytes: 40 Cardinality: 1
5 INDEX RANGE SCAN NON-UNIQUE DSCMISADM.IDX_DHB_CPRK_CPXH_RKSJ Cost: 2 Cardinality: 1
9 TABLE ACCESS BY INDEX ROWID DSCMISADM.BM_SCX Cost: 1 Bytes: 12 Cardinality: 1
8 INDEX UNIQUE SCAN UNIQUE DSCMISADM.SYS_C003487 Cardinality: 1
13 BUFFER SORT Cost: 4 Bytes: 3,330 Cardinality: 111
12 TABLE ACCESS BY INDEX ROWID DSCMISADM.WX_WXXGDB Cost: 1 Bytes: 3,330 Cardinality: 111
11 INDEX RANGE SCAN NON-UNIQUE QXFP.IDX_WXXGDB_WXRQ Cost: 1 Cardinality: 112
16 TABLE ACCESS BY INDEX ROWID DSCMISADM.WW_BXB Cost: 1 Bytes: 33 Cardinality: 1
15 INDEX UNIQUE SCAN UNIQUE DSCMISADM.PK_WW_BXB Cost: 1 Cardinality: 1
18 INDEX RANGE SCAN NON-UNIQUE QXFP.IDX_BM_GZDM_GZLBBM Cost: 1 Cardinality: 22


把上面的走索引的字段建联合索引

[crystal_chen1002]

子查询单独执行是没有问题的

我按照你修改的试了一下
还是不好用
请高手继续支招

谢谢！

[gys007]

你子查询查出来的字段没见在外层使用啊！不到底想做什么？
[Quote=引用 8 楼 crystal_chen1002 的回复:]
这是我的sql语句
子查询中加上 and WW_BXB.JZXH ='SRM-CD419CED-TS1' 后
sql就执行不了了
toad显示应该是WX_WXXGDB表和BM_SCX表笛卡尔积

SELECT "WX_GZDM"."GZLBBM",
"WX_GZDM"."GZLB",
"WX_GZDM"."GZYYBM",
"WX_GZDM"."GZYYFX……
[/Quote]

[amdgaming]

说下 逻辑吧 楼主