请教高手
我的sql涉及到6各表,
再加一个子查询
子查询的目的是要去掉重复记录查询的时候速度有一点点慢
但是还可以接受,2分钟左右出结果当我给子查询追加一个条件后
执行了十几分钟还没有结果
据toad显示,出现了笛卡尔积
请教高手
这种问题一般怎么解决
怎么会出现这么奇怪的问题附:数据库是oracle 9i
我用的表,最大的数据量就三十几万
我的sql涉及到6各表,
再加一个子查询
子查询的目的是要去掉重复记录查询的时候速度有一点点慢
但是还可以接受,2分钟左右出结果当我给子查询追加一个条件后
执行了十几分钟还没有结果
据toad显示,出现了笛卡尔积
请教高手
这种问题一般怎么解决
怎么会出现这么奇怪的问题附:数据库是oracle 9i
我用的表,最大的数据量就三十几万
子查询中加上 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
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
"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
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
把上面的走索引的字段建联合索引