join优化

1.left outer join先执行连接操作,再将结果通过WHERE语句进行过滤

select s.ymd,s.symbol,s.price_close,d.dividend

from stocks s

left outer join

dividends d

on s.ymd=d.ymd and s.symbol and d.symbol

where s.symbol="AAPL"

2.left outer join 先执行where过滤,再执行连接操作

select s.ymd,s.symbol,s.price_close,d.dividend

(select * from stocks where  symbol="AAPL") s

left outer join

(select * from dividends where symbol="AAPL") d

on s.ymd=d.ymd 

3.内连接,可以将过滤条件放到on语句中

4. 将stocks表作为驱动表,虽然在查询中不是位于最后面的

select /*+STREAMTABLE(s)* / s.ymd,s.symbol,s.price_close,d.dividend

from stocks s

left outer join

dividends d

on s.ymd=d.ymd and s.symbol and d.symbol

where s,symbol="AAPL"

5. MAPJOIN 不支持右外连接(right outer join)和全外连接(full outer join)

select /*+MAPJOIN(s)* / s.ymd,s.symbol,s.price_close,d.dividend

from stocks s

JOIN(INNER JOIN)

dividends d

on s.ymd=d.ymd and s.symbol and d.symbol

where s,symbol="AAPL"

6.笛卡儿积不支持优化

select * from stocks join dividends

 

posted on 2019-06-30 12:08  happygril3  阅读(103)  评论(0)    收藏  举报

导航