oracle优化技巧

男娘i 2021-06-26 16:06 436阅读 0赞

## 一、编码习惯以及技巧 ##

**1、`SELECT`子句中避免使用 " \* "：**

ORACLE在解析的过程中, 会将"\*" 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。

**2、sql语句用大写的：**

因为oracle总是先解析`sql`语句，把小写的字母转换成大写的再执行。

**3、`WHERE`子句中的连接顺序：**

ORACLE采用自下而上的顺序解析`WHERE`子句,根据这个原理,表之间的连接必须写在其他`WHERE`条件之前, 那些可以过滤掉最大数量记录的条件必须写在`WHERE`子句的末尾。

**4、尽量多使用`COMMIT`：**

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

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

b. 被程序语句获得的锁

c. `redo log buffer` 中的空间

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

**5、用`TRUNCATE`替代`DELETE`：**

当删除表中的记录时,在通常情况下, 回滚段(`rollback segments` ) 用来存放可以被恢复的信息. 如果你没有`COMMIT`事务,`ORACLE`会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用`TRUNCATE`时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. (译者按: `TRUNCATE`只在删除全表适用,`TRUNCATE`是`DDL`不是``)；

**6、用`EXISTS`替代`IN`、用`NOT EXISTS`替代`NOT IN`：**

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用`EXISTS`(或`NOT EXISTS`)通常将提高查询的效率. 在子查询中,`NOT IN`子句将执行一个内部的排序和合并. 无论在哪种情况下,`NOT IN`都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用`NOT IN` ,我们可以把它改写成外连接(`Outer Joins`)或`NOT EXISTS`.

例子：

（高效）

SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT 'X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = 'MELB')

(低效)

SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE

**7、用`EXISTS`替换`DISTINCT`：**

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在`SELECT`子句中使用`DISTINCT`. 一般可以考虑用`EXIST`替换, `EXISTS`使查询更为迅速,因为`RDBMS`核心模块将在子查询的条件一旦满足后,立刻返回结果. 例子：

(低效):

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

**8、避免在索引列使用`not`，`is null` ,`is not null` ，以及使用计算。**

**9、用`>=` 替代`>`**

**10、用`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`, 那就需要返回记录最少的索引列写在最前面.

**11、用`in` 替换`or` 。**

**12、总是使用索引的第一个列：**

如果索引是建立在多个列上, 只有在它的第一个列(`leading column`)被`where`子句引用时,优化器才会选择使用该索引. 这也是一条简单而重要的规则，当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引

**13、避免改变索引列的类型.:**

当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.

假设 `EMPNO`是一个数值类型的索引列.

SELECT … FROM EMP WHERE EMPNO = '123'

实际上,经过`ORACLE`类型转换, 语句转化为:

SELECT … FROM EMP WHERE EMPNO = TO_NUMBER('123')

幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.

现在,假设`EMP_TYPE`是一个字符类型的索引列.

SELECT … FROM EMP WHERE EMP_TYPE = 123

这个语句被ORACLE转换为:

SELECT … FROM EMP WHERE TO_NUMBER(EMP_TYPE)=123

因为内部发生的类型转换, 这个索引将不会被用到! 为了避免ORACLE对你的SQL进行隐式的类型转换, 最好把类型转换用显式表现出来. 注意当字符和数值比较时, ORACLE会优先转换数值类型到字符类型

14、需要当心的`WHERE`子句:

某些`SELECT` 语句中的`WHERE`子句不使用索引. 这里有一些例子.

在下面的例子里,  
(1)‘`!=`’ 将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中.  
(2) ‘`||`'是字符连接函数. 就象其他函数那样, 停用了索引.  
(3) ‘`+`'是数学函数. 就象其他数学函数那样, 停用了索引.  
(4)相同的索引列不能互相比较,这将会启用全表扫描.

**15、创建索引，但该索引又允许为空，我们都指定 索引列 `is null` 会使索引失效，解决的方法是，创建索引时，增加一个常量。**

如：

create index IDX_DEPT_FSTANDARDCODE on T_ORG_DEPARTMENT (FSTANDARDCODE, 0);

## 二、常用SQL操作 ##

### 1.0 创建完整表SQL ###

CREATE TABLE banping
      (
         id         NUMBER(5) CONSTRAINT banping_id_pk PRIMARY KEY,
         last_name  VARCHAR2(10) CONSTRAINT banping_last_name_nn NOT NULL,
         first_name VARCHAR2(10) NOT NULL UNIQUE,
         userid     VARCHAR2(8) CONSTRAINT banping_userid_uk UNIQUE,
         start_date DATE DEFAULT SYSDATE,
         title      VARCHAR2(10),
         dept_id    NUMBER(7) CONSTRAINT banping_dept_id_fk REFERENCES dept(id),
         salary     NUMBER(11, 2),
         user_type  VARCHAR2(4) CONSTRAINT banping_user_type_ck CHECK (user_type IN(
         'IN',
         'OUT')),
         CONSTRAINT banping_uk_title UNIQUE (title, salary)
      )
    INITRANS 1
    MAXTRANS 255
    PCTFREE 20
    PCTUSED 50
    STORAGE( INITIAL 1024k
             NEXT 1024k
             PCTINCREASE 0
             MINEXTENTS 1
             MAXEXTENTS 5)
    TABLESPACE data;

**2、字段解释：Schema：表的所有者**

`Table`：表名

`Column`：字段名

`Datatype`：字段的数据类型

`Tablespace`：表所在的表空间

`Pctfree`：为了行长度增长而在每个块中保留的空间的量（以占整个空间减去块头部后所剩余空间的百分比形式表示），当剩余空间不足pctfree时，不再向该块中增加新行。

`Pctused`：在块剩余空间不足`pctfree`后，块已使用空间百分比必须小于`pctused`后，才能向该块中增加新行。

`INITRANS`：在块中预先分配的事务项数，缺省值为1

`MAXTRANS`：限定可以分配给每个块的最大事务项数，缺省值为255

`STORAGE`：标识决定如何将区分配给表的存储子句

`LOGGING`：指定表的创建将记录到重做日志文件中。它还指定所有针对该表的后续操作都将被记录下来。这是缺省设置。

`NOLOGGING`：指定表的创建将不被记录到重做日志文件中。

`CACHE`：指定即使在执行全表扫描时，为该表检索的块也将放置在缓冲区高速缓存的LRU列表最近使用的一端。

`NOCACHE`：指定在执行全表扫描时，为该表检索的块将放置在缓冲区高速缓存的LRU列表最近未使用的一端。

`STORAGE`子句：

`INITIAL`：初始区的大小

`NEXT`：下一个区的大小

`PCTINCREASE`：以后每个区空间增长的百分比

`MINEXTENTS`：段中初始区的数量

`MAXEXTENTS`：最大能扩展的区数

如果已为表空间定义了`MINIMUM EXTENT`，则表的区大小将向上舍入为`MINIMUM EXTENT`值的下一个较高的倍数。

**3、语言增加约束条件：`alter table dept add constraint dept_id_pk primary key(id);`**

### 1.1、创建两种临时表(会话级与事务级) ###

**1.会话级临时表**

会话级临时表是指临时表中的数据只在会话生命周期之中存在，当用户退出会话结束的时候，Oracle自动清除临时表中数据。

格式：

Create Global Temporary Table Table_Name 
    (
       Col1 Type1,
       Col2 Type2
       ...
    ) 
    On Commit Preserve Rows;

**2.事务级临时表**

事务级临时表是指临时表中的数据只在事务生命周期中存在。

Create Global Temporary Table Table_Name  
      (
        Col1 Type1,
        Col2 Type2
        ...
      ) 
      On Commit Delete Rows;

当一个事务结束（`commit or rollback`），`Oracle`自动清除临时表中数据。

### 1.3、索引 ###

**1、索引分为5种:唯一索引，组合索引，反向键索引，位图索引，基于函数的索引**

**2、一个基表不能建太多的索引；  
空值不能被索引  
只有唯一索引才真正提高速度,一般的索引只能提高30%左右。**

**3、索引提高查询速度，对插入，更新操作则会变慢。**

create unique index  index_name on t_dept(n_id);

### 1.4、创建序列 ###

CREATE SEQUENCE sequence_name
    
    [START WITH num]
    
    [INCREMENT BY increment]
    
    [MAXVALUE num|NOMAXVALUE]
    
    [MINVALUE num|NOMINVALUE]
    
    [CYCLE|NOCYCLE]
    
    [CACHE num|NOCACHE]

语法解析：

① `START WITH`：从某一个整数开始，升序默认值是1，降序默认值是-1。

② `INCREMENT BY`：增长数。如果是正数则升序生成，如果是负数则降序生成。升序默认值是1，降序默认值是-1。

③ `MAXVALUE`：指最大值。

④ `NOMAXVALUE`：这是最大值的默认选项，升序的最大值是：1027，降序默认值是-1。

⑤ `MINVALUE`：指最小值。

⑥ `NOMINVALUE`：这是默认值选项，升序默认值是1，降序默认值是-1026。

⑦ `CYCLE`：表示如果升序达到最大值后，从最小值重新开始;如果是降序序列，达到最小值后，从最大值重新开始。

⑧ `NOCYCLE`：表示不重新开始，序列升序达到最大值、降序达到最小值后就报错。默认`NOCYCLE`。

⑨ `CACHE`：使用`CACHE`选项时，该序列会根据序列规则预生成一组序列号。保留在内存中，当使用下一个序列号时，可以更快的响应。当内存中的序列号用完时，系统再生成一组新的序列号，并保存在缓存中，这样可以提高生成序列号的效率。`Oracle`默认会生产20个序列号。

⑩ `NOCACHE`：不预先在内存中生成序列号。

### 1.5、创建触发器 ###

![在这里插入图片描述][20181230135035774.png]  
**1、对`shcema`的触发器控制**

create or replace trigger tri_dm1
     before insert or update or delete on scott.emp
     begin
       if user <>'SCOTT' then
         raise_application_error(-20001,'You don''t have access to modify this table.');
       end if;
     end;
     /

**2.利用触发器进行表和备份表之间的同步复制。**

CREATE OR replace TRIGGER duplicate_emp
      AFTER UPDATE OR INSERT OR DELETE ON scott.emp
      FOR EACH ROW
    BEGIN
        IF inserting THEN
          INSERT INTO employee
          VALUES      (:new.empno,
                       :new.ename,
                       :new.job,
                       :new.mgr,
                       :new.hiredate,
                       :new.sal,
                       :new.comm,
                       :new.deptno);
        ELSIF deleting THEN
          DELETE FROM employee
          WHERE  empno = :old.empno;
        ELSE
          UPDATE employee
          SET    empno = :new.empno,
                 ename = :new.ename,
                 job = :new.job,
                 mgr = :new.mgr,
                 hiredate = :new.hiredate,
                 sal = :new.sal,
                 comm = :new.comm,
                 deptno = :new.deptno
          WHERE  empno = :old.empno;
        END IF;
    END;
    
    /

**3.建立触发器，对`scott.emp` 表进行`DML`操作时的时间、用户进行日志记录。**

CREATE OR replace TRIGGER dm1_log
      AFTER INSERT OR UPDATE OR DELETE ON scott.emp
    DECLARE
        oper emp_log.oper%TYPE;
    BEGIN
        IF inserting THEN
          oper := 'insert';
        ELSIF deleting THEN
          oper := 'delete';
        ELSE
          oper := 'update';
        END IF;
    
        INSERT INTO emp_log
        VALUES     (USER,
                    SYSDATE,
                    oper);
    END;

### 1.8、创建存储过程 ###

CREATE OR replace PROCEDURE pr_tets(name IN VARCHAR2,
                                        o_msg OUT VARCHAR2,
                                        o_rs SYS_REFCURSOR) AS
      BEGIN
    
      EXCEPTION
      WHEN other THEN
    
        ROLLBACK;
      END
    END

### 1.9、创建函数 ###

### 2.0、创建包与包体 ###

### 2.1、赋权限 ###

### 2.2、`insert into /update /delete /select` ###

## 三、常用场景需要用到的高级函数 ##

1、查询树状数据 (多选下拉接口)

2、统计每五分钟的数据量

3、增加线程提高查询效率

4、有插入又更新的数据导入

[20181230135035774.png]: /images/20210626/bba9c1c79f9c47ad8694c0b2c3fc3309.png