《SQLO优化简介》由会员分享，可在线阅读，更多相关《SQLO优化简介（9页珍藏版）》请在金锄头文库上搜索。
1、analyze table testdate compute statistics for table for all indexes for all indexed columns;写出高效的SQL在多数情况下，Oracle使用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。但是， 如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器删去索引而使用全表扫描，一般 就这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应清楚优化器根据何种原则来删除索 引，这有助于写出高性能的SQL语句。1.IS NULL 与 IS NOT NULL避免在索引中使用任何可以为
2、空的列，ORACLE将无法使用该索引。对于单列索引，如果列包含空值，索引中将不存在此记录；对于复合索引，如果每个列都为空，索引中同样不存在此记录；如果至少有一个列不为空，则记录存在于索引中。举例：如果唯一性索引建立在表的A列和B列上，并且表中存在一条记录的A, B值为(123, null), ORACLE将不接受下一条具有相同A, B值(123,null)的记录(插入)，然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空。因此你可以插入1000条具有相同键值的记录，当然它们都是空！因为空值不存在于索引列中，所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引。
3、不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况 下，只要这些列中有一列含有null,该列就会从索引中排除。也就是说如果某列存在空值，即使对该列 建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。注意：如果我们必须要用is null,又需要提供査询效率可以用函数索引 实例如下：create table test_date (name varchar2(2O),day date);insert into test_date(name ,day) values (*lucynull);i
4、nsert into test_date(nanie ,day) values (Jonynull);insert into test_date(name,day) values (jamessysdate);select * from test_date;-创建decode函数索引来代替create index finx_day on test_date(decode(dayjiull/N Y1)-使用decode判断来代替is null判断select * from test_date a where decode(day,null/N,Y,) = NSELECT STATEMENT. G
5、OAL = CHOOSETABLE ACCESS FULLObject owner=SP2Object name=TEST_DATE注意要使用cost才会使用function indexCost=lCardinality=lBytes=llObject name=TEST_DATECost=lObject name=FINX_DAYCost=2select * from test_date a where decodc(day,nuIl/N7Y,) = N SELECT STATEMENT. GOAL = CHOOSETABLE ACCESS BY INDEX ROWID Object own
6、er=SP2 Cardinality=lBytes=l 1INDEX RANGE SCANObject owner=SP2Cardinality 12.联接列对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。我们一起来看一个 例子，假定有一个职工表(employee),对于一个职工的姓和名分成两列存放(FIRST_NAME和LAST_NAME), 现在要查询一个叫比尔.克林顿(B订1 Cliton)的职工。下面是一个采用联接查询的SQL语句，select * from employs wherefirst_name last_name Beill Cliton上面这条语句完
7、全可以查询出是否有Bill Cliton这个员工，但是这里需要注意，系统优化器对基 于last_name创建的索引没有使用。当釆用下面这种SQL语句的编写，Oracle系统就可以采用基于last_name创建的索引。Select * from employee wherefirst_name Beill* and last_name 二Cliton遇到下面这种情况乂如何处理呢？如果一个变量(name)中存放着Bill Cliton这个员工的姓名， 对于这种情况我们乂如何避免全程遍历，使用索引呢？可以使用一个函数，将变量name中的姓和名分 开就可以了，但是有一点需要注意，这个函数是不能作用在索
8、引列上。下面是SQL查询脚本：select * from employeewherefirst_name 二 SUBSTR (&name, 1, INSTR (&name, )T)andlast_name = SUBSTR (&name，INSTR (，&name9 )+1)注：substr和instr用法1) x substr(string string, int a, int b)参数1:string要处理的字符串参数2： a徵取字符串的开始位宜(起始位迓是0)参数3： b截取的字符串的长度(而不是字符串的结束位置)例如：substr (ABCDEFG 0) ; /返回：ABCDEFG.截
9、取所有字符substr (ABCDEFG*, 2); /返回：CDEFG,截取从C开始之后所有字符substr (ABCDEFG, 0, 3); /返回：ABC,截取从 A 开始 3 个字符substr (ABCDEFG, 0, 100); /返回：ABCDEFG. 100虽然超出侦处理的字符串最长度，但不会影响返回结果，系统按倾处理字符串最大 数虽返回。substr (ABCDEFG 0, -3); /返回：EFG,注意参数3 为负值时表示从尾部开始算起,字符串排列位置不变2) 、instr的语法如下：instr( stringl, string2 , start_position , nt
10、h_appearance :)函数说明：该函数返回参数string?在参数stringl中的位宜cstart_position参数表示将从string 1的第几个字符开始來査找string2a该参数为可选参数，如果省略，那么默认为X instr函数默 认的査找顺序为从左到右。当该参数为负数的时候，则从右边开始査找。nth.appearance表示返回第几次出现的stnng2的位宜。该参数为可选参数，如果省略则默认为1,也就是返回首次出现string2的位宜。示例：SELECT INSTR( CORPORATE FLOOR , OR , -1, 1) waaaw FROM DUAL;aaa143
11、.带通配符()的like语句同样以上面的例子来看这种情况。H前的需求是这样的，要求在职工表中查询名字中包含cliton 的人。可以釆用如下的查询SQL语句：select * from employee where last_name like J %cliton%这里由于通配符()在搜寻词首出现，所以Oracle系统不使用last_name的索引。在很多情况下 可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在 字符吊其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用：select * from employee where last_name
12、 like 4. Order by 语句ORDER BY语句决定了 Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特 别的限制，也可以将函数加入列中（象联接或者附加等）。任何在Order by语句的非索引项或者有计 算表达式都将降低査询速度。仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是 重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by 子句中使用表达式。5. NOT的理想替代方案我们在查询时经常在where子句使用一些逻辑表达式，如大于、小于、等于以及不等于等
13、等，也可 以使用and （与）、or （或）以及not （非）。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子 句的例子： where not （status 二VALID）如果要使用NOT,则应在取反的短语前面加上括号，并在短语前面加上0T运算符。NOT运算符包 含在另外一个逻辑运算符中，这就是不等于（；）运算符。换句话说，即使不在查询where子句中显 式地加入NOT词，NOT仍在运算符中，见下例： where status INVALID再看下面这个例子：select * from employee where salary3000;对这个查询，可以改写为不使用NOT：selec
14、t * from employee where salary3000 or salary；3000;虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许 Oracle对salary列使用索引,而第一种查询则不能使用索引。6. IN和EXISTS（下面有个重复的）有时候会将一列和一系列值相比较。最简单的办法就是在where子句中使用子查询。在where子句 中可以使用两种格式的子查询。第一种格式是使用IN操作符： where column in(select * from where );笫二种格式是使用EXIST操作符: where exists (select
15、 X from . . where .);我相信绝大多数人会使用第一种格式，因为它比较容易编写，而实际上第二种格式要远比第一种格 式的效率高。在Oracle中可以儿乎将所有的IN操作符子查询改写为使用EXISTS的子查询。第二种格式中，子查询以select X开始。运用EXISTS子句不管子查询从表中抽取什么数据它 只查看where子句。这样优化器就不必遍历整个表而仅根据索引就可完成工作(这里假定在where语句 中使用的列存在索引)。相对于IN子句来说，EXISTS使用相连子查询，构造起来要比IN子查询困难一 些。通过使用EXIST, Oracle系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就 节省了时间。Oracle系统在执行子查询时，首先执行子查询，并将获得的结果列表存放在在一个加 了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以 后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因。同时应尽可能使用NOT EXISTS来代替NOT IN,尽管二者都使用了 NOT (不能使用索引而降低速度)， NO
