今天写了一个SQL,表数据太大,表索引也加了,可和另一张表关联一起查一个月的数据总是很慢,大约要60多秒。优化后只用了0.07秒,记录一下防止以后忘记了。
select /*+leading(t) use_nl(t, d)*/ t.a,t.b,t.c, d.a,d.b,d.c
from tablea t, tableb d
where t.id = d.id
and t.a is not null
and t.b is not null
and t.time >= to_date('2021-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and t.time <= to_date('2021-01-26 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
说明:
/*+LEADING(TABLE)*/ 将指定的表作为连接次序中的首表.
/*+USE_NL(TABLE1, TABLE2, TABLE3...)*/ 将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.
1、表最好都加索引,会提高查询速度
2、LEADING中 TABLE为小表,用小表做为驱动表,小表多用where筛选去掉大量不必要的数据
3、查出的数据最好不要用*,如t.*,d.*,最好只把要用到的数据查出来
成本计算方法:(引用其他博文)
设小表100行,大表100000行。
两表均有索引:
如果小表在内,大表在外(驱动表)的话,则扫描次数为:
100000+100000*2 (其中2表示IO次数,一次索引,一次数据)
如果大表在内,小表在外(驱动表)的话,则扫描次数为:
100+100*2.
两表均无索引:
如果小表在内,大表在外的话,则扫描次数为:
100000+100*100000
如果大表在内,小表在外的话,则扫描次数为:
100+100000*100
注意:如果一个表有索引,一个表没有索引,ORACLE会将没有索引的表作驱动表。如果两个表都有索引,则外表作驱动表。如果两个都没索引的话,则也是外表作驱动表。
2、使用index直接匹配索引来查询数据提高查询速度
/*+INDEX(TABLE INDEX_NAME)*/ 表明对表选择索引的扫描方法.
TABLE为表名 INDEX_NAME为索引名
select /*+ INDEX(tablea IDX_ID) */ t.a,t.b,t.c from tablea t