Oracle SQL语句优化总结

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/oygx6670929/article/details/93514959

一、合理使用索引

创建索引的根本目的:提高查询效率

使用规则:

1.在条件中经常用到的不同值较多的列上建立索引

2.在不同值少的列上,不要建立索引及位图索引(不但不会提高查询效率,反而会降低更新速度

3.在经常进行连接的列上建立索引 

4.在频繁进行排序或分组的列上建立索引

如:order by D,E   需要注意:

在建立复合索引时,D列、E列和排序后面的顺序要相同

5.如果条件中经常对某个列应用某个函数后再指定查询条件,则应建立函数索引

如:select * from test where UPPER(owner)='BI'; 即使为条件列(owner列)建立了索引,当索引列上使用函数进行条件匹配,执行计划将不会选择索引扫描,这时可考虑建立函数索引:create index func_owner on test(UPPER(owner));

user_expressions 视图,主要针对函数索引,可以查看具体的函数信息。

以下情况,不会用到列上的索引:

1.存在数据类型的隐式转换

select * from emp where empno = '123';  --empno为数字型,oracle会先将字符串'123'隐式转化为数字123.

2.列上有数学运算

select * from emp where sal*2 < 1000 ; --在索引列上只有sal的值,并没有sal*2的值

3.使用不等于(<>)运算时 : where deptno<>10;

4.使用substr字符串函数时: where substr(ename,1,3) ='UZI';

5. '%'通配符放在前面时: where ename like '%UZI' ;

6.字符串连接( || )时 :  where ename || 'abc' ='UZIabc';

 

索引限制

即使相关字段上有索引,在如下4种情况,也不会走索引:

(1)    使用不等于操作符(<>、!=)    

(2)    使用IS NULL 或IS NOT NULL

(3)    使用函数

(4)    比较不匹配的数据类型  

所以在SQL 编写过程中,尽量避免以上4种情况。

 

10053事件

利用10053事件生成一个 SQL 分析过程的 trace 文件,分析一下执行计划的生成过程,看看到底是没有使用索引还是因为索引的成本比较大而选择了全表扫描。
1.先进行表分析:
exec dbms_ststs.gather_table_stats('user','tablename',cascade=>true);
2.开启10053事件:
alter session set events '10053 trace name context forever,level 1';
3.执行sql:
explain plan for  要运行的sql
4.关闭10053事件:
alter session set events '10053 trace name context off';

trace 文件的目录:$ORACLE_BASE/admin/sid/udump

 

查看真实执行计划:select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

二、避免或简化排序

1.以下是需要排序的情况,应当简化或避免对大型表进行重复的排序:

SQL中包含Group byOrder byDistinctMinusUnion字句;in子句中的SQL子查询

以下情况不能有效利用索引:

1.排序列没有全部建立索引:如 order by D,E ; 而表中只在D列建立了索引

2.排序列与索引列的顺序不同:如 Creat index emp_idx1 on emp(empno,ename)   ;    order by ename,empno ;

3.排序列来自不同的表 (索引不能跨表)

 

三、消除对大表的扫描

1.两个表进行连接时,对连接列创建索引

2.使用并集来避免顺序存取。

例如:(a,b列上都创建了索引) select * from abc where a>10 or b<10; 应当改写为:

select * from abc where a>10 

union 

select * from abc where b<10

 

四、Exists和IN运算符

1.带IN的关联子查询是多余的,因为IN字句和子查询中相关操作的功能是一样的。

如:select name from emp_mem em where emp_id  in (select emp_id from emp_info ei where em.emp_id = ei.emp_id )

2.非关联子查询不要使用Exists子句,因为这样会产生笛卡尔积

3.尽量不适用NOT IN,使用 minus 会更快一些

 

分页查询

方法一:适合小数据量

select * form

(select rownum as rn,d.* from emp d

where rownum <=20 )

where rn>=11 ;

方法二 速度教稳定 推荐

select * form

(select row_number() over(order by ID)  as rn,d.* from emp d )

where rn between 11 and 20 ;

方法三  不适合大数据量

select * from emp where rownum <= 20

minus

select * from emp  where rownum < 11 ;

 

 

 

 

展开阅读全文

oracle sql 语句优化

01-22

[code=sql]rnSELECTrn t.aoi_name,rn ( SELECT count(p.per_id) FROM d_person_info p, m_user_info u WHERE u.aoi_id = t.aoi_id and u.user_id = p.input_user and p.valid = '1' and p.input_date > to_date('2010-10-10','yyyy-mm-dd') and p.input_date < to_date('2012-10-10','yyyy-mm-dd')+1 ) v_person,rn ( SELECT count(p.per_id) FROM d_body_check_info p, m_user_info u WHERE u.aoi_id = t.aoi_id and u.user_id = p.input_user and p.valid = '1' and p.BCI_DATE > to_date('2010-10-10','yyyy-mm-dd') and p.BCI_DATE < to_date('2012-10-10','yyyy-mm-dd')+1 ) v_bodycheck,rn ( SELECT count(p.per_id) FROM D_NEWBORN_HOME_VISIT p, m_user_info u WHERE u.aoi_id = t.aoi_id and u.user_id = p.input_user and p.valid = '1' and p.new_visitDate > to_date('2010-10-10','yyyy-mm-dd') and p.new_visitDate < to_date('2012-10-10','yyyy-mm-dd')+1 ) v_born,rn ( SELECT count(p.per_id) FROM D_oneChild_health_exam p, m_user_info u WHERE u.aoi_id = t.aoi_id and u.user_id = p.input_user and p.valid = '1' and p.OC_visitDate > to_date('2010-10-10','yyyy-mm-dd') and p.OC_visitDate < to_date('2012-10-10','yyyy-mm-dd')+1 ) v_one,rn ( SELECT count(p.per_id) FROM D_twoChild_health_exam p, m_user_info u WHERE u.aoi_id = t.aoi_id and u.user_id = p.input_user and p.valid = '1' and p.TWC_visitDate > to_date('2010-10-10','yyyy-mm-dd') and p.TWC_visitDate < to_date('2012-10-10','yyyy-mm-dd')+1 ) v_two,rn ( SELECT count(p.per_id) FROM D_CHILD_HEALTH_EXAM p, m_user_info u WHERE u.aoi_id = t.aoi_id and u.user_id = p.input_user and p.valid = '1' and p.CHE_DATE > to_date('2010-10-10','yyyy-mm-dd') and p.CHE_DATE < to_date('2012-10-10','yyyy-mm-dd')+1 ) v_six,rn ( SELECT count(p.per_id) FROM D_FIRST_follow_service p, m_user_info u WHERE u.aoi_id = t.aoi_id and u.user_id = p.input_user and p.valid = '1' and p.ffs_fillDate > to_date('2010-10-10','yyyy-mm-dd') and p.ffs_fillDate < to_date('2012-10-10','yyyy-mm-dd')+1 ) v_first,rn ( SELECT count(p.per_id) FROM D_follow_service_info p, m_user_info u WHERE u.aoi_id = t.aoi_id and u.user_id = p.input_user and p.valid = '1' and p.fts_wVisDate > to_date('2010-10-10','yyyy-mm-dd') and p.fts_wVisDate < to_date('2012-10-10','yyyy-mm-dd')+1 ) v_second,rn ( SELECT count(p.per_id) FROM D_Postpartum_visits p, m_user_info u WHERE u.aoi_id = t.aoi_id and u.user_id = p.input_user and p.valid = '1' and p.pv_followDate > to_date('2010-10-10','yyyy-mm-dd') and p.pv_followDate < to_date('2012-10-10','yyyy-mm-dd')+1 ) v_postpartum,rn ( SELECT count(p.per_id) FROM D_Postpartum_healthCheck p, m_user_info u WHERE u.aoi_id = t.aoi_id and u.user_id = p.input_user and p.valid = '1' and p.phc_followDate > to_date('2010-10-10','yyyy-mm-dd') and p.phc_followDate < to_date('2012-10-10','yyyy-mm-dd')+1 ) v_postpartum_health,rn ( SELECT count(p.per_id) FROM D_DPF_Record_INFO p, m_user_info u WHERE u.aoi_id = t.aoi_id and u.user_id = p.input_user and p.valid = '1' and p.DPF_Date > to_date('2010-10-10','yyyy-mm-dd') and p.DPF_Date < to_date('2012-10-10','yyyy-mm-dd')+1 ) v_dpf,rn ( SELECT count(p.per_id) FROM D_HPFS_RECORD_INFO p, m_user_info u WHERE u.aoi_id = t.aoi_id and u.user_id = p.input_user and p.valid = '1' and p.HPF_DATE > to_date('2010-10-10','yyyy-mm-dd') and p.HPF_DATE < to_date('2012-10-10','yyyy-mm-dd')+1 ) v_hpfs,rn ( SELECT count(p.per_id) FROM D_GMIP_Follow_INFO p, m_user_info u WHERE u.aoi_id = t.aoi_id and u.user_id = p.input_user and p.valid = '1' and p.GMF_FollowDate > to_date('2010-10-10','yyyy-mm-dd') and p.GMF_FollowDate < to_date('2012-10-10','yyyy-mm-dd')+1 ) v_gmf,rn ( SELECT count(p.per_id) FROM D_Infectious_DIS p, m_user_info u WHERE u.aoi_id = t.aoi_id and u.user_id = p.input_user and p.valid = '1' and p.DID_DDate > to_date('2010-10-10','yyyy-mm-dd') and p.DID_DDate < to_date('2012-10-10','yyyy-mm-dd')+1 ) v_infectious,rn ( SELECT count(p.per_id) FROM D_Older_Case_Info p, m_user_info u WHERE u.aoi_id = t.aoi_id and u.user_id = p.input_user and p.valid = '1' and p.OLDC_follow_Date > to_date('2010-10-10','yyyy-mm-dd') and p.OLDC_follow_Date < to_date('2012-10-10','yyyy-mm-dd')+1 ) v_olderrn FROM m_admin_organ_info t , s_organ_info brn WHERE t.org_id = b.org_idrn and t.valid = '1' and t.area_sid like '36012210%' and b.ole_id='2'rn[/code]rnrn针对你的查询条件 来建立所以rn一般wherern里面包含的 条件建立个索引rn会快一点rnrn条件这里这么建立索引,啊。真心不这么会。 论坛

Oracle SQL语句优化技术分析

01-17

操作符优化 rnrn  IN 操作符 rnrn  用IN写出来的SQL的优点是比较容易写及清晰易懂,这比较适合现代软件开发的风格。 rnrn  但是用IN的SQL性能总是比较低的,从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别: rnrn  ORACLE试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了。 rnrn  推荐方案:在业务密集的SQL当中尽量不采用IN操作符。 rnrn  NOT IN操作符 rnrn  此操作是强列推荐不使用的,因为它不能应用表的索引。 rnrn  推荐方案:用NOT EXISTS 或(外连接+判断为空)方案代替 rnrn  <> 操作符(不等于) rnrn  不等于操作符是永远不会用到索引的,因此对它的处理只会产生全表扫描。 rnrn  推荐方案:用其它相同功能的操作运算代替,如 rnrn  a<>0 改为 a>0 or a<0 rnrn  a<>’’ 改为 a>’’ rnrn  IS NULL 或IS NOT NULL操作(判断字段是否为空) rnrn  判断字段是否为空一般是不会应用索引的,因为B树索引是不索引空值的。 rnrn  推荐方案: rnrn  用其它相同功能的操作运算代替,如 rnrn  a is not null 改为 a>0 或a>’’等。 rnrn  不允许字段为空,而用一个缺省值代替空值,如业扩申请中状态字段不允许为空,缺省为申请。 rnrn  建立位图索引(有分区的表不能建,位图索引比较难控制,如字段值太多索引会使性能下降,多人更新操作会增加数据块锁的现象)。 rnrn  > 及 < 操作符(大于或小于操作符) rnrn  大于或小于操作符一般情况下是不用调整的,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化,如一个表有100万记录,一个数值型字段A,30万记录的A=0,30万记录的A=1,39万记录的A=2,1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了,因为A>2时ORACLE会先找出为2的记录索引再进行比较,而A>=3时ORACLE则直接找到=3的记录索引。 rnrn  LIKE操作符 rnrn  LIKE操作符可以应用通配符查询,里面的通配符组合可能达到几乎是任意的查询,但是如果用得不好则会产生性能上的问题,如LIKE ‘%5400%’ 这种查询不会引用索引,而LIKE ‘X5400%’则会引用范围索引。一个实际例子:用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描,如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询,性能肯定大大提高。 rnrnhttp://develop.csai.cn/dbms/200701051434361858.htm 论坛

没有更多推荐了,返回首页