【网学网提醒】：网学会员编辑为广大网友搜集整理了：sql优化技巧绩等信息，祝愿广大网友取得需要的信息，参考学习。
一、关于索引的知识要写出运行效率高的sql，需要对索引的机制有一定了解，下面对索引的基本知识做一介绍。1、索引的优点和局限索引可以提高查询的效率，但会降低dml操作的效率。所以建立索引时需要权衡。对于dml操作比较频繁的表，索引的个数不宜太多。2、什么样的列需要建索引？经常用于查询、排序和分组的列（即经常在where、order或groupby子句中出现的列）。3、主键索引和复合索引对于一张表的主键，系统会自动为其建立索引。如果一张表的几列经常同时作为查询条件，可为其建立复合索引。4、建立索引的语句createcreateindexi_staffindexi_agentononstaffagent(empno);(empno,start_date);
5、删除索引的语句dropdropindexindexI_staff;I_agent;
6、查询索引的语句法一：利用数据字典表一：all_indexes例如：select查看一张表有哪些索引以及索引状态是否有效table_name,statusstatus主要字段：index_name,
index_name,
fromall_indexeswheretable_name=’STAFF_INFO’;STATUS----------VALID查看一张表在哪些字段上建了索引index_name,column_name,column_position
INDEX_NAME--------------------I_STAFF表二：all_ind_columns例如：select主要字段：table_name,
index_name,
column_name,
column_position
fromall_ind_columnswheretable_name=’AGENT’COLUMN_NAME----------------------EMPNOSTART_DATECOLUMN_POSITON-------------------------12
INDEX_NAME--------------------I_AGENTI_AGENT法二：利用toad工具
由此可见，agent表中有一个复合索引（empno,start_date）
toad用户界面比sql*plus友好，并且功能强大。你可以在toad编辑器中键入表名，按F4，便可见到这张表的表结构以及所有索引列等基本信息。7、索引的一些特点1):不同值较多的列上可建立检索，不同值少的列上则不要建。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就没必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。2):如果在索引列上加表达式，则索引不能正常使用例如：b1,c1分别是表b,c的索引列selectselect*fromb*fromcwhereb1/30<1000;whereto_char(c1,’YYYYMMDDHH24:MI:SS’)=‘20020314:01:01’;
以上都是不正确的写法3):where子句中如果使用in、or、like、!=，均会导致索引不能正常使用例如：select*frombwhereb1=30orb1=40;4):使用复合索引进行查询时必须使用前置列例如表a上有一个复合索引（c1,c2,c3）,则c1为其前置列如果用c1或c1+c2或c1+c2+c3为条件进行查询，则该复合索引可以发挥作用，反之，用c2或c3或c2+c3进行查询，则该索引不能起作用。
二.书写sql注意事项：1、避免给sql语句中引
用的索引列添加表达式：典型实例：b1,c1分别是表b,c的索引列：1）select2）select替代方案：1)2)select*frombwhereb1<30000;*fromb*fromcwhereb1/30<1000;whereto_char(c1,’YYYYMMDDHH24:MI:SS’)=‘20020314:01:01’;
select*fromc
wherec1=to_date(‘2002030114:01:01’,‘YYYYMMDDHH24:MI:SS’);
注：在lbs中有两个重要字段，pol_info中的undwrt_date和prem_info中的payment_date，这两个日期是带时分秒的，所以经常有同事用to_char来查询某一时间段的数据。例如：selectselect替代方案：selectcount(*)frompol_infoandcount(*)count(*)frompol_infofromprem_infowherewhereto_char(undwrt_date,’YYYYMMDD’)=’20020416’;to_char(undwrt_date,’YYYYMM’)=’200203’;
where
undwrt_date>=to_date(’20020416’,’YYYYMMDD’)undwrt_date
select
count(*)
fromprem_infoand
where
payment_date>=to_date(’20020301’,’YYYYMMDD’)payment_date 2、避免在where子句中使用in、or、like、!=典型实例：
a1是a表上的索引列：1)selectwhere2)select*froma(a1=‘0’and...)orcount(*)(a1=‘1’and...);a1in(‘0’,’1’);
fromawhere
替代方案：1）selectunionselect2)selectselect小结：对字段使用了‘in，or，like’做条件、对字段使用了不等号‘!=’，均会使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引，或者使用union连结符代替。另一种方式是使用存储过程，它使SQL变得更加灵活和高效。3、建立适当的索引曾经接过开发的一个统计sql，select…fromtableawherecola=…and…运行效率非常慢，经查tablea数据量巨大，再查all_ind_columns，发现cola是tablea的一个复合索引中的一列，但不是前置列。象这种情况，就需要与开发商量，是否针对cola建一个索引。4、like和substr对于‘like’和‘substr’，其效率并没有多大分别。但是，当所搜索的值不存在时，使用‘like’的速度明显大于‘substr’。所以：selectselect*froma*fromawherewheresubstr(a1,1,4)='5378'a1like‘5378%’;可以用like替代*fromacount(*)count(*)wherea1=‘1’and...*fromawherea1=‘0’and...
fromawhere
a1=‘0’;
fromawherea1=‘1’;
然后做一次加法运算；或者直接用存储过程来实现；
5、写where条件时，有索引字段的判断在前，其它字段的判断在后；如果where条件中用到复合索引，按照索引列在复合索引中出现的顺序来依次写where条件；6、使用多表连接时，在from子句中，将记录数少的表放在后面，可提高执行效率；
7、避免使用notinnotin是效率极低的写法，尽量使用minus
或外连接加以替代典型实例：1)selectcol1fromtab1wherecol1notin(selectcol1fromtab2);2)selectsum(col2)fromtab1wherecol1notin(selectcol1fromtab2);替代方案selectcol1fromtab1minusselectsum(a.col2)selectcol1fromtab2;b
fromtab1a,tab2
wherea.col1=b.col2(+)andb.col1isnull;
8、多表查询时，如果其中一个表的记录数量明显大于其他表，则可以先对此表进行查询后，再与其他小表进行表连接。
典型实例：selecta.plan_code,b.dno,c,tno,sum(a.tot_modal_prem),plan_typec
fromprem_infoa,where
dept_refb,
substr(a.deptno,1,7)=substr(b.deptno,1,7)anda.plan_code=c.plan_code
groupbyb.dno,替代方案：selectb.dno,
c.tno,
a.plan_code;
c.tno,
a.plan_code,deptno,
a.tot_amountsum(tot_modal_prem)tot_amount
from(select
plan_code,
fromprem_infogroupbydeptno,plan_code)adept_refb,plan_typewherec
substr(a.deptno,1,7)=substr(b.deptno,1,7)anda.plan_code=c.plan_codec.tno,a.plan_code;
groupbyb.dno,小结：
由于prem_info表的记录数远远大于dept_ref表和plan_type表中的记录数，所以首先从prem_info表中查询需要的记录，此时记录数已经被大量缩小，然后再和其他两个表连接，速度会得到很大改善！9、查询数量较大时，使用表连接代替IN，EXISTS，NOTIN，NOTEXISTS等。典型实例：a、使用IN：selectsum(col2)fromtab1wherecol1in(selectcol1fromtab2);使用EXISTS:：selectsum(col2)fromtab1awhereexists(select*fromtab2wherecol1=a.col1);b、使用NOTIN：selectsum(col2)fromtab1wherecol1notin(selectcol1fromtab2);使用NOTEXISTS：selectsum(col2)fromtab1awherenotexists(select*fromtab2wherecol1=a.col1);替代方案：a、使用连接：selectsum(a.col2)fromtab1a,tab2bwherea.col1=b.col2;b、使用外连接：selectsum(a.col2)fromtab1a,tab2bwherea.col1=b.col2(+)andb.col1isnull;
