14、 Using Oracle Data Storage Structure Efficiently ---有效使用Oracle数据存储结构
1、创建cluster
SQL> create cluster mycluster (deptno number(2)) size 1024; Index created. SQL> create table dept
SQL> create table emp SQL> create cluster hc(hk number) hashkeys 1000 size 8192; Cluster created.
|
2、When to Use Cluster
3、Partitioning Methods
Range
Hash
List
Composite
4、Range Partitioning
SQL> create table range1 2 ( rk date, select segment_name,partition_name,segment_type from user_segments select * from range1 partition(p1); SQL> alter table range1 |
5、Hash Partitioning
SQL> create table emp1 2 ( empno int, 3 ename varchar2(20) 4 ) 5 partition by hash(empno) 6 ( 7 partition part1 tablespace ts1, 8 partition part2 tablespace ts2 9 ) 10 / Table created. SQL> insert into emp1 select empno,ename from scott.emp; 1* select * from emp1 partition(part1) |
5、List Partitioning
SQL> SQL> create table list1 2 (stateid varchar2(2), 3 data varchar2(100) 4 ) 5 partition by list(stateid) 6 ( 7 partition p1 values('TX','MA','NY') tablespace ts0, 8 partition p2 values('CA','PA') tablespace ts1, 9 partition p3 values(default) tablespace ts2 10 ) 11 / Table created. |
6、Composite Partitioning
SQL> 1 create table composite1 2 (range_key date, 3 hash_key int, 4 data varchar2(20) 5 ) 6 partition by range(range_key) 7 subpartition by hash(hash_key) subpartitions 2 8 ( 9 partition part1 values less than(to_date('01/01/2008','dd/mm/yyyy')) 10 ( subpartition h1, 11 subpartition h2 12 ), 13 partition part2 values less than(to_date('01/01/2009','dd/mm/yyyy')) 14 ( subpartition h12, 15 subpartition h22 16 ) 17* ) SQL> / Table created. |
7、Partitioned Indexes
SQL> SQL> create index local_idx1 on range1(rk,data) local; ---local partition index Index created. SQL> select object_name,object_type from user_objects; OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------- LOCAL_IDX1 INDEX PARTITION LOCAL_IDX1 INDEX PARTITION LOCAL_IDX1 INDEX PARTITION
LOCAL_IDX1 INDEX
SQL>
create index g_idx1 on range1(data); ---global partition index
Index created.
create index gp_idx on range1(b) global
partition by range(b)
(
partition idx1 values less than (1000),
partition idx2 values less than (maxvalue)
);
|
十五、Application Tuning --应用调优