oracle优化小知识

「这是我参与11月更文挑战的第1天，活动详情查看：2021最后一次更文挑战」

1.创建索引

CREATE [UNIQUE] INDEX index_name ON table_name(column_name[,column_name…])

1. UNIQUE:指定索引列上的值必须是唯一的。称为唯一索引。

2. index_name：指定索引名。

3. tabl_name：指定要为哪个表创建索引。

4. column_name：指定要对哪个列创建索引。我们也可以对多列创建索引;这种索引称为组合索引。

案例4：为EMP表的ENAME列创建创建唯一索引，为EMP表的工资列创建普通索引，把JOB列先变为小写再创建索引。
CREATE INDEX IDX_JOB_LOWER ON EMP(LOWER(JOB)); 

2.数据执行顺序

oracle 过滤条件是从右到左的，所以右边的数据尽量是数据量比较小的一方

3.执行计划查看

一般按缩进长度来判断，缩进最大的最先执行，如果有2行缩进一样，那么就先执行上面的。

表的访问方式
TABLE ACCESS FULL（全表扫描）
TABLE ACCESS BY INDEX ROWID（通过 ROWID 的表存取）
TABLE ACCESS BY INDEX SCAN（索引扫描）

索引扫描
索引范围扫描(INDEX RANGE SCAN)
引唯一性扫描(INDEX UNIQUE SCAN)
索引全扫描（INDEX FULL SCAN）
索引快速扫描(index fast full scan)
索引跳跃式扫描（INDEX SKIP SCAN）

4.优化小知识

4.1.like

通配符%在前面的时候（%a）不会走索引的。

4.2.is null 或 is not null

当过滤条件为where a is null 或者 where is not null是不走索引的 

4.2.1 is null处理

常见的操作例如:
select * from a
    left join b on a.id=b.id
    where b.id is null;
    
这时候用函数nvl来处理
where 1=nvl(b.id,1) 

4.2.2 is not null处理

常见的操作例如:
select * from a
    left join b on a.id=b.id
    where b.id is not null;
    
用 exists 来处理 
  select * from a where exists (select 1 from b where b.id=a.id)

用 nvl 大小处理
select * from a
    left join b on a.id=b.id
    where 1=<nvl(b.score,0);
    
用 length 长度处理
select * from a
    left join b on a.id=b.id
    where 2=<length(nvl(b.score,0));

4.3.通过使用>=、<=等，避免使用<>命令

ORACLE遇到NOT <>,他就会停止使用索引转而执行全表扫描.

4.4.用union all 代替 union all

4.5.当使用的是组合索引的时候，当第一个索引被引用才会生效

4.6.尽量不要再索引列上使用函数和计算

select * from a where a.wages * 12 > 120000
用下面的
select * from a where a.wages > 120000/12

4.7减少对表的查询

比如判断多个字段和另一个表相同的时候
select * from a where a.1=(select b.1 from b where a.id=bi.id) and 
                      a.2=(select b.2 from b where a.id=bi.id)
调整为                      
select * from a where (a.1,a.2)=(select b.1,b.2 from b where a.id=bi.id)

5.查询数据库链接数

select count(*) from v$process --当前的连接数
select value from v$parameter where name = 'processes' --数据库允许的最大连接数

6.锁表查看

查询锁记录：
SELECT SESS.SID,  SESS.SERIAL#,  LO.ORACLE_USERNAME,  LO.OS_USER_NAME,  AO.OBJECT_NAME 被锁对象名, LO.LOCKED_MODE 锁模式, sess.LOGON_TIME 登录数据库时间,
'ALTER SYSTEM KILL SESSION ''' || SESS.SID || ','||SESS.SERIAL#||'''' FREESQL
FROM V$LOCKED_OBJECT LO,  DBA_OBJECTS AO,  V$SESSION SESS 
WHERE AO.OBJECT_ID = LO.OBJECT_ID AND LO.SESSION_ID = SESS.SID ORDER BY sid, sess.serial#;

精简：
select b.username,b.sid,b.serial#,logon_time 
from v$locked_object a,v$session b 
where a.session_id = b.sid order by b.logon_time; 

干掉锁：
alter system kill session 'sid,serial#';