当前位置:Gxlcms > mysql > oracle11g的表

oracle11g的表

时间:2021-07-01 10:21:17 帮助过:40人阅读

create cluster scott.cluster1(code_key number); create table scott.student (sno1 number, sname varchar2(10)) cluster scott.cluster1(sno1); create table scott.address (sno2 number, zz varchar2(10)) cluster scott.cluster1(sno2); create index

create cluster scott.cluster1(code_key number);

create table scott.student (sno1 number, sname varchar2(10)) cluster scott.cluster1(sno1);

create table scott.address (sno2 number, zz varchar2(10)) cluster scott.cluster1(sno2);

create index index1 on cluster scott.cluster1; --为簇创建索引

//1.首先创建簇表

SQL> create cluster cluster1(share_col number(10));

Cluster created


SQL> create table tab1(sno number(10),sname varchar2(20)) cluster cluster1(sno);

Table created


SQL> create table tab2(sno number(10),saddr varchar2(20)) cluster cluster1(sno);

Table created


SQL> create index cluster_index on cluster cluster1;

Index created


SQL> select uc.CLUSTER_NAME,uc.TABLESPACE_NAME,uc.CLUSTER_TYPE from user_clusters uc where uc.CLUSTER_NAME='CLUSTER1';

CLUSTER_NAME TABLESPACE_NAME CLUSTER_TYPE

------------------------------ ------------------------------ ------------

CLUSTER1 USERS INDEX


SQL> select ut.TABLE_NAME,ut.TABLESPACE_NAME,ut.CLUSTER_NAME from user_tables ut where ut.TABLE_NAME='TEST1';

TABLE_NAME TABLESPACE_NAME CLUSTER_NAME

------------------------------ ------------------------------ ------------------------------


SQL> select ut.TABLE_NAME,ut.TABLESPACE_NAME,ut.CLUSTER_NAME from user_tables ut where ut.CLUSTER_NAME='CLUSTER1';

TABLE_NAME TABLESPACE_NAME CLUSTER_NAME

------------------------------ ------------------------------ ------------------------------

TAB2 USERS CLUSTER1

TAB1 USERS CLUSTER1


SQL>

先删除表,再删除簇表


四、临时表:


存放临时数据,可以使用临时表;临时表被每个session

单独使用,即:不同session看到的临时表中的数据可能不一

样。

如果在退出session时删除临时表中的数据,可以使用on

commit preserve rows;如果在用户commit或rollback时删

除临时表中的数据,可以使用on commit delete rows;

从v$sort_usage中查看正在使用临时表空间的session信

息和SQL语句的ID号,从v$sort_segment中查看临时表空间中

的段的使用情况。

临时表在临时表空间中保存。

create global temporary table temp_tab1() on commmit preserve rows/delete rows;


五、分区表:



q允许用户将一个表分成多个分区

q用户可以执行查询,只访问表中的特定分区

q将不同的分区存储在不同的磁盘,提高访问性能和安全性

q可以独立地备份和恢复每个分区

分区方式有一下几种:

      1. 范围分区:以表中的一个列或一组列的值的范围分区

SQL> create table test_partition(id number(20),account number(20))

2 partition by range(id)(

3 partition part1 values less than(1000),

4 partition part2 values less than(2000),

5 partition part3 values less than(3000));

Table created


SQL> select utp.table_name,utp.partition_name,utp.high_value from user_tab_partitions utp where utp.table_name='TEST_PARTITION';

TABLE_NAME PARTITION_NAME HIGH_VALUE

------------------------------ ------------------------------ --------------------------------------------------------------------------------

TEST_PARTITION PART1 1000//<1000

TEST_PARTITION PART2 2000>=1000 和<2000

TEST_PARTITION PART3 3000


SQL> insert into test_partition part1 values(1,100);

1 row inserted


SQL> insert into test_partition values(1000,200);

1 row inserted


SQL> insert into test_partition part1 values(2000,300);//标注为会被忽略

1 row inserted


SQL> insert into test_partition values(5000,400);

insert into test_partition values(5000,400)

ORA-14400: 插入的分区关键字未映射到任何分区


SQL> select * from test_partition partition(part1);

ID ACCOUNT

--------------------- ---------------------

1 100


SQL>

SQL> select * from test_partition partition(part2);

ID ACCOUNT

--------------------- ---------------------

1000 200


SQL> alter table test_partition add partition part4 values less than(maxvalue);//无上限

Table altered


SQL> insert into test_partition values(6000,600);

1 row inserted


SQL> select * from test_partition partition(part4);

ID ACCOUNT

--------------------- ---------------------

6000 600


SQL>


2.散列分区

允许用户对不具有逻辑范围的数据进行分区

通过在分区键上执行HASH函数决定存储的分区

将数据平均地分布到不同的分区

SQL> create table test_partition_hash(id number(20),name varchar(20))

2 partition by hash(id)(//散列分区是通过hash算法得到分区来进行的

3 partition part1,partition part2,partition part3);

Table created


SQL> select utp.table_name,utp.partition_name,utp.high_value from user_tab_partitions utp where utp.table_name='TEST_PARTITION_HASH';

TABLE_NAME PARTITION_NAME HIGH_VALUE

------------------------------ ------------------------------ --------------------------------------------------------------------------------

TEST_PARTITION_HASH PART1 //所以不存在high_value

TEST_PARTITION_HASH PART2

TEST_PARTITION_HASH PART3


SQL> insert into test_partition_hash values(1,'张三');

1 row inserted


SQL> insert into test_partition_hash values(2,'李四');

1 row inserted


SQL> insert into test_partition_hash values(3,'王五');

1 row inserted

SQL>

SQL> select * from test_partition_hash partition(part1);

ID NAME

--------------------- --------------------


SQL> select * from test_partition_hash partition(part2);

ID NAME

--------------------- --------------------

1 张三

3 王五


SQL> select * from test_partition_hash partition(part3);

ID NAME

--------------------- --------------------

2 李四


SQL>



3. 列表分区

允许用户将不相关的数据组织在一起

注意:列表分区是针对于可以列举的类型进行分区的

SQL> create table test_partition_list(id number(20),name varchar2(20),address varchar2(20))

2 partition by list(address)(

3 partition 上北 values('九江'),

4 partition 下南 values('赣州','鹰潭'),

5 partition 左西 values('抚州','新余'),

6 partition 右东 values('景德镇'));

Table created


SQL> insert into test_partition_list values(1,'吴xx','九江');

1 row inserted


SQL> insert into test_partition_list values(2,'陈xx','赣州');

1 row inserted


SQL> insert into test_partition_list values(3,'邹xx','抚州');

1 row inserted


SQL> insert into test_partition_list values(4,'刘xx','景德镇');

1 row inserted


SQL> select * from test_partition_list partition(上北);

ID NAME ADDRESS

--------------------- -------------------- --------------------

1 吴xx 九江


SQL> select * from test_partition_list partition(下南);

ID NAME ADDRESS

--------------------- -------------------- --------------------

2 陈xx 赣州


SQL>

4. 复合分区

范围分区与散列分区或列表分区的组合//只有这两种组合而且顺序不能颠倒

SQL> create table test_partition_compass(

2 id number(20),name varchar2(20))

3 partition by range(id)//主分区

4 subpartition by hash(name)//子分区

5 subpartitions 4(//每个主分区包括4个子分区

6 partition part1 values less than(100),//第一个分区

7 partition part2 values less than(200),

8 partition part3 values less than(maxvalue));

Table created

通过EM查看表的分区信息如下:

A browser with Javascript enabled is required for this page to operate properly.

Partitions

Partitioning Description

Partitioning Method Range-Hash
Partitioning Columns ID
Number of Partitions 3
Subpartitioning Columns NAME
Number of Subpartitions 12

Partition Definitions

Previous 1-3 of 3 Next
Partition Name High Value - ID (NUMBER) Subpartition Default Tablespace Subpartitions
PART1 100 USERS 4
PART2 200 USERS 4
PART3 MAXVALUE USERS 4

Subpartition Definitions

Previous 1-12 of 12 Next
Partition Name Subpartition Name Tablespace
PART1 SYS_SUBP21 USERS
SYS_SUBP22 USERS
SYS_SUBP23 USERS
SYS_SUBP24 USERS
PART2 SYS_SUBP25 USERS
SYS_SUBP26 USERS
....... ........ ........


5.11g新增的表分区的类型 引用分区

引用分区:基于由外键引用的父表的分区的方

法,它依赖已有的父表子表的关系,子表通过外键

关联到父表,进而继承了父表的分区方式而不需自

己创建,子表还继承了父表的维护操作。

1,主表是范围分区,子表是引用分区

2,主表是列表分区,子表是引用分区

3,主表是散列分区,子表是引用分区

//创建范围分区


SQL>

SQL> create table test_partition_student(

2 id number(10),name varchar2(20),grade varchar2(20),constraints pk_student primary key(id))

3 partition by range(id) (

4 partition part1 values less than(100),

5 partition part2 values less than(200),

6 partition part3 values less than(maxvalue));

Table created


SQL> create table test_partition_score(

2 id number(10) primary key ,sid number(10),goal number(10),constraints fk_student_score foreign key(sid)

3 references test_partition_student(id))

4 partition by reference(fk_student_score);

create table test_partition_score(

id number(10) primary key ,sid number(10),goal number(10),constraints fk_student_score foreign key(sid)

references test_partition_student(id))

partition by reference(fk_student_score)

ORA-14652: 不支持引用分区外键,关联的外键必须是非空


SQL>

SQL> create table test_partition_score(

2 id number(10) primary key ,sid number(10) not null,goal number(10),constraints fk_student_score foreign key(sid)

3 references test_partition_student(id))

4 partition by reference(fk_student_score);

Table created

SQL>

SQL> select upt.table_name,upt.partition_name,upt.high_value from user_tab_partitions upt where upt.table_name in(upper('test_partition_score'),upper('test_partition_student'));

TABLE_NAME PARTITION_NAME HIGH_VALUE

------------------------------ ------------------------------ --------------------------------------------------------------------------------

TEST_PARTITION_STUDENT PART1 100

TEST_PARTITION_STUDENT PART2 200

TEST_PARTITION_STUDENT PART3 MAXVALUE

TEST_PARTITION_SCORE PART1

TEST_PARTITION_SCORE PART2

TEST_PARTITION_SCORE PART3

6 rows selected

分区名称相同

SQL> insert into TEST_PARTITION_STUDENT values(1,'张三','二年级');

1 row inserted


SQL> insert into TEST_PARTITION_STUDENT values(111,'李四','三年级');

1 row inserted


SQL> insert into TEST_PARTITION_SCORE values(1,1,100);

1 row inserted


SQL> insert into TEST_PARTITION_SCORE values(2,111,99);

1 row inserted


SQL> select * from TEST_PARTITION_STUDENT partition(part1);

ID NAME GRADE

----------- -------------------- --------------------

1 张三 二年级


SQL> select * from TEST_PARTITION_SCORE partition(part1);

ID SID GOAL

----------- ----------- -----------

1 1 100


SQL>


6.11g新增的表分区的类型 间隔分区


间隔分区:可以完全自动地根据间隔阈值创建范

围分区,它是范围分区的扩展 。

在数据仓库中有广泛的应用。


SQL> select * from user_part_tables;//存放的是分区表的情况

SQL> select * from user_tab_partitions;/存放的是表分区的情况


SQL> create table test_partition_interval(

2 id number(10),name varchar2(20),num number(20),_date date)

3 partition by range(_date)

4 interval(NUMTOYMINTERVAL(1,'MONTH'))(

5 partition part1 values less than(to_date(20140101,'yyyymmdd')));

create table test_partition_interval(

id number(10),name varchar2(20),num number(20),_date date)

partition by range(_date)

interval(NUMTOYMINTERVAL(1,'MONTH'))(

partition part1 values less than(to_date(20140101,'yyyymmdd')))

ORA-00911: 无效字符//不能使用_开头的属性名称


SQL>

SQL> create table test_partition_interval(

2 id number(10),name varchar2(20),num number(20),s_date date)

3 partition by range(s_date)

4 interval(NUMTOYMINTERVAL(1,'MONTH'))(//按照一个月来间隔增长的

5 partition part1 values less than(to_date(20140101,'yyyymmdd')));//初始的月份

Table created


SQL> select sysdate from dual;

SYSDATE

-----------

13-1月-15 1:


SQL> INSERT INTO test_partition_interval VALUES(1,'上衣',20,'01-1月-2014');

1 row inserted

SQL>

SQL> INSERT INTO test_partition_interval VALUES(1,'上衣',20,'01-2月-2014');

1 row inserted


SQL> select utp.table_name,utp.partition_name,utp.high_value from user_tab_partitions utp where utp.table_name='TEST_PARTITION_INTERVAL';

TABLE_NAME PARTITION_NAME HIGH_VALUE

------------------------------ ------------------------------ --------------------------------------------------------------------------------

TEST_PARTITION_INTERVAL PART1 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TEST_PARTITION_INTERVAL SYS_P41 TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TEST_PARTITION_INTERVAL SYS_P42 TO_DATE(' 2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


SQL> INSERT INTO test_partition_interval VALUES(1,'上衣',20,'01-10月-2014');

1 row inserted


SQL> select utp.table_name,utp.partition_name,utp.high_value from user_tab_partitions utp where utp.table_name='TEST_PARTITION_INTERVAL';

TABLE_NAME PARTITION_NAME HIGH_VALUE

------------------------------ ------------------------------ --------------------------------------------------------------------------------

TEST_PARTITION_INTERVAL PART1 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TEST_PARTITION_INTERVAL SYS_P41 TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TEST_PARTITION_INTERVAL SYS_P42 TO_DATE(' 2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

TEST_PARTITION_INTERVAL SYS_P43 TO_DATE(' 2014-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


SQL>


7. 11g新增的表分区的类型--基于虚拟列的分区

基于虚拟列的分区:把分区建立在某个虚拟列

上,即建立在函数或表达式的计算结果上,来完成

某种任务。

SQL>

SQL> create table test_partition_virtual(

2 id number(10),name varchar2(20),num number(20),price number(8,2),total_price as price*num virtual)

3 partition by range(total_price)(

4 partition part1 values less than(1000),

5 partition part2 values less than(2000),

6 partition part3 values less than(maxvalue));

ORA-02000: 缺失 ( 关键字


SQL>

SQL>

SQL> create table test_partition_virtual(

2 id number(10),name varchar2(20),num number(20),price number(8,2),total_price as (price*num) virtual)

3 partition by range(total_price)(

4 partition part1 values less than(1000),

5 partition part2 values less than(2000),

6 partition part3 values less than(maxvalue));

Table created


SQL> insert into test_partition_virtual(id,name,num,price) values(1,'上衣',10,100);

1 row inserted


SQL> insert into test_partition_virtual(id,name,num,price) values(1,'上衣',20,100);

1 row inserted


SQL> select * from test_partition_virtual partition(part1);

ID NAME NUM PRICE TOTAL_PRICE

----------- -------------------- --------------------- ---------- -----------


SQL> select * from test_partition_virtual partition(part2);

ID NAME NUM PRICE TOTAL_PRICE

----------- -------------------- --------------------- ---------- -----------

1 上衣 10 100.00 1000


SQL>



8. 11g新增的表分区的类型系统分区


系统分区:不指定分区列,由ORACLE来完成分

区的控制和管理,它没有了范围分区或列表分区的

界限。

分区维护操作

q分区维护操作修改已分区表的分区。

q分区维护的类型:

q计划事件 - 定期删除最旧的分区

q非计划事件 - 解决应用程序或系统问题

q分区维护操作有:

q添加分区

q删除分区

q截断分区

q合并分区

q拆分分区


SQL> alter table test_partition add partition values less than(6000);

alter table test_partition add partition values less than(6000)

ORA-14074: 分区界限必须调整为高于最后一个分区界限

分区因为是添加在最后以后分区上的

//删除分区

SQL> alter table test_partition drop partition part4;

Table altered

//增加分区

SQL> alter table test_partition add partition part4 values less than(7000);

Table altered

//拆分分区


SQL> alter table test_partition merge partitions part1,part2 into partition part2;

Table altered

//合并分区

SQL> alter table test_partition split partition part2 at(1000) into (partition part1 ,partition part2);

SQL> alter table test_partition split partition part2 at(1000) into (partition part1 ,partition part2);

Table altered

//截断分区

SQL> alter table test_partition truncate partition part3;

Table truncated


SQL>

人气教程排行