优化前：

sql
SELECT 
        /*+ mapjoin(dd),skewjoin(a(url))*/
        a.* except(url_tag, join_key),
        CASE 
            WHEN cc.url IS NOT NULL THEN 'black_url'
            WHEN dd.url IS NOT NULL THEN 'black_url'
            ELSE ''
        END AS url_tag
    FROM
    (
        select 
            *,
            1 AS join_key
        from 
            dwd_crawler_urls_step1_di a 
        where 
            ymd = '${P_DATE}'
            AND url_tag != 'black_url'
    ) as
    LEFT JOIN 
        black_urls2 cc  
        ON replace(a.host, 'www.', '') = replace(cc.url, 'www.', '')
	LEFT JOIN 
        black_urls1 dd  
        ON INSTR(a.url, dd.url) > 0

优化后

sql
SELECT 
        /*+ mapjoin(dd),skewjoin(a(url))*/
        a.* except(url_tag, join_key),
        CASE 
            WHEN cc.url IS NOT NULL THEN 'black_url'
            WHEN dd.url IS NOT NULL THEN 'black_url'
            ELSE ''
        END AS url_tag
    FROM
    (
        select 
            *,
            1 AS join_key
        from 
            dwd_crawler_urls_step1_di a 
        where 
            ymd = '${P_DATE}'
            AND url_tag != 'black_url'
    ) a
    LEFT JOIN 
        black_urls1 dd  
        ON INSTR(a.url, dd.url) > 0
    LEFT JOIN 
        black_urls2 cc  
        ON replace(a.host, 'www.', '') = replace(cc.url, 'www.', '')

优化前执行时间

优化后执行时间