优化一个sql时,想起了建立索引,不过不知道是应该分开建立(一个索引名对应一个字段),还是应该一起建立(一个索引名对应多个字段),这两个在执行上有什么区别。
sql如下
SELECT "WW_BXB"."BXDBH",
"WW_BXB"."ZZBH",
"WW_BXB"."WXS",
"WW_BXB"."BXZZ",
"WW_BXB"."RID",
"WW_BXB"."GDXZ",
"CALL_DIRE_USER"."LEVEL1",
"WW_BXB"."ZONE",
"CALL_KH_LEVEL1"."KH_NAME",
"CALL_KH_LEVEL2"."KH_NAME",
"WW_BXB"."BGY",
"WW_BXB"."YD",
"WW_BXB"."WXLX",
"WW_BXB"."DHPZ_LX",
"WW_BXB"."DHPZ_ZT",
"WW_BXB"."DHPZ_SJ",
"WW_BXB"."DHPZ_CZZ",
"WW_BXB"."KSF_DH",
"KHXXDB"."KHMC",
"WW_BXB"."JZXH",
"WW_BXB"."PHONE",
"WW_BXB"."FAX",
"CALL_DIRE_USER"."MOBILE",
"WW_BXB"."KSF_ZCBH",
"WW_BXB"."PEPSI_DH",
"WW_BXB"."PEPSI_INTERID",
"WW_BXB"."PEPSI_ZCBH",
"CALL_DIRE_USER"."YYB",
"CALL_DIRE_USER"."YYS",
"CALL_DIRE_USER"."LINKMAN",
"CALL_DIRE_USER"."KH_SF",
"CALL_DIRE_USER"."KH_CITY",
"CALL_DIRE_USER"."KH_COUNTY",
"CALL_DIRE_USER"."KH_TOWN",
"CALL_DIRE_USER"."ADDRESS",
"WW_BXB"."TSWXLX",
"CALL_DIRE_USER"."LEVEL2",
"WW_BXB"."KSF_INTERID",
"WW_BXB"."TINGHSIN_SOUCE"
FROM "WW_BXB",
"WW_ZTB",
"CALL_DIRE_USER",
"CALL_KH_LEVEL1",
"CALL_KH_LEVEL2",
"KHXXDB"
WHERE ( call_dire_user.level1 = call_kh_level1.id_level1 (+)) and
( call_dire_user.level2 = call_kh_level2.id_level2 (+)) and
( ww_bxb.wxs = khxxdb.khbm (+)) and
( "WW_BXB"."ZJKHBM" = "CALL_DIRE_USER"."ID" ) and
( "WW_BXB"."BXDBH" = "WW_ZTB"."WXDBH" )
and ( ( "WW_ZTB"."PG" = '0' ) AND ( "WW_BXB"."BXR" not in ('顶津E站','百事E站','EXCEL') )
AND ("WW_BXB"."DHPZ_ZT" is null OR "WW_BXB"."DHPZ_ZT" <> '1')
AND ("WW_BXB"."GDXZ" is null OR "WW_BXB"."GDXZ" <> '08')
AND ("WW_BXB"."PDR" is null OR "WW_BXB"."PDR" like '%') );
疑惑点1.像 where后面的 ( call_dire_user.level1 = call_kh_level1.id_level1 (+)) and
( call_dire_user.level2 = call_kh_level2.id_level2 (+)) 这句话,我是把 call_dire_user这个表的level1和level2这两个字段分别建立两个索引,还是把两个字段建立一个索引。
疑惑点2 像 ww_bxb这种表 每个and后都有一个字段限定,我是把这几个字段捏在一起写成一个索引,还是分别建立多个索引?
甚是迷惑