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

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