SQL01

Oracle sql开发注意事项

一、 ORACLE优化器

早起的ORACLE 版本使用基于规则的优化器（RBO,Rule-Based Optimizer），在确定RBO原理经常导致错误的判断后，ORACLE摒弃了它，转而使用基于成本的优化器(CBO, Cost-Based Optimizer)

二、访问Table的方式

ORACLE 采用两种访问表中记录的方式:

a. 全表扫描

全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描.

b. 通过ROWID访问表

你可以采用基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位置信息..ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.

三、共享SQL语句

为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享. 因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它

和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的

执行路径. ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用.

可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询.

数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了.

当你向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句.

这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须

完全相同(包括空格,换行等).

共享的语句必须满足三个条件:

A. 字符级的比较:

当前被执行的语句和共享池中的语句必须完全相同.

例如:

SELECT * FROM EMP;

和下列每一个都不同

SELECT * from EMP;

Select * From Emp;

SELECT * FROM EMP;

B. 两个语句所指的对象必须完全相同:

例如:

用户对象名如何访问

Jack sal_limit private synonym

Work_city public synonym

Plant_detail public synonym

Jill sal_limit private synonym

Work_city public synonym

Plant_detail table owner

考虑一下下列SQL语句能否在这两个用户之间共享.

SQL	能否共享	原因
select max(sal_cap) from sal_limit;	不能	每个用户都有一个private synonym - sal_limit , 它们是不同的对象
select count(*0 from work_city where sdesc like 'NEW%';	能	两个用户访问相同的对象public synonym - work_city
select a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id	不能	用户jack 通过private synonym访问plant_detail 而jill 是表的所有者,对象不同.

C. 两个SQL语句中必须使用相同的名字的绑定变量(bind variables)

例如：第一组的两个SQL语句是相同的(可以共享),而第二组中的两个语句是不同的(即使在运行时,赋于不同的绑定变量相同的值)

select pin , name from people where pin = :blk1.pin;

select pin , name from people where pin = :blk1.ot_ind;

select pin , name from people where pin = :blk1.ov_ind;

四、 SQL开发注意事项

SELECT子句中避免出现不需要的列

AA表中有C1，C2，C3三个列

Select * from aa 比Select c1 from aa 消耗的资源更大。

数字做主键比用长字符做主键性能更高。

以my_user表为例，表中两列(id char(36),seq number(10))都建有唯一索引，即：访问方式等同于主键。下面测试：通过字符索引和数字索引获取一条记录的执行情况。

显然，用数字做主键比用长字符做主键性能更高。

表的列位置越靠前，性能越好

如果表A中依次有c1,c2,c3三列，在同等条件下，查询c1的性能>c2>c3。

减少访问数据库的次数

当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量.

例如,

以下有三种方法可以检索出雇员号等于0342或0291的职员.

方法1 (最低效)

SELECT EMP_NAME , SALARY , GRADE

FROM EMP

WHERE EMP_NO = 342;

SELECT EMP_NAME , SALARY , GRADE

FROM EMP

WHERE EMP_NO = 291;

方法2 (次低效)

DECLARE

CURSOR C1 (E_NO NUMBER) IS

SELECT EMP_NAME,SALARY,GRADE

FROM EMP

WHERE EMP_NO = E_NO;

BEGIN

OPEN C1(342);

FETCH C1 INTO …,..,.. ;

OPEN C1(291);

FETCH C1 INTO …,..,.. ;

CLOSE C1;

END;

方法3 (高效)

SELECT A.EMP_NAME , A.SALARY , A.GRADE,

B.EMP_NAME , B.SALARY , B.GRADE

FROM EMP A,EMP B

WHERE A.EMP_NO = 342

AND B.EMP_NO = 291;

使用DECODE函数来减少处理时间

使用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%’;

类似的,DECODE函数也可以运用于GROUP BY 和ORDER BY子句中.

用TRUNCATE替代DELETE

当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)

而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.

(译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)

尽量多使用COMMIT

只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:

COMMIT所释放的资源:

a. 回滚段上用于恢复数据的信息.

b. 被程序语句获得的锁

c. redo log buffer 中的空间

d. ORACLE为管理上述3种资源中的内部花费

(译者按: 在使用COMMIT时必须要注意到事务的完整性,现实中效率和事务完整性往往是鱼和熊掌不可得兼)

关于计算记录条数

count(*)

count(1)

count(index_col) ,

前两者效果一样，第三者优于前两者。

用Where子句替换HAVING子句

避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.

例如:

低效:

SELECT REGION，AVG(LOG_SIZE)

FROM LOCATION

GROUP BY REGION

HAVING REGION REGION != ‘SYDNEY’

AND REGION != ‘PERTH’

高效

SELECT REGION，AVG(LOG_SIZE)

FROM LOCATION

WHERE REGION REGION != ‘SYDNEY’

AND REGION != ‘PERTH’

GROUP BY REGION

HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等. 除此而外,一般的条件应该写在WHERE子句中

减少对表的查询

在含有子查询的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)

Update 多个Column 例子:

低效:

UPDATE EMP

SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),

SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)

WHERE EMP_DEPT = 0020;

高效:

UPDATE EMP

SET (EMP_CAT, SAL_RANGE)

= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)

FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020;

使用表的别名(Alias)

当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.

(译者注: Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属)

用EXISTS替代IN

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.

低效:

SELECT *

FROM EMP (基础表)

WHERE EMPNO > 0

AND DEPTNO IN (SELECT DEPTNO

FROM DEPT

WHERE LOC = ‘MELB’)

高效:

SELECT *

FROM EMP (基础表)

WHERE EMPNO > 0

AND EXISTS (SELECT ‘X’

FROM DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO

AND LOC = ‘MELB’)

用NOT EXISTS替代NOT IN

在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.

例如:

SELECT …

FROM EMP

WHERE DEPT_NO NOT IN (SELECT DEPT_NO

FROM DEPT

WHERE DEPT_CAT=’A’);

为了提高效率.改写为:

SELECT ….

FROM EMP E

WHERE NOT EXISTS (SELECT ‘X’

FROM DEPT D

WHERE D.DEPT_NO = E.DEPT_NO

AND DEPT_CAT = ‘A’);

注意not exists和not in 对于子查询中null值的不同处理

Not exists 和not in 除了性能上有区别以外，对null 的处理也是不一样的。

A,B两个的数据如图：

A: B:

B表中有NULL值，接下来分别运行如下两条语句

1 select * from a where a.c1 not in (select c1 from b );

2 select * from a where not exists (select 1 from b where a.c1=b.c1);

我们期望获取的结果是: 4,5 ，而实际的情况是：

Not in 返回了空集，

not exist返回了4,5

因为子查询返回了null，导致not in返回false，而not exists只要有记录匹配就返回true，直到所有记录都不匹配才返回false.

用EXISTS替换DISTINCT

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换

例如:

低效:

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);

EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.

用>=替代>

如果DEPTNO上有一个索引,

高效:

SELECT *

FROM EMP

WHERE DEPTNO >=4

低效:

SELECT *

FROM EMP

WHERE DEPTNO >3

两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.

用UNION替换OR (适用于索引列)

通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低.

在下面的例子中, LOC_ID 和REGION上都建有索引.

高效:

SELECT LOC_ID , LOC_DESC , REGION

FROM LOCATION

WHERE LOC_ID = 10

UNION

SELECT LOC_ID , LOC_DESC , REGION

FROM LOCATION

WHERE REGION = “MELBOURNE”

低效:

SELECT LOC_ID , LOC_DESC , REGION

FROM LOCATION

WHERE LOC_ID = 10 OR REGION = “MELBOURNE”

如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.

注意:

WHERE KEY1 = 10 (返回最少记录)

OR KEY2 = 20 (返回最多记录)

ORACLE 内部将以上转换为

WHERE KEY1 = 10 AND

((NOT KEY1 = 10) AND KEY2 = 20)

用IN来替换OR

下面的查询可以被更有效率的语句替换:

低效:

SELECT….

FROM LOCATION

WHERE LOC_ID = 10

OR LOC_ID = 20

OR LOC_ID = 30

高效

SELECT…

FROM LOCATION

WHERE LOC_IN IN (10,20,30);

避免在索引列上使用IS NULL和IS NOT NULL

避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引．对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录.　如果至少有一个列不为空，则记录存在于索引中．

举例:

如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入). 然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000条具有相同键值的记录,当然它们都是空!

因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.

举例:

低效: (索引失效)

SELECT …

FROM DEPARTMENT

WHERE DEPT_CODE IS NOT NULL;

高效: (索引有效)

SELECT …

FROM DEPARTMENT

WHERE DEPT_CODE >=0;

用UNION-ALL 替换UNION ( 如果有可能的话)

当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序.

如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高.

举例:

低效：

　　　　SELECT ACCT_NUM, BALANCE_AMT

FROM DEBIT_TRANSACTIONS

WHERE TRAN_DATE = ’31-DEC-95’

UNION

SELECT ACCT_NUM, BALANCE_AMT

FROM DEBIT_TRANSACTIONS

WHERE TRAN_DATE = ’31-DEC-95’

高效:

SELECT ACCT_NUM, BALANCE_AMT

FROM DEBIT_TRANSACTIONS

WHERE TRAN_DATE = ’31-DEC-95’

UNION ALL

SELECT ACCT_NUM, BALANCE_AMT

FROM DEBIT_TRANSACTIONS

WHERE TRAN_DATE = ’31-DEC-95’

使用提示(Hints)

1) 并行提示(parallel)

所谓并行，是Oracle的多线程处理功能，默认是不启用的。适用用于配置了多核处理器服务器。

功能：提示ORACLE启用多线程执行任务，缩短执行时间。

语法：parallel(table_name,thread_number)

例如： select /*+ parallel(my_know,4)*/ * from my_know t where t.uploaduserid in (select userid FROM my_group where groupname in (select groupname from my_group where userid=v_id));

2) 索引提示(index)

功能：提示ORACLE使用指定的索引访问表

语法：index(table_name,index_name)

例如：select /*+ index(aa,idx_c1)*/ * from aa;

不使用索引用 no_index提示

3) 全表扫描提示(full)

功能：提示ORACLE使用全表扫描的方式访问表

语法：full(table_name)

例如：select /*+ full(aa)*/ * from aa;

用WHERE替代ORDER BY

ORDER BY 子句只在两种严格的条件下使用索引:

ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.

ORDER BY中所有的列必须定义为非空.

WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列.

例如:

表DEPT包含以下列:

DEPT_CODE PK NOT NULL

DEPT_DESC NOT NULL

DEPT_TYPE NULL

非唯一性的索引(DEPT_TYPE)

低效: (索引不被使用)

SELECT DEPT_CODE

FROM DEPT

ORDER BY DEPT_TYPE

EXPLAIN PLAN:

SORT ORDER BY

TABLE ACCESS FULL

高效: (使用索引)

SELECT DEPT_CODE

FROM DEPT

WHERE DEPT_TYPE > 0

EXPLAIN PLAN:

TABLE ACCESS BY ROWID ON EMP

INDEX RANGE SCAN ON DEPT_IDX

五、 关于索引

索引是表的一个概念部分,用来提高检索数据的效率. 实际上,ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.

除了那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率.

虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来

存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.

索引的类型

索引基本分为以下几种：B*Tree索引，反向索引，降序索引，位图索引，函数索引。

b*tree index：几乎所有的关系型数据库中都有b*tree类型索引，也是被最多使用的。其树结构与二叉树比较类似，根据rid快速定位所访问的行。

　　反向索引：反转了b*tree索引码中的字节，是索引条目分配更均匀，多用于并行服务器环境下，用于减少索引叶的竞争。

　　降序索引：8i中新出现的索引类型，针对逆向排序的查询。

　　位图索引：使用位图来管理与数据行的对应关系，多用于OLAP系统。

　　函数索引：这种索引中保存了数据列基于function返回的值，在select * from table where function（column）=value这种类型的语句中起作用。

索引失效的情况

1) ‍like "%_" 百分号在前.

2) 表未分析，ORACLE未获取该表的统计信息。

分析表的语法：analyze table table_name computer statistics;

3) 未引用复合索引的索引列。所谓“索引列”是指复合索引创建时书写在第一个位置的列。

例如：x,y,z 三列构成一个复合索引，只要where语句中引用了x列，则会使用索引（如：x,xy,xz,xyz），相应的，如果未引用x列，则不会使用索引（如：y,z,yz）。

4) 对索引列使用了函数、操作符或潜在的数据类型转换时，会导致不使用索引。

例如：使用了函数： lower(index_col)=’abc’

使用了操作符： Index_col || “ ” =’abc’

潜在的数据类型转换：Index_col=123（列index_col为字符型），oracle会将这个表达式转换成to_number(index_col)=123 导致索引列加上了to_number函数，是索引失效。

5) 对索引列进行运算.需要建立函数索引.

6) 查询返回记录大于10%，不会使用索引。

7) B-tree索引 is null不会使用索引,is not null会使用,位图索引 is null,is not null 都会使用索引

================================================================

六、用EXPLAIN PLAN 分析SQL语句

EXPLAIN PLAN 是一个很好的分析SQL语句的工具,它甚至可以在不执行SQL的情况下分析语句. 通过分析,我们就可以知道ORACLE是怎么样连接表,使用什么方式扫描表(索引扫描或全表扫描)以及使用到的索引名称.

你需要按照从里到外,从上到下的次序解读分析的结果. EXPLAIN PLAN分析的结果是用缩进的格式排列的, 最内部的操作将被最先解读, 如果两个操作处于同一层中,从上到下执行.

在PL/SQL developer 点file → new →explain plan window 可以直接打开执行计划的解析窗口。如图：

在开发过程中，可以使用explain plan分析所开发的SQL，对比各自的COST值，一般来说cost值越低，性能越好。

posted @ 2021-07-26 23:14 一码事阅读(61) 评论(0) 编辑收藏举报

刷新页面返回顶部

一码事

快乐码

SQL01

公告