北京高端品牌网站定制网站广告牌制作教程
RT,该字段未建立索引，以下贴出SQL,及执行计划，不加trim走hash join，求解释！
----------------------语句如下，标红的字段加trim()
EXPLAIN PLAN FOR select a.楼盘id,
a.监测明细id,
a.报告日期,
a.广告位名称,
a.页面名称,
a.所属集团,
b.项目名称,
a.投放地域,
a.媒体,
a.对手,
c.province as 关联所在省,
c.city as 关联所在市
from t_hw_en a, t_hw_building b, t_crm_city c,t_crm_city d
where a.楼盘id = b.楼盘id
and b.所在城市uid = c.huid
and trim(a.投放地域) not in (trim(c.province), trim(c.city))
and b.项目类别 = '1'
and trim(a.媒体) not in ('XX', 'YY', 'ZZ')
and a.报告日期 >= trunc(sysdate - 7)
and not exists (select buildingid, ad, site
from dw_yidi_list g
where a.楼盘id = g.buildingid
and a.广告位名称 = g.ad
and a.媒体 = g.site)
AND trim(a.投放地域)=d.city(+)
AND (d.province,c.province) NOT IN (SELECT '海南','海南' FROM dual);
------------------------加trim()的执行计划
SELECT * FROM TABLE(dbms_xplan.display);
1 Plan hash value: 1048134721
2
3 -----------------------------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 -----------------------------------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 1 | 310 | 1948 (1)| 00:00:24 |
7 |* 1 | FILTER | | | | | |
8 | 2 | NESTED LOOPS OUTER | | 1 | 310 | 1946 (1)| 00:00:24 |
9 | 3 | NESTED LOOPS | | 1 | 294 | 1944 (1)| 00:00:24 |
10 | 4 | NESTED LOOPS | | 1 | 236 | 1942 (1)| 00:00:24 |
11 | 5 | NESTED LOOPS ANTI | | 1 | 183 | 1936 (1)| 00:00:24 |
12 |* 6 | TABLE ACCESS BY INDEX ROWID| T_HW_EN | 1 | 119 | 1935 (1)| 00:00:24 |
13 |* 7 | INDEX RANGE SCAN | INX_REPORTDETAIL_EN_THEDATE | 11943 | | 54 (0)| 00:00:01 |
14 |* 8 | INDEX RANGE SCAN | BID_AD_SITE | 1 | 64 | 1 (0)| 00:00:01 |
15 |* 9 | TABLE ACCESS FULL | T_CRM_CITY | 5 | 265 | 6 (0)| 00:00:01 |
16 |* 10 | TABLE ACCESS BY INDEX ROWID | T_HW_BUILDING | 1 | 58 | 2 (0)| 00:00:01 |
17 |* 11 | INDEX RANGE SCAN | BUILDING_ID_IDX | 1 | | 1 (0)| 00:00:01 |
18 | 12 | TABLE ACCESS BY INDEX ROWID | T_CRM_CITY | 1 | 16 | 2 (0)| 00:00:01 |
19 |* 13 | INDEX RANGE SCAN | CITY_IDX | 1 | | 1 (0)| 00:00:01 |
20 |* 14 | FILTER | | | | | |
21 | 15 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
22 -----------------------------------------------------------------------------------------------------------------
23
24 Predicate Information (identified by operation id):
25 ---------------------------------------------------
26
27 1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "SYS"."DUAL" "DUAL" WHERE LNNVL(:B1<>'海南') AND
28 LNNVL(:B2<>'海南')))
29 6 - filter("HWBID" IS NOT NULL AND TRIM("SITE")<>'XX' AND TRIM("SITE")<>'YY' AND TRIM("SITE")<>'ZZ'
30 )
31 7 - access("THEDATE">=TRUNC(SYSDATE@!-7))
32 8 - access("HWBID"="G"."BUILDINGID" AND "SITE"="G"."SITE" AND "PDPSNAME"="G"."AD")
33 9 - filter(TRIM("TOUFANG_AREA")<>TRIM("C"."PROVINCE") AND TRIM("TOUFANG_AREA")<>TRIM("C"."CITY"))
34 10 - filter("B"."所在城市UID" IS NOT NULL AND "B"."项目类别"=1 AND "B"."所在城市UID"="C"."HUID")
35 11 - access("HWBID"="B"."楼盘ID")
36 13 - access("D"."CITY"(+)=TRIM("TOUFANG_AREA"))
37 14 - filter(LNNVL(:B1<>'海南') AND LNNVL(:B2<>'海南'))
不加trim的执行计划
1 Plan hash value: 2634927421
2
3 -----------------------------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 -----------------------------------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 18 | 5580 | 2183 (1)| 00:00:27 |
7 |* 1 | FILTER | | | | | |
8 |* 2 | HASH JOIN ANTI | | 18 | 5580 | 2165 (1)| 00:00:26 |
9 |* 3 | HASH JOIN OUTER | | 18 | 4428 | 2160 (1)| 00:00:26 |
10 |* 4 | HASH JOIN | | 18 | 4140 | 2153 (1)| 00:00:26 |
11 | 5 | TABLE ACCESS FULL | T_CRM_CITY | 2176 | 112K| 6 (0)| 00:00:01 |
12 |* 6 | HASH JOIN | | 7200 | 1244K| 2146 (1)| 00:00:26 |
13 |* 7 | TABLE ACCESS BY INDEX ROWID| T_HW_EN | 7200 | 836K| 1935 (1)| 00:00:24 |
14 |* 8 | INDEX RANGE SCAN | INX_REPORTDETAIL_EN_THEDATE | 11943 | | 54 (0)| 00:00:01 |
15 |* 9 | TABLE ACCESS FULL | T_HW_BUILDING | 31328 | 1774K| 210 (2)| 00:00:03 |
16 | 10 | TABLE ACCESS FULL | T_CRM_CITY | 2176 | 34816 | 6 (0)| 00:00:01 |
17 |* 11 | INDEX FAST FULL SCAN | BID_AD_SITE | 1069 | 68416 | 5 (0)| 00:00:01 |
18 |* 12 | FILTER | | | | | |
19 | 13 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
20 -----------------------------------------------------------------------------------------------------------------
21
22 Predicate Information (identified by operation id):
23 ---------------------------------------------------
24
25 1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "SYS"."DUAL" "DUAL" WHERE LNNVL(:B1<>'海南') AND
26 LNNVL(:B2<>'海南')))
27 2 - access("HWBID"="G"."BUILDINGID" AND "PDPSNAME"="G"."AD" AND "SITE"="G"."SITE")
28 3 - access("D"."CITY"(+)=TRIM("TOUFANG_AREA"))
29 4 - access("B"."所在城市UID"="C"."HUID")
30 filter(TRIM("TOUFANG_AREA")<>TRIM("C"."PROVINCE") AND TRIM("TOUFANG_AREA")<>TRIM("C"."CITY"))
31 6 - access("HWBID"="B"."楼盘ID")
32 7 - filter("SITE"<>'XX' AND "HWBID" IS NOT NULL AND "SITE"<>'YY' AND "SITE"<>'ZZ'
33 )
34 8 - access("THEDATE">=TRUNC(SYSDATE@!-7))
35 9 - filter("B"."所在城市UID" IS NOT NULL AND "B"."项目类别"=1)
36 11 - filter("G"."SITE"<>'XX' AND "G"."SITE"<>'YY' AND "G"."SITE"<>'ZZ'
37)
38 12 - filter(LNNVL(:B1<>'海南') AND LNNVL(:B2<>'海南'))