Oracle局部索引

-- 创建表空间
create tablespace TBS01 datafile '/u01/app/oracle/oradata/EMREP/tbs01.dbf' size 50M autoextend on;
create tablespace TBS02 datafile '/u01/app/oracle/oradata/EMREP/tbs02.dbf' size 50M autoextend on;
create tablespace TBS03 datafile '/u01/app/oracle/oradata/EMREP/tbs03.dbf' size 50M autoextend on;
create tablespace TBS04 datafile '/u01/app/oracle/oradata/EMREP/tbs04.dbf' size 50M autoextend on;

-- 授权
ALTER USER HR
 QUOTA UNLIMITED ON TBS01
 QUOTA UNLIMITED ON TBS02
 QUOTA UNLIMITED ON TBS03
 QUOTA UNLIMITED ON TBS04;

-- 创建分区表
CREATE TABLE "HR"."P_EMPLOYEES"
(
 "EMPLOYEE_ID" NUMBER(6, 0),
 "FIRST_NAME" VARCHAR2(20),
 "LAST_NAME" VARCHAR2(25),
 "EMAIL" VARCHAR2(25),
 "PHONE_NUMBER" VARCHAR2(20),
 "HIRE_DATE" DATE,
 "JOB_ID" VARCHAR2(10),
 "SALARY" NUMBER(8, 2),
 "COMMISSION_PCT" NUMBER(2, 2),
 "MANAGER_ID" NUMBER(6, 0),
 "DEPARTMENT_ID" NUMBER(4, 0)
)
 partition by range (employee_id)
(
 partition p1 values less than (150) indexing off tablespace tbs01,
 partition p2 values less than (200) indexing on tablespace tbs02,
 partition p3 values less than (250) indexing on tablespace tbs03,
 partition p_other values less than (maxvalue) indexing off tablespace tbs04
);

-- 创建分区索引
create index emp_pidx on p_employees (employee_id) global partition by range (employee_id)
 (partition p1 values less than (150) tablespace TBS01,
 partition p2 values less than (200) tablespace TBS02,
 partition p3 values less than (250) tablespace TBS03,
 partition p_other values less than (maxvalue) tablespace TBS04);

-- 改为局部索引
alter index emp_pidx indexing partial;