为主键的索引指定表空间:
创建时:
create table t (a date, b date,constraint pk_t primary key (a) using index tablespace idx) tablespace users;
创建后:
alter table t add constraint pk_t primary key(a) using index TABLESPACE idx
主键可以是分区索引,但如果是本地索引,就必须包含表的分区键
这和Oracle 如何保证纪录唯一有关
如果unique index key没有partition_key,那么每插入一行的纪录,需要扫描所有的索引分区才能验证整个表上这个记录是否唯一
否则只能保证它所进入的分区中,是唯一的
也就是,主键或唯一键含了分区键后,是可以保证分区中就可以搞清楚是否唯一了
测试:
[SQL] 纯文本查看复制代码
hr@ORCL> drop table t purge;
Table dropped.
hr@ORCL> create table t (id number not null,p_id number)
2 partition by range (p_id)
3 (
4 partition p1 values less than (2),
5 partition p2 values less than (3),
6 partition p3 values less than (4),
7 partition p4 values less than (5),
8 partition p5 values less than (maxvalue)
9 );
Table created.
hr@ORCL> insert into t select rownum,decode(mod(rownum,5),0,5,mod(rownum,5)) from dual connect by level<=100000;
100000 rows created.
hr@ORCL> commit;
Commit complete.
hr@ORCL> alter table t add constraint pk_t primary key (id) using index local;
alter table t add constraint pk_t primary key (id) using index local
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
hr@ORCL> alter table t add constraint pk_t primary key (id,p_id) using index local;
Table altered.
㈤ OCP_007考题:
[SQL] 纯文本查看复制代码
Q: 1 Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
Which three statements insert a row into the table? (Choose three.)
A. INSERT INTO employees
VALUES ( NULL, 'John', 'Smith');
B. INSERT INTO employees( first_name, last_name)
VALUES( 'John', 'Smith');
C. INSERT INTO employees
VALUES ( '1000', 'John', NULL);
D. INSERT INTO employees (first_name, last_name, employee_id)
VALUES ( 1000, 'John', 'Smith');
E. INSERT INTO employees (employee_id)
VALUES (1000);
F. INSERT INTO employees (employee_id, first_name, last_name)
VALUES ( 1000, 'John', ' ');
Answer: C, E, F