SITE_CONF_DATA 24669
CELL_CONF_DATA 53268
TCH_TRX 1206976
DIC_BSC 是一个view 功一百多行
dic_bsc:
SELECT SUBSTR(obj_inst,1,4),SUBSTR(obj_inst,5,2),
obj_inst,name,c_name
FROM mot_nmc.mot_bsc_con
WHERE c10=1
AND obj_inst IS NOT NULL
UNION ALL
SELECT SUBSTR(object_instance,1,4),
SUBSTR(object_instance,5,2),
object_instance,name,c_name
FROM alc_nmc.c_bsc_hu
UNION ALL
SELECT SUBSTR(object_instance,1,4),
SUBSTR(object_instance,5,2),
object_instance,name,c_name
FROM alc_nmc.c_bsc_jx;
先看看原先的执行计划:
SQL> SELECT B.Dist_ID,B.City_ID,A.Obj_Inst,nvl(D.BSc_C_Name,D.Bsc_Name),A.Lac,A.
CI,C.LATITUDE,C.LONGTITUDE,B.angle_of_Antenna,
2 ','||A.BCCH||','||TRX_1||','||TRX_2||','||TRX_3||','||TRX_4||','||TRX_5||',
'||TRX_6||','||TRX_7||','||TRX_8||','||TRX_9||','||TRX_10||','||Trx_11||','||Trx
_12||',' All_TRX,
3 A.BCCH,TRX_Count,TRX_1,TRX_2,TRX_3,TRX_4,TRX_5,
4 TRX_6,TRX_7,TRX_8,TRX_9,TRX_10,Trx_11,Trx_12
5 FROM db2.TCH_TRX A,db2.CELL_CONF_DATA B,db2.SITE_CONF_DATA C,db2.Dic_BSC D
6 WHERE A.ACTIVE=1 AND A.OBJ_INST=B.OBJ_INST AND B.P_OBJ_INST=C.OBJ_INST
7 AND C.LATITUDE IS NOT NULL AND C.LONGTITUDE IS NOT NULL
8 and b.active=1 and c.active=1
9 and C.Bsc_ID=D.Bsc_ID;
已选择4366行。
已用时间: 00: 00: 32.77
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=767 Card=8822866 Byt
es=1702813138)
1 0 HASH JOIN (Cost=767 Card=8822866 Bytes=1702813138)
2 1 VIEW OF 'DIC_BSC' (Cost=58 Card=865 Bytes=38060)
3 2 SORT (UNIQUE) (Cost=58 Card=865 Bytes=28947)
4 3 UNION-ALL
5 4 TABLE ACCESS (FULL) OF 'MOT_BSC_CON' (Cost=2 Card=
701 Bytes=21731)
6 4 TABLE ACCESS (FULL) OF 'C_BSC_HU' (Cost=1 Card=82
Bytes=3608)
7 4 TABLE ACCESS (FULL) OF 'C_BSC_JX' (Cost=1 Card=82
Bytes=3608)
8 1 HASH JOIN (Cost=708 Card=1315780 Bytes=196051220)
9 8 TABLE ACCESS (FULL) OF 'SITE_CONF_DATA' (Cost=17 Card=
4288 Bytes=210112)
10 8 HASH JOIN (Cost=686 Card=1140094 Bytes=114009400)
11 10 TABLE ACCESS (FULL) OF 'CELL_CONF_DATA' (Cost=41 Car
d=20033 Bytes=861419)
12 10 TABLE ACCESS (FULL) OF 'TCH_TRX' (Cost=632 Card=6026
85 Bytes=34353045)
Statistics
----------------------------------------------------------
53 recursive calls
27 db block gets
15914 consistent gets
182 physical reads
0 redo size
571843 bytes sent via SQL*Net to client
11313 bytes received via SQL*Net from client
293 SQL*Net roundtrips to/from client
14 sorts (memory)
0 sorts (disk)
4366 rows processed
全部是full table 先看看index 访问有没有用
已用时间: 00: 00: 00.50
SQL> SELECT /*+INDEX(A)*/ B.Dist_ID,B.City_ID,A.Obj_Inst,nvl(D.BSc_C_Name,D.Bsc_
Name),A.Lac,A.CI,C.LATITUDE,C.LONGTITUDE,B.angle_of_Antenna,
2 ','||A.BCCH||','||TRX_1||','||TRX_2||','||TRX_3||','||TRX_4||','||TRX_5||',
'||TRX_6||','||TRX_7||','||TRX_8||','||TRX_9||','||TRX_10||','||Trx_11||','||Trx
_12||',' All_TRX,
3 A.BCCH,TRX_Count,TRX_1,TRX_2,TRX_3,TRX_4,TRX_5,
4 TRX_6,TRX_7,TRX_8,TRX_9,TRX_10,Trx_11,Trx_12
5 FROM DB2.TCH_TRX A,DB2.CELL_CONF_DATA B,DB2.SITE_CONF_DATA C,DB2.Dic_BSC D
6 WHERE A.ACTIVE=1 AND A.OBJ_INST=B.OBJ_INST AND B.P_OBJ_INST=C.OBJ_INST
7 AND C.LATITUDE IS NOT NULL AND C.LONGTITUDE IS NOT NULL
8 and b.active=1 and c.active=1
9 and C.Bsc_ID=D.Bsc_ID;
已选择4366行。
已用时间: 00: 00: 46.56
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=118824 Card=30553646
Bytes=5805192740)
1 0 HASH JOIN (Cost=118824 Card=30553646 Bytes=5805192740)
2 1 VIEW OF 'DIC_BSC' (Cost=4 Card=865 Bytes=38060)
3 2 UNION-ALL
4 3 TABLE ACCESS (FULL) OF 'MOT_BSC_CON' (Cost=2 Card=70
1 Bytes=21731)
5 3 TABLE ACCESS (FULL) OF 'C_BSC_HU' (Cost=1 Card=82 By
tes=3608)
6 3 TABLE ACCESS (FULL) OF 'C_BSC_JX' (Cost=1 Card=82 By
tes=3608)
7 1 HASH JOIN (Cost=118816 Card=4203334 Bytes=613686764)
8 7 TABLE ACCESS (FULL) OF 'SITE_CONF_DATA' (Cost=23 Card=
6640 Bytes=325360)
9 7 HASH JOIN (Cost=118780 Card=2433430 Bytes=236042710)
10 9 TABLE ACCESS (FULL) OF 'CELL_CONF_DATA' (Cost=53 Car
d=26562 Bytes=1168728)
11 9 TABLE ACCESS (BY INDEX ROWID) OF 'TCH_TRX' (Cost=118
710 Card=603101 Bytes=31964353)
12 11 INDEX (FULL SCAN) OF 'IDX_INST' (NON-UNIQUE) (Cost
=6228 Card=603101)
Statistics
----------------------------------------------------------
7 recursive calls
22 db block gets
1206055 consistent gets
17461 physical reads
69928 redo size
499669 bytes sent via SQL*Net to client
11313 bytes received via SQL*Net from client
293 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
4366 rows processed
SQL>
使用index(A) COST 和逻辑读更多了
不能用 不是说index的连接是最快的吗?
那我们就在C表上强制index
先建立index
CREATE BITMAP INDEX DB2.dddd1
ON DB2.SITE_CONF_DATA(ACTIVE, LATITUDE, LONGTITUDE, BSC_ID)
TABLESPACE INDX;
SQL> SELECT /*+INDEX(C)*/ B.Dist_ID,B.City_ID,A.Obj_Inst,nvl(D.BSc_C_Name,D.Bsc_
Name),A.Lac,A.CI,C.LATITUDE,C.LONGTITUDE,B.angle_of_Antenna,
2 ','||A.BCCH||','||TRX_1||','||TRX_2||','||TRX_3||','||TRX_4||','||TRX_5||',
'||TRX_6||','||TRX_7||','||TRX_8||','||TRX_9||','||TRX_10||','||Trx_11||','||Trx
_12||',' All_TRX,
3 A.BCCH,TRX_Count,TRX_1,TRX_2,TRX_3,TRX_4,TRX_5,
4 TRX_6,TRX_7,TRX_8,TRX_9,TRX_10,Trx_11,Trx_12
5 FROM DB2.TCH_TRX A,DB2.CELL_CONF_DATA B,DB2.SITE_CONF_DATA C,DB2.Dic_BSC D
6 WHERE A.ACTIVE=1 AND A.OBJ_INST=B.OBJ_INST AND B.P_OBJ_INST=C.OBJ_INST
7 AND C.LATITUDE IS NOT NULL AND C.LONGTITUDE IS NOT NULL
8 and b.active=1 and c.active=1
9 and C.Bsc_ID=D.Bsc_ID;
已选择4649行。
已用时间: 00: 00: 11.26
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3106 Card=30553646 B
ytes=5805192740)
1 0 HASH JOIN (Cost=3106 Card=30553646 Bytes=5805192740)
2 1 VIEW OF 'DIC_BSC' (Cost=4 Card=865 Bytes=38060)
3 2 UNION-ALL
4 3 TABLE ACCESS (FULL) OF 'MOT_BSC_CON' (Cost=2 Card=70
1 Bytes=21731)
5 3 TABLE ACCESS (FULL) OF 'C_BSC_HU' (Cost=1 Card=82 By
tes=3608)
6 3 TABLE ACCESS (FULL) OF 'C_BSC_JX' (Cost=1 Card=82 By
tes=3608)
7 1 HASH JOIN (Cost=3098 Card=4203334 Bytes=613686764)
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'SITE_CONF_DATA' (Cos
t=2383 Card=6640 Bytes=325360)
9 8 INDEX (FULL SCAN) OF 'PK_SITE_CONF_DATA' (UNIQUE) (C
ost=139 Card=6640)
10 7 HASH JOIN (Cost=702 Card=2433430 Bytes=236042710)
11 10 TABLE ACCESS (FULL) OF 'CELL_CONF_DATA' (Cost=53 Car
d=26562 Bytes=1168728)
12 10 TABLE ACCESS (FULL) OF 'TCH_TRX' (Cost=632 Card=6031
01 Bytes=31964353)
Statistics
----------------------------------------------------------
7 recursive calls
22 db block gets
38565 consistent gets
35 physical reads
0 redo size
580441 bytes sent via SQL*Net to client
11997 bytes received via SQL*Net from client
311 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
4649 rows processed
SQL>
果然快了很多,但是系统本身不会选择c表上的index用来hash_join
所以强制index 在c表上强制index会比较快。
现在 主要的瓶井还是在 tch_trx 和cell_conf_data上面
用index 进行连接并不快
不知到为什么?需要在研究一下!