SITE_CONF_DATA 24669CELL_CONF_DATA 53268TCH_TRX 1206976DIC_BSC is a multi-view function hundred lines 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 = 1AND obj_inst IS NOT NULLUNION ALLSELECT SUBSTR (object_instance, 1,4), SUBSTR (object_instance, 5,2), object_instance, name, c_nameFROM alc_nmc.c_bsc_huUNION ALLSELECT SUBSTR (object_instance, 1,4), SUBSTR (object_instance, 5,2), object_instance, name, c_namefrom alc_nmc.c_bsc_jx;
First look at the original execution plan: 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_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 lines have been selected.
Time: 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 = 882866 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 Clom Client 14 Sorts (Memory) 0 Sorts (Disk) 4366 Rows Processed is all Full Table first look at INDEX access is available
Time: 00: 00:00.50sql> 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_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 is selected.
Time: 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 Clom 7 Sorts (Memory) 0 Sorts (Disk) 4366 Rows Processed
SQL> Using Index (a) Cost and Logic Read more can't be used not to say that INDEX is the fastest connection? Then we enforce index on the C table first build 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_2 || ',' || TRX_3 || ',' || TRX_4 || ',' || TRX_5 || ',' || TRX_6 || ', '|| TRX_7 ||', '|| 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 lines have been selected.
Time: 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) 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>
Sure enough, there is a lot, but the system itself does not select index on the C table to use Hash_JOIN so Inceburate INDEX to force index on the C table.
Now the main bottle well is still connected to the TCH_TRX and CELL_CONF_DATA to connect with Index and don't know why? Need to study!
