1.like 最右原则
select*from scott.emp e where e.ename like'W%';-- 高效select*from scott.emp e where e.ename like'%W%';-- 低select*from scott.emp e where e.ename like'%W';-- 低2.'表达式' 独立成行
select*from scott.emp e WHERE e.mgr >=700*10;-- 高效select*from scott.emp e WHERE e.mgr/700>=10;-- 低3. 避免在 '非函数索引' 列上使用 '函数'-- 隐式类型转换 同理select*from scott.emp e where e.empno like'78%';-- 高效select*from scott.emp e where substr(e.empno,1,2)='78';-- 低
3.3 减少对表的查询
-- sql 写法类似,语法仅供参考-- 情况1: where 条件中select t1.valuefrom table1 t1
where t1.col1 =(select t2.col1
from table2 t2
where t2.col ='520')and t1.col2 =(select t2.col2
from table2 t2
where t2.col ='520');-- 低select t1.valuefrom table1 t1
where(t1.col1, t1.col2)=(select t2.col1, t2.col2
from table2 t2
where t2.col ='520');-- 高效-- 情况2:子查询select(select t2.value1
from table2 t2
where t2.col = t1.col) value1,(select t2.value2
from table2 t2
where t2.col = t1.col) value2
from table1 t1
where t1.value='1314';-- 低select t2.value1,
t2.value2
from table1 t1,
table2 t2
where t2.col = t1.col
and t1.value='1314';-- 高效