网站开发有侵权吗,好的公文写作网站,编程网课平台哪个好,网络网站建2. 给优化器更明确的命令 自动选择索引 如果表中有两个以上#xff08;包括两个#xff09;索引#xff0c;其中有一个唯一性索引#xff0c;而其他是非唯一性#xff0e; 在这种情况下#xff0c;ORACLE将使用唯一性索引而完全忽略非唯一性索引#xff0e; 举例: SELEC… 2. 给优化器更明确的命令 自动选择索引 如果表中有两个以上包括两个索引其中有一个唯一性索引而其他是非唯一性 在这种情况下ORACLE将使用唯一性索引而完全忽略非唯一性索引 举例: SELECT ENAME FROM EMP WHERE EMPNO 2326 AND DEPTNO 20 ; 这里只有EMPNO上的索引是唯一性的所以EMPNO索引将用来检索记录 TABLE ACCESS BY ROWID ON EMP INDEX UNIQUE SCAN ON EMP_NO_IDX 至少要包含组合索引的第一列 如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. SQL create table multiindexusage ( inda number , indb number , descr varchar2(10)); Table created. SQL create index multindex on multiindexusage(inda,indb); Index created. SQL set autotrace traceonly SQL select * from multiindexusage where inda 1; Execution Plan ---------------------------------------------------------- SELECT STATEMENT OptimizerCHOOSE0 TABLE ACCESS (BY INDEX ROWID) OF MULTIINDEXUSAGE1 INDEX (RANGE SCAN) OF MULTINDEX (NON-UNIQUE) SQL select * from multiindexusage where indb 1; Execution Plan ---------------------------------------------------------- SELECT STATEMENT OptimizerCHOOSE0 TABLE ACCESS (FULL) OF MULTIINDEXUSAGE 很明显 当仅引用索引的第二个列时 优化器使用了全表扫描而忽略了索引 避免在索引列上使用函数 WHERE子句中如果索引列是函数的一部分优化器将不使用索引而使用全表扫描 举例: 低效 SELECT . FROM DEPT WHERE SAL * 12 25000; 高效: SELECT . FROM DEPT WHERE SAL 25000/12; 避免使用前置通配符 WHERE子句中, 如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始, 索引将不被采用. SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES WHERE USER_NO LIKE %109204421; 在这种情况下ORACLE将使用全表扫描. 避免在索引列上使用NOT 通常我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他 就 会停止使用索引转而执行全表扫描.举例: 低效: (这里,不使用索引) SELECT . FROM DEPT WHERE DEPT_CODE NOT 0; 高效: (这里,使用了索引) SELECT . FROM DEPT WHERE DEPT_CODE 0; 避免在索引列上使用 IS NULL和IS NOT NULL 避免在索引中使用任何可以为空的列ORACLE将无法使用该索引 对于单列索引如果列包含空值索引中将不存在此记录. 对于复合索引如果每个列都为空索引中同样不存在此 记录. 如果至少有一个列不为空则记录存在于索引中 如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值123,null的记录(插入). 然而如果所有的索引列都为空ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000条具有相同键值的记录,当然它们都是空! 因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引. 任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。 避免出现索引列自动转换 当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换. 假设EMP_TYPE是一个字符类型的索引列. SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES WHERE USER_NO 109204421 这个语句被ORACLE转换为: SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES WHERE TO_NUMBER(USER_NO) 109204421 因为内部发生的类型转换, 这个索引将不会被用到! 在查询时尽量少用格式转换 如用 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) 3.减少访问次数 减少访问数据库的次数 当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少 ORACLE的工作量. 类比工程实施 使用DECODE来减少处理时间 例如: SELECT COUNT(*)SUM(SAL) FROM EMP WHERE DEPT_NO 0020 AND ENAME LIKE ‘SMITH%’; SELECT COUNT(*)SUM(SAL) FROM EMP WHERE DEPT_NO 0030 AND ENAME LIKE ‘SMITH%’; 你可以用DECODE函数高效地得到相同结果 SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT, COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT, SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL FROM EMP WHERE ENAME LIKE ‘SMITH%’; 减少对表的查询 在含有子查询的SQL语句中,要特别注意减少对表的查询.例如: 低效 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) 4. 细节上的影响 WHERE子句中的连接顺序 ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理, 当在WHERE子句中有多个表联接时WHERE子句中排 在最后的表应当是返回行数可能最少的表有过滤条件的子句应放在WHERE子句中的最后。 如设从emp表查到的数据比较少或该表的过滤条件比较确定能大大缩小查询范围则将最具有选择性部分放在WHERE子句中的最后 select * from emp e,dept d where d.deptno 10 and e.deptno 30 ; 如果dept表返回的记录数较多的话上面的查询语句会比下面的查询语句响应快得多。 select * from emp e,dept d where e.deptno 30 and d.deptno 10 ; WHERE子句 ——函数、表达式使用 最好不要在WHERE子句中使用函或表达式如果要使用的话最好统一使用相同的表达式或函数这样便于以后使用合理的索引。 Order by语句 ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制也可以将函数加入列中象联接或 者附加等。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。 仔细检查order by语句以找出非索引项或者表达式它们会降低性能。解决这个问题的办法就是 重写order by语句以使用索引也可以为所使用的列建立另外一个索引同时应绝对避免在order by子句中使用表达式。 联接列 对于有联接的列即使最后的联接值为一个静态值优化器是不会使用索引的。 select * from employss where first_name||||last_name Beill Cliton; 系统优化器对基于last_name创建的索引没有使用。 当采用下面这种SQL语句的编写Oracle系统就可以采用基于last_name创建的索引。 select * from employee where first_name Beill and last_name Cliton; 带通配符%的like语句 通配符%在搜寻词首出现Oracle系统不使用last_name的索引。 select * from employee where last_name like %cliton%; 在很多情况下可能无法避免这种情况但是一定要心中有底通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时优化器就能利用索引。在下面的查询中索引得到了使用 select * from employee where last_name like c%; 用Where子句替换HAVING子句 避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. 例如: 低效: SELECT REGIONAVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION REGION ! ‘SYDNEY’ AND REGION ! ‘PERTH’ 高效 SELECT REGIONAVG(LOG_SIZE) FROM LOCATION WHERE REGION REGION ! ‘SYDNEY’ AND REGION ! ‘PERTH’ GROUP BY REGION 顺序 WHERE GROUP HAVING 用NOT EXISTS 替代 NOT IN 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历).使用NOT EXISTS 子句可以有效地利用索引。尽可能使用NOT EXISTS来代替NOT IN尽管二者都使用了NOT不能使用索引而降低速度NOT EXISTS要比NOT IN查询效率更高。 例如: 语句1 SELECT dname, deptno FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp); 语句2 SELECT dname, deptno FROM dept WHERE NOT EXISTS (SELECT deptno FROM emp WHERE dept.deptno emp.deptno); 2要比1的执行性能好很多。 因为1中对emp进行了full table scan,这是很浪费时间的操作。而且1中没有用到emp的index 因为没有where子句。而2中的语句对emp进行的是缩小范围的查询。 用索引提高效率 索引是表的一个概念部分,用来提高检索数据的效率ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key) 的唯一性验证。 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引 是有必要的。 避免在索引列上使用计算 WHERE子句中如果索引列是函数的一部分优化器将不 使用索引而使用全表扫描 低效 SELECT . FROM DEPT WHERE SAL * 12 25000; 高效: SELECT . FROM DEPT WHERE SAL 25000/12; 用 替代 如果DEPTNO上有一个索引。 高效: SELECT * FROM EMP WHERE DEPTNO 4 低效: SELECT * FROM EMP WHERE DEPTNO 3 通过使用、等避免使用NOT命令 例子 select * from employee where salary 3000; 对这个查询可以改写为不使用NOT select * from employee where salary3000 or salary3000; 虽然这两种查询的结果一样但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引而第一种查询则不能使用索引。 如果有其它办法不要使用子查询。 外部联接的用法 外部联接按其在的左边或右边分左联接和右联接。若不带运算符的表中的一个行不直接匹配于带预算符的表中的任何行 则前者的行与后者中的一个空行相匹配并被返回。利用外部联接可以替代效率十分低下的 not in 运算大大提高运行速度。例如下面这条命令执行起来很慢 select a.empno from emp a where a.empno not in (select empno from emp1 where jobSALE); 利用外部联接改写命令如下: select a.empno from emp a ,emp1 b where a.empnob.empno() and b.empno is null and b.jobSALE; 这样运行速度明显提高. 尽量多使用COMMIT 事务是消耗资源的大事务还容易引起死锁 COMMIT所释放的资源: 回滚段上用于恢复数据的信息. 被程序语句获得的锁 redo log buffer 中的空间 ORACLE为管理上述3种资源中的内部花费 用TRUNCATE替代DELETE 当删除表中的记录时,在通常情况下, 回滚段 (rollback segments ) 用来存放可以被恢复的信息. 如果你没有 COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准 确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的 信息.当命令运行后,数据不能被恢复.因此很少的资源被调用, 执行时间也会很短. 计算记录条数 和一般的观点相反, count(*) 比count(1)稍快 , 当然如果可 以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO) 字符型字段的引号 比如有的表PHONE_NO字段是CHAR型,而且创建有索引 但在WHERE条件中忘记了加引号就不会用到索引。 WHERE PHONE_NO‘13920202022’ WHERE PHONE_NO13920202022 优化EXPORT和IMPORT
使用较大的BUFFER(比如10MB , 10,240,000)可以提高
EXPORT和IMPORT的速度; ORACLE将尽可能地获取你所指定的内
存大小,即使在内存
不满足,也不会报错.这个值至少要和表中最大的列相当,否则
列值会被截断;
