1.选择主键的方式
oracle主键有两种,guid与自动增长
1.guid,长度为32,中间随便四条杠,保证主键不相同,调用guid方法就行
2.自动增长,oracle没有mysql那样,有identity自动增长语句,设置主键自动增长分为两步
create tablespace bschis datafile 'E:\app\yan\oradata\yans'
size 100M autoextend on logging online permanent EXTENT MANAGEMENT LOCAL
AUTOALLOCATE SEGMENT SPACE MANAGEMENT Auto BLOCKSIZE 8K ;
create user yanjun identified by yanjun default tablespace BSCHIS
grant connect to yanjun
grant dba to yanjun
grant unlimited tablespace to yanjun
系统权限回收:系统权限只能由DBA用户回收
命令: Revoke connect, resource from user50;
Oracle中修改列名不可以,但是可以删除列,增加列
alter table Tablename drop column column1
alter table Tablename add(column1 varchar2(20),column2 number(7,2)...)
以上为创表空间,用户过程
创表
create table s(
sid integer,
sname varchar2(20)
)
创SEQUENCE
create SEQUENCE s_sequence
increment by 1
start with 1
nomaxvalue
nocycle
cache 10;
创建trigger
create or replace trigger s_trigger before
insert on s FOR EACH ROW
begin
select s_sequence.nextval into:New.sid from dual;
end;
增加
insert into s(sname) values('yanjun')
2.大数据量查询优化
1.建索引
create index picture_album_idxss on s (sname);
2.表分区(范围、哈希、综和三种类型)
PARTITION BY RANGE(HASH)(sid)(
partition c1 values less than(10) tablespace bschis,
partition c2 values less than(10000) tablespace bschiss,
)