做搞机网站,天津智能网站建设,专业做网带,秦皇岛乾兴建设RT,该字段未建立索引#xff0c;以下贴出SQL,及执行计划#xff0c;不加trim走hash join#xff0c;求解释#xff01;
----------------------语句如下#xff0c;标红的字段加trim()
EXPLAIN PLAN FOR select a.楼盘id,
a.监测明细id,
a.报告日期,
a.广告位名称,
…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(THEDATETRUNC(SYSDATE!-7))
32 8 - access(HWBIDG.BUILDINGID AND SITEG.SITE AND PDPSNAMEG.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.所在城市UIDC.HUID)
35 11 - access(HWBIDB.楼盘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(HWBIDG.BUILDINGID AND PDPSNAMEG.AD AND SITEG.SITE)
28 3 - access(D.CITY()TRIM(TOUFANG_AREA))
29 4 - access(B.所在城市UIDC.HUID)
30 filter(TRIM(TOUFANG_AREA)TRIM(C.PROVINCE) AND TRIM(TOUFANG_AREA)TRIM(C.CITY))
31 6 - access(HWBIDB.楼盘ID)
32 7 - filter(SITEXX AND HWBID IS NOT NULL AND SITEYY AND SITEZZ
33 )
34 8 - access(THEDATETRUNC(SYSDATE!-7))
35 9 - filter(B.所在城市UID IS NOT NULL AND B.项目类别1)
36 11 - filter(G.SITEXX AND G.SITEYY AND G.SITEZZ
37)
38 12 - filter(LNNVL(:B1海南) AND LNNVL(:B2海南))
