Oracle学习之数据库管理（二）索引&约束&表分区

一、索引和约束
建立索引的目的是提高对表的查询速度，对表有关列的取值进行检查。但是，对表进行 insert,update,delete 处理时，
由于要表的存放位置记录到索引项中而会降低一些速度。
[注：一个基表不能建太多的索引，空值不能被索引，只有唯一索引才真正提高速度,一般的索引只能提高30%左右]

1、唯一索引
  CREATE UNIQUE INDEX IDX_TEST_A ON TEST_D(K_ID);
2、创建约束
  ALTER TABLE TEST_D ADD CONSTRAINT uniq_id UNIQUE(K_ID);--唯一约束
  ALTER TABLE TEST_D MODIFY K_ID NOT NULL;--非空
  ALTER TABLE TEST_D ADD CONSTRAINT PK_ID PRIMARY KEY(K_ID);--主键约束
  ALTER TABLE TEST_D ADD CONSTRAINT CHECK_ID CHECK(K_ID in(1,2,3,4));--check约束
  ALTER TABLE TEST_D DROP CONSTRAINT uniq_iD;
总结如下：
  1.主键约束和唯一键约束均会隐式创建同名的唯一索引，当主键约束或者唯一键约束失效时，隐式创建的唯一索引会被删除。
  2.主键约束要求列值非空，而唯一键约束和唯一索引不要求列值非空。
  3.相同字段序列不允许重复创建索引。
3、组合索引
  CREATE INDEX TEST_D_GROUP ON TEST_D(K_DATE,A,B);
总结如下：
  查询条件中必须有先导列，即：K_DATE
4、基于函数的索引
  CREATE INDEX TEST_D_FUNC ON TEST_D(TO_CHAR(K_DATE,'YYYYMM'));
使用基于函数的索引，需要几个条件：
  1.用户需要有 CREATE INDEX 或者 CREATE ANY INDEX 权限
   GRANT CREATE ANY INDEX TO scott;
  2.用户需要有query rewrite或者global query rewirte权限
   GRANT GLOBAL QUERY REWRITE TO scott;
  3.设置系统参数 query_rewrite_enabled=TRUE和query_rewrite_integrity=ENFORCED
   ALTER SYSTEM SET query_rewrite_enabled=TRUE;
  4.设置系统参数 ：COMPATIBLE=8.1.0.0.0 或者更高
  5.创建了BFI后，需要对表进行分析
5、删除索引
  DROP INDEX TEST_D_FUNC ON TEST_D;

二、分区
--登录sys创建表空间
  CREATE TABLESPACE dw_1
  DATAFILE 'D:\ProgramFiles\Oracle\oradata\orcl\dw_1.ora' SIZE 50M;
  CREATE TABLESPACE dw_2
  DATAFILE 'D:\ProgramFiles\Oracle\oradata\orcl\dw_2.ora' SIZE 50M;
1、范围分区
  CREATE TABLE TEST_A(
    K_ID number primary key,
    K_DATE date,
    K_A varchar2(20),
    K_B varchar2(20),
    K_C varchar2(20),
    K_D number)
  PARTITION BY RANGE(K_DATE)
    (
    PARTITION dta_1 VALUES LESS THAN (to_date('2014-1-1','yyyy-mm-dd')) TABLESPACE dw_1,
    PARTITION dta_2 VALUES LESS THAN (to_date('2015-1-1','yyyy-mm-dd')) TABLESPACE dw_1,
    PARTITION dta_3 VALUES LESS THAN (MAXVALUE) TABLESPACE dw_1
    );
查看某分区下数据
  SELECT * FROM TEST_A PARTITION(DTA_1);
2、列表分区：
该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。
  CREATE TABLE TEST_B(
      K_ID number primary key,
      K_DATE date,
      K_A varchar2(20),
      K_B varchar2(20),
      K_C varchar2(20),
      K_D number)
    PARTITION BY LIST(K_C)
      (
      PARTITION C_1 VALUES ('C1') TABLESPACE dw_1,
      PARTITION C_2 VALUES ('C2') TABLESPACE dw_1,
      PARTITION C_3 VALUES ('C3') TABLESPACE dw_1
      );
3、散列分区
  CREATE TABLE TEST_C(
      K_ID number primary key,
      K_DATE date,
      K_A varchar2(20),
      K_B varchar2(20),
      K_C varchar2(20),
      K_D number)
  PARTITION BY HASH(K_DATE)
  (
  PARTITION dta_1 TABLESPACE dw_1,
  PARTITION dta_2 TABLESPACE dw_2
  );
  [注：系统按dta_date将记录散列插入到两个分区中，这里也就是两个不同的表空间中]
4、复合分区
[注：复合分区是先使用范围分区，然后在每个分区同再使用散列分区的一种分区方法]
  CREATE TABLE TEST_D(
    K_ID NUMBER PRIMARY KEY,
    K_DATE DATE,
    K_A VARCHAR2(20),
    K_B VARCHAR2(20),
    K_C VARCHAR2(20),
    K_D NUMBER)
  PARTITION BY RANGE (K_DATE) SUBPARTITION BY HASH(K_ID)
  SUBPARTITIONS 2 STORE IN(dw_1,dw_2)
  (
  PARTITION dta_1 VALUES LESS THAN(to_date('2013-01-01','yyyy-mm-dd')) TABLESPACE dw_1,
  PARTITION dta_2 VALUES LESS THAN(to_date('2014-01-01','yyyy-mm-dd')) TABLESPACE dw_2,
  PARTITION dta_3 VALUES LESS THAN(MAXVALUE) TABLESPACE dw_1
  );

5、分区维护：(只对范围分区)
/*增加一个分区：分区范围只能往上增，不能增加一个少于原有的分区*/
  ALTER TABLE tablename ADD PARTITION new_partitionname VALUES LESS THAN(MAXVALUE);
/*合并/拆分分区:(合并后的分区必须指下最后一个大value的分区)*/
ALTER TABLE tablename MERGE PARTITIONS partitionname1,partitionname2 INTO PARTITION partitionname2;
ALTER TABLE tablename SPLIT PARTITION partitionname1 AT (xx) INTO (
PARTITION newpartition1 ,PARTITION newpartition2) ;
注意:xx为分割点
/*删除一个分区*/
ALTER TABLE niegc_part DROP PARTITION partitionname;
/*将分区改名*/
ALTER TABLE table_name RENAME PARTITION partition_name TO partition_name
/*将分区改表空间*/
ALTER TABLE table_name MOVE partition_name
TABLESPACE tablespace_name NOLOGGING
/*查询特定分区*/
SELECT COUNT(*) FROM table_name PARTITION (partition_name);
/*添加数据*/
INSERT INTO table_name SELECT * FROM table_name PARTITION (partition_name)
/*技巧：删除表中一个字段*/
ALTER TABLE table_name SET UNUSED COLUMN column_name;
/*加一个字段*/
ALTER TABLE table_name ADD column_name NUMBER(1);