oracle数据库（五）_性能优化

1 索引

1.1索引的使用情况

• 唯一索引（b树索引）：primary key 自动生成点的索引，用于频繁的条件过滤字段
• 重复索引：用于有多种相同数据的字段，如部门编号
• 组合索引：多列一起为过滤条件的字段使用，较频繁的放在前面
• 位图索引：1与0表示的位图索引，用于少种相同数据的字段，如性别
• 函数索引：用于函数为筛选条件的索引，算数表达式、PL/SQL函数、程序包函数、SQL函数、用户自定义函数
• 其他：反向键索引，键压缩索引，索引组织表(IOT)，分区索引

1.2 索引的优缺点

• 优点：
  唯一所以确保数据唯一性
  提升数据检索速度
  提升表之间的连接速度
  提升排序速度，因为索引是已排好序的
  提升范围查询速度

• 缺点：
  费时
  维护成本高
  占用空间

1.3 索引的失效情况

• 条件的数据类型与索引类型不一致时（包含隐式转换查询）时
• 带%的模糊搜索
• 左则使用表达式
• 使用函数，但索引不是函数索引
• 单独使用复合索引的非第一位置
• isnull，not null，not in ，not exist，!=，<>

1.4 使用索引

CREATE UNIUQE | BITMAP INDEX <schema>.<index_name>
ON <schema>.<table_name>
(<column_name> | <expression> ASC | DESC,
<column_name> | <expression> ASC | DESC,...)
TABLESPACE <tablespace_name>
STORAGE <storage_settings>
LOGGING | NOLOGGING
COMPUTE STATISTICS
NOCOMPRESS | COMPRESS<nn>
NOSORT | REVERSE
PARTITION | GLOBAL PARTITION<partition_setting>

相关说明
1） UNIQUE | BITMAP：指定 UNIQUE为唯一值索引， BITMAP为位图索引，省略为 B-Tree索引。
2） <column_name> | ASC | DESC：可以对多列进行联合索引，当为
expression 时即―基于函数的索引
3） TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时效率更高)
4） STORAGE：可进一步设置表空间的存储参数
5） LOGGING | NOLOGGING：是否对索引产生重做日志(对大表尽量使用 NOLOGGING
来减少占用空间并提高效率)
6） COMPUTESTATISTICS：创建新索引时收集统计信息
7） NOCOMPRESS | COMPRESS：是否使用―键压缩‖(使用键压缩可以删除一个键列
中出现的重复值)
8） NOSORT | REVERSE： NOSORT 表示与表中相同的顺序创建索引， REVERSE表示相
反顺序存储索引值
9） PARTITION | NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区

2 sql优化

• 少用子查询
• 不用*
• 多用别名
• 及时提交事务释放资源
• exists代替in
• 驱动表的选择
• 最大过滤条件方最后

3 分区

2.1 分区优缺点

• 优点
  性能更高，只关心自己的分区
  高可用，故障不影响其他分区
  维护方便，修复循环分区即可
  均衡i/o，磁盘均衡映射
• 缺点
  不能再没有创建分区的表增加分区
  需要维护

2.2 范围分区：RANGE

DROP TABLE ptemp;

CREATE TABLE ptemp(
        empno NUMBER(8) PRIMARY KEY
       ,ename VARCHAR2(16)
       ,job VARCHAR2(16)
       ,mgr NUMBER(8)
       ,hiredate DATE
       ,sal NUMBER(8,2) NOT NULL
       ,comm NUMBER(8,2)
       ,deptno NUMBER(8) REFERENCES dept(deptno)
       -- ,CONSTRAINT fk_dept_no FOREIGN KEY(deptno) REFERENCES dept(deptno)
)
PARTITION BY RANGE (hiredate)
(
       PARTITION PT_19810101 VALUES LESS THAN (TO_DATE('1981-01-01','YYYY-MM-DD'))
      ,PARTITION PT_19820101 VALUES LESS THAN (TO_DATE('1982-01-01','YYYY-MM-DD'))
      ,PARTITION PT_19830101 VALUES LESS THAN (TO_DATE('1983-01-01','YYYY-MM-DD'))
      ,PARTITION PT_19840101 VALUES LESS THAN (TO_DATE('1984-01-01','YYYY-MM-DD'))
      ,PARTITION PT_19850101 VALUES LESS THAN (TO_DATE('1985-01-01','YYYY-MM-DD'))
      ,PARTITION PT_19860101 VALUES LESS THAN (TO_DATE('1986-01-01','YYYY-MM-DD'))
      ,PARTITION PT_19870101 VALUES LESS THAN (TO_DATE('1987-01-01','YYYY-MM-DD'))
      ,PARTITION PT_MX VALUES LESS THAN (MAXVALUE)
);

INSERT INTO ptemp SELECT * FROM emp;
COMMIT;

2.3 列表分区：LIST

CREATE TABLE MYEMP2
(
    EMPNO  NUMBER(4)  PRIMARY KEY,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL  NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(7,2),
    CONSTRAINT EMP1_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)
)
PARTITION BY LIST (DEPTNO)
(
    PARTITION MYEMP_DEPTNO_10  VALUES (10),
    PARTITION MYEMP_DEPTNO_20  VALUES (20),
    PARTITION MYEMP_DEPTNO_30  VALUES (30), 
    PARTITION MYEMP_DEPTNO_40  VALUES (40) 
)

2.4 散列（哈希）分区：HASH

2.5 组合分区

CREATE TABLE MYEMP3
(
    EMPNO  NUMBER(4)  PRIMARY KEY,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL  NUMBER(7,2),
    COMM NUMBER(7,2), 
    DEPTNO NUMBER(7,2) ,
    CONSTRAINT EMP3_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)
)
PARTITION BY RANGE(HIREDATE) SUBPARTITION BY LIST (DEPTNO)
(
   PARTITION P1 VALUES LESS THAN(TO_DATE('1981-01-01','YYYY-MM-DD'))
          (
              SUBPARTITION P1A VALUES (10) ,
              SUBPARTITION P1B VALUES (20),
              SUBPARTITION P1C VALUES (30),
              SUBPARTITION P1D VALUES (40)
          ),
   PARTITION P2 VALUES LESS THAN (TO_DATE('1982-01-01','YYYY-MM-DD'))
          (
              SUBPARTITION P2A VALUES (10) ,
              SUBPARTITION P2B VALUES (20),
              SUBPARTITION P2C VALUES (30),
              SUBPARTITION P2D VALUES (40)
          ),
   PARTITION P3 VALUES LESS THAN (TO_DATE('1983-01-01','YYYY-MM-DD'))
          (
              SUBPARTITION P3A VALUES (10) ,
              SUBPARTITION P3B VALUES (20),
              SUBPARTITION P3C VALUES (30),
              SUBPARTITION P3D VALUES (40)
          ),
   PARTITION P4 VALUES LESS THAN (TO_DATE('1988-01-01','YYYY-MM-DD'))
          (
              SUBPARTITION P4A VALUES (10) ,
              SUBPARTITION P4B VALUES (20),
              SUBPARTITION P4C VALUES (30),
              SUBPARTITION P4D VALUES (40)
          )
)

2.6 分区维护

-- 查询分区的内容
select * from LDAY PARTITION(PART3);


-- 所有分区信息 
select * from DBA_PART_TABLES where owner=upper('test') AND table_name=upper('lday');


-- 表的分区信息
select * from user_tab_partitions where table_name = 'LDAY'


-- 添加分区
ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));
ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');


-- 删除分区
ALTER TABLE SALES DROP PARTITION P3;
ALTER TABLE SALES DROP SUBPARTITION P4SUB1;

-- 截断分区
ALTER TABLE SALES TRUNCATE PARTITION P2;
ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2; 


-- 合并分区
ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;


-- 拆分分区
ALTER TABLE SALES SPLIT PARTITION P20 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P2,PARTITION P22);


-- 接合分区(将散列分区中的数据接合到其它分区中)
ALTER TABLE SALES COALESCA PARTITION;


-- 重命名表分区
ALTER TABLE SALES RENAME PARTITION P21 TO P2;