Using Oracle Data Storage Structures Efficiently

1.创建集群表

create cluster mycluster (deptno number(2)) size 1024;

]

 

2.为集群表创建索引

create index myc_idx on cluster mycluster;

 

3.创建普通表与集群表关联

create table dept(

 deptid number(2) primary key,

 dname varchar2(20),

 loc varchar2(30)

)

cluster mycluster(deptid);

 

create table emp(

empid number primary key,

ename varchar2(20),

sal number,

deptno number(2) references dept(deptid)

)

cluster mycluster(deptno)

 

修正

SQL > 4 修改第四行

SQL > c/number/number, 修改number为number,

 

4.创建partition 表

create table kk(

rk date,

data varchar2(20)

)

partition by range(rk)(

partition p1 values less than (to_date('2009-01-01','yyyy-mm-dd')) tablespace ts0,

partition p2 values less than ((to_date('2010-01-01','yyyy-mm-dd')) tablespace ts1

);

 

5.查看记录

select * from kk partition(p1);

 

6.涵盖所有记录

alter table kk add partition  p3 values less than (MAXVALUE) tablespace ts2;

 

7.创建Hash Partition 表

create table emp(

empno number,

ename varchar2(20)

)

partition by hash(empno)(

partition part1 tablespace ts1,

partition part2 tablespace ts2

);

 

8.创建List partition表

create table locations(

location_id number,

street_address varchar2(40),

city varchar2(20),

state_province(30),

country_id number)

) tablespace users

partition by LIST (state_province)

(partition region_east values('MA','NY','CT','ME','MD'),

partition  region_west values('CA','ZA'),

partition region_south values('TX','KY'),

partition region_default values(default)

 

9.创建组合分区表

create table composite1(

range_key date,

hash_key int,

data varchar2(20)

)

partition by range(range_key)

subpartition by hash(hash_key) subpartitions 2

(

  partition part1 values less than((to_date('2008-01-01','yyyy-mm-dd'))

  (

    subpartition h1,

    subpartition h2

  ),

 partition part2 values less than(to_date('2009-01-01','yyyy-mm-dd'))

 (

   subpartition h22,

   subpartition h21

 )

)

 

10.创建分区表索引

 1.local index

create index local_index on table1(a,b) local;

 

2.global index

 create index global_index on table1(b) global

partition by range(b)

(

 partition idx1 values less than (1000),

 partition idx2 values less than (MAXVALUE)

);

 

11.移动表

 alter table hr.employees move tablespace users;

 

12.压缩索引

 create index emp_index on hr.employee (last_name,first_name) compress.

 

alter index emp_index rebuild compress;

 

13. IOT表

create table T(id int primary key,

 y varchar2(2000),

 z varchar2(2000)

) organization index

pctthreshold 20/including y

overflow tablespace users;