Oracle SQL优化

Oracle SQL优化

数据访问数据方式

　　1.全表扫描

　　2.索引扫描 ： index --> rowID -- >数据

　　(1) 扫描索引得到对应的rowid值。 --可能从内存中得到

　　(2) 通过找到的rowid从表中读出具体的数据。--物理IO，消耗也大

　　　　索引唯一扫描(index unique scan) ：返回单个ROWID，如：存在UNIQUE 或PRIMARY KEY 约束
　　　　索引范围扫描(index range scan) ：通常是组合索引，返回多行数据
　　　　索引全扫描(index full scan) ：只返回索引列时
　　　　索引快速扫描(index fast full scan) ：只返回索引列时，不对数据排序-不带order by情况下常发生

　　3.rowId（包括索引）：包括where中有rowID 及 索引方式

 

SQL调优使用技巧

　　★索引要定期维护，定期重建

　　★索引上不用NOT、IS NULL和IS NOT NULL：用了将进行全表扫描

　　★避免使用前置通配符：LIKE '%109204421' 通配符在前面不走索引

　　★索引列是函数不走索引，如 businessNo * 12 >500

　　★将*转换成具体的列名称

　　★用EXISTS替换DISTINCT
　　例如:

　　低效:
　　　　SELECT DISTINCT DEPT_NO,DEPT_NAME
　　　　FROM DEPT D,EMP E
　　　　WHERE D.DEPT_NO = E.DEPT_NO

　　高效:
　　　　SELECT DEPT_NO,DEPT_NAME
　　　　FROM DEPT D
　　　　WHERE EXISTS ( SELECT ‘X’
　　　　FROM EMP E
　　　　WHERE E.DEPT_NO = D.DEPT_NO);

　　★UNION-ALL 代替 UNION

　　★索引有多个列中，只有第一个列在Where中才会用到索引。

　　★避免出现索引列自动转换：内部发生的类型转换, 这个索引将不会被用到。
　　　　如 WHERE USER_NO = 109204421，USER_NO为字符串，
　　　　那么会自动转换成number来比较，这样就不走索引了。

　　★在查询时尽量少用格式转换：
　　　　如用：WHERE a.order_no = b.order_no
　　　　不用：WHERE TO_NUMBER (substr(a.order_no, instr(b.order_no, '.') - 1) = TO_NUMBER (substr(a.order_no, instr(b.order_no, '.') - 1)

　　★减少对表的查询
　　例如:
　　　　低效
　　　　SELECT TAB_NAME
　　　　FROM TABLES
　　　　WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604)

　　　　AND DB_VER= ( SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)
　　高效
　　　　SELECT TAB_NAME
　　　　FROM TABLES
　　　　WHERE (TAB_NAME,DB_VER)= ( SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604)

　　★尽可能使用NOT EXISTS来代替NOT IN

 

　　★用>= 替代 >

　　★外部联接"+"的用法 ：
　　　　外部联接"+"按其在"="的左边或右边分左联接和右联接。
　　　　若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行，
　　　　则前者的行与后者中的一个空行相匹配并被返回。
　　　　利用外部联接"+"，可以替代效率十分低下的 not in 运算，大大提高运行速度。
　　　　例如，下面这条命令执行起来很慢：
　　　　select a.empno from emp a where a.empno not in (select empno from emp1 where job='SALE');

　　　　利用外部联接，改写命令如下:

　　　　select a.empno from emp a ,emp1 b
　　　　where a.empno=b.empno(+)
　　　　and b.empno is null and b.job='SALE';