兩個TABALE在聯合查詢很慢的問題
開發人員反饋如果在語句中加上下面的一個查詢條件的話，一個小時都沒有結果。
select part.s1partnumber 父項編碼,
part.revision 父項PDM版本,
part.s1partrevision 父項ERP版本,
part1.s1partnumber 子項,
part1.s1partrevision 子項ERP版本,
substr(doc.h3phyfilerev,2,1) 物理版本拆分的子項版本,
doc.h3phyfilerev 物理版本,
doc.documentname 文件物件,
doc.revision 文件物件版本
from
(
select bj.obid,
bj.s1partnumber,
bj.revision,
bj.s1partrevision,
bj.lifecyclestate
from sum50a.s1assem bj
) part,
(
select bj1.obid,
bj1.s1partnumber,
bj1.revision,
bj1.s1partrevision,
bj1.lifecyclestate,
mst.obid mstobid
from sum50a.s1AsmMtr mst--部件
,sum50a.s1assem bj1
where bj1.itemmstrobid=mst.obid
) part1,
sum50a.partdoc rel,
sum50a.h3litdoc doc,
sum50a.assmstrc str
where substr(doc.h3phyfilerev,2,1)<>part1.s1partrevision ---加上這句很慢，一個小時沒有跑完，不加的話只需要30秒左右
and part.obid=rel.left
and doc.obid=rel.right
and part.obid=str.left
and part1.mstobid=str.right
and str.preciserevision=part1.revision
and part1.s1partnumber like '0301%'
and doc.documenttype='單板'
and doc.h3documentsubtype='焊接操作指導書'
先去掉那個“有問題” 的查詢條件檢視結果：
SQL> select part.s1partnumber col1,
2 part.revision col2,
3 part.s1partrevision col3,
4 part1.s1partnumber col4,
5 part1.s1partrevision col5,
6 substr(doc.h3phyfilerev,2,1) col5,
7 doc.h3phyfilerev col6,
8 doc.documentname col7,
9 doc.revision col8
10 from
11 (
12 select bj.obid,
13 bj.s1partnumber,
14 bj.revision,
15 bj.s1partrevision,
16 bj.lifecyclestate
17 from sum50a.s1assem bj
18 ) part,
19 (
20 select bj1.obid,
21 bj1.s1partnumber,
22 bj1.revision,
23 bj1.s1partrevision,
24 bj1.lifecyclestate,
25 mst.obid mstobid
26 from sum50a.s1AsmMtr mst--部件
27 ,sum50a.s1assem bj1
28 where bj1.itemmstrobid=mst.obid
29 ) part1,
30 sum50a.partdoc rel,
31 sum50a.h3litdoc doc,
32 sum50a.assmstrc str
33 where part.obid=rel.left
34 and doc.obid=rel.right
35 and part.obid=str.left
36 and part1.mstobid=str.right
37 and str.preciserevision=part1.revision
38 and part1.s1partnumber like '0301%'
39 and doc.documenttype='dd'
40 and doc.h3documentsubtype='dd'
41 ;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=8 Card=1 Bytes=209)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'S1ASSEM' (Cost=3 Card=1
Bytes=37)
2 1 NESTED LOOPS (Cost=8 Card=1 Bytes=209)
3 2 NESTED LOOPS (Cost=5 Card=1 Bytes=172)
4 3 NESTED LOOPS (Cost=4 Card=1 Bytes=158)
5 4 NESTED LOOPS (Cost=3 Card=1 Bytes=127)
6 5 NESTED LOOPS (Cost=2 Card=1 Bytes=90)
7 6 INDEX (FULL SCAN) OF 'PARTDOC_RLTLEFTNRIGHTIND
EX' (UNIQUE)
8 6 TABLE ACCESS (BY INDEX ROWID) OF 'H3LITDOC' (C
st=1 Card=1 Bytes=62)
9 8 INDEX (UNIQUE SCAN) OF 'H3LITDOC_CLUSTEREDUN
IQUEINDEX' (UNIQUE)
10 5 TABLE ACCESS (BY INDEX ROWID) OF 'S1ASSEM' (Cost
=1 Card=499065 Bytes=18465405)
11 10 INDEX (UNIQUE SCAN) OF 'S1ASSEM_CLUSTEREDUNIQU
EINDEX' (UNIQUE)
12 4 TABLE ACCESS (BY INDEX ROWID) OF 'ASSMSTRC' (Cost=
1 Card=1 Bytes=31)
13 12 INDEX (RANGE SCAN) OF 'ASSMSTRC_RLTLEFTINDEX' (N
ON-UNIQUE)
14 3 INDEX (UNIQUE SCAN) OF 'S1ASMMTR_CLUSTEREDUNIQUEINDE
X' (UNIQUE)
15 2 INDEX (RANGE SCAN) OF 'S1ASSEM_PARTNUMBER_REVISION' (N
ON-UNIQUE) (Cost=1 Card=4492)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
249389 consistent gets
0 physical reads
0 redo size
779 bytes sent via SQL*Net to client
460 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
把關聯查詢的子查詢部分單獨剝離出來，檢視執行效果：
SQL> select bj1.obid,
2 bj1.s1partnumber,
3 bj1.revision,
4 bj1.s1partrevision,
5 bj1.lifecyclestate,
6 mst.obid mstobid
7 from sum50a.s1AsmMtr mst--部件
8 ,sum50a.s1assem bj1
9 where bj1.itemmstrobid=mst.obid;
59843 rows selected.
Elapsed: 00:00:01.98
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (FULL) OF 'S1ASSEM'
3 1 INDEX (UNIQUE SCAN) OF 'S1ASMMTR_CLUSTEREDUNIQUEINDEX' (
UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
73552 consistent gets
832 physical reads
0 redo size
2811464 bytes sent via SQL*Net to client
44534 bytes received via SQL*Net from client
3991 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
59843 rows processed
查詢條件： substr(doc.h3phyfilerev,2,1)<>part1.s1partrevision
SQL> select count(*) from sum50a.h3litdoc;
COUNT(*)
----------
20588
可以看到關鍵的聯合語句對於的資料來源都是“大表”，可通過查詢發現對於的欄位都沒有索引。先嚐試建立INDEX，再執行SQL看下變化：
這時語句可以順利查詢出來：
SQL> select part.s1partnumber col1,
2 part.revision col2,
3 part.s1partrevision col3,
4 part1.s1partnumber col4,
5 part1.s1partrevision col5,
6 substr(doc.h3phyfilerev,2,1) col5,
7 doc.h3phyfilerev col6,
8 doc.documentname col7,
9 doc.revision col8
10 from
11 (
12 select bj.obid,
13 bj.s1partnumber,
14 bj.revision,
15 bj.s1partrevision,
16 bj.lifecyclestate
17 from sum50a.s1assem bj
18 ) part,
19 (
20 select bj1.obid,
21 bj1.s1partnumber,
22 bj1.revision,
23 bj1.s1partrevision,
24 bj1.lifecyclestate,
25 mst.obid mstobid
26 from sum50a.s1AsmMtr mst--部件
27 ,sum50a.s1assem bj1
28 where bj1.itemmstrobid=mst.obid
29 ) part1,
30 sum50a.partdoc rel,
31 sum50a.h3litdoc doc,
32 sum50a.assmstrc str
33 where substr(doc.h3phyfilerev,2,1)<>part1.s1partrevision
34 and part.obid=rel.left
35 and doc.obid=rel.right
36 and part.obid=str.left
37 and part1.mstobid=str.right
38 and str.preciserevision=part1.revision
39 and part1.s1partnumber like '0301%'
40 and doc.documenttype='dd'
41 and doc.h3documentsubtype='dd'
42 ;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=6 Card=1 Bytes=193)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'S1ASSEM' (Cost=1 Card=1
Bytes=30)
2 1 NESTED LOOPS (Cost=6 Card=1 Bytes=193)
3 2 NESTED LOOPS (Cost=5 Card=1 Bytes=163)
4 3 NESTED LOOPS (Cost=4 Card=1 Bytes=149)
5 4 NESTED LOOPS (Cost=3 Card=1 Bytes=87)
6 5 NESTED LOOPS (Cost=2 Card=1 Bytes=56)
7 6 INDEX (FULL SCAN) OF 'PARTDOC_RLTLEFTNRIGHTIND
EX' (UNIQUE)
8 6 TABLE ACCESS (BY INDEX ROWID) OF 'S1ASSEM' (Co
st=1 Card=1 Bytes=28)
9 8 INDEX (UNIQUE SCAN) OF 'S1ASSEM_CLUSTEREDUNI
QUEINDEX' (UNIQUE)
10 5 TABLE ACCESS (BY INDEX ROWID) OF 'ASSMSTRC' (Cos
t=1 Card=1 Bytes=31)
11 10 INDEX (RANGE SCAN) OF 'ASSMSTRC_RLTLEFTINDEX'
(NON-UNIQUE)
12 4 TABLE ACCESS (BY INDEX ROWID) OF 'H3LITDOC' (Cost=
1 Card=1 Bytes=62)
13 12 INDEX (UNIQUE SCAN) OF 'H3LITDOC_CLUSTEREDUNIQUE
INDEX' (UNIQUE)
14 3 INDEX (UNIQUE SCAN) OF 'S1ASMMTR_CLUSTEREDUNIQUEINDE
X' (UNIQUE)
15 2 INDEX (RANGE SCAN) OF 'S1ASSEM_ITEMMSTROBID' (NON-UNIQ
UE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4024339 consistent gets
0 physical reads
0 redo size
779 bytes sent via SQL*Net to client
460 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
說明： 在兩個大表做聯合查詢時，聯合的欄位如沒有欄位，那首先嚐試下建立INDEX。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/40239/viewspace-557979/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- beego orm join 聯合查詢的問題GoORM
- 注意：Oracle中的聯合主鍵查詢問題(轉)Oracle
- SQL 兩個表組合查詢SQL
- 關於聯合索引，範圍查詢，時間列索引的幾個問題索引
- MySQL自聯合查詢的一個例子MySql
- 深入sql多表差異化聯合查詢的問題詳解SQL
- 求助：DetachedCriteria關聯查詢問題~~
- MySQL Sending data導致查詢很慢的問題詳細分析MySql
- SQL多個表實現聯合查詢SQL
- thinkPHP多表聯合查詢PHP
- 基於聯合查詢的注入
- 一個使用JDBC按Date查詢查詢的問題JDBC
- jpa動態查詢與多表聯合查詢
- 一個MySQL多表查詢的問題MySql
- JPA 之 多表聯合查詢
- SQL 三表聯合查詢SQL
- 按月分表聯合查詢
- 01-sql-聯合查詢SQL
- mybatis多表聯合查詢的寫法MyBatis
- hibernate複合主鍵查詢問題
- 查詢皮膚中如何實現兩個 select 下拉框的關聯查詢？
- 兩表關聯查詢：sql、mybatisSQLMyBatis
- 使用並查集處理集合的合併和查詢問題並查集
- MySQL 優化五（關聯查詢子查詢以及 in 的效率問題）（高階篇）MySql優化
- 巢狀關聯會查詢兩次巢狀
- 模型聯合查詢返回指定欄位模型
- 查詢訪問同一表的兩個以上索引（三）索引
- 查詢訪問同一表的兩個以上索引（二）索引
- 查詢訪問同一表的兩個以上索引（一）索引
- SQL聯合查詢中的關鍵語法SQL
- 如何使用 Eloquent 在兩個日期之間進行查詢？
- MySQL 合併查詢union 查詢出的行合併到一個表中MySql
- [MySQL] - 聯表查詢，查詢一個不在另一個表的記錄MySql
- 解決 mybatis一對多分頁問題 聯級查詢MyBatis
- 多表聯合查詢 - 基於註解SQLSQL
- Yii2聯合查詢（配合GridView）View
- Sql Server 聯合查詢的排序規則衝突SQLServer排序
- MySQL 合併查詢join 查詢出的不同列合併到一個表中MySql