view的优化（其实就是表连接的问题）

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 进行连接并不快
不知到为什么？需要在研究一下！