如何在组合键上添加群集? [英] How do I add a cluster on a composite key?

问题描述

我已经创建了一个集群

create cluster abc_clus
(abc_key int)
;

，然后基于该群集创建索引

and then created an index based on that cluster

create index abc_clus_idx
on cluster abc_clus;

我尝试在这4个表上添加此集群以进行 complex 连接

I tried adding this cluster on these 4 tables for a complex join

 create table number1
(
    dateofbirth date,  
    Time timestamp(0), 
    IDnumber int not null,
    class varchar(7) not null, 
primary key (dateofbirth, Time, class))
cluster abc_clus(class);

和

  create table number2(
    tutornumber int not null,
    forename varchar2(20) not null,
 constraint number2 primary key (tutornumber))
       cluster abc_clus(tutornumber);

和

create table number3
(
constraint number3 primary key (Roomnumber),
Roomnumber int not null, 
xyz varchar(20))
cluster abc_clus(Roomnumber3);

和

create table number4
(
constraint class_pk primary key (classnumber),
classnumber int not null)
 cluster abc_clus(classnumber);

但是，当我尝试此操作时，出现此错误:

However, when I try this, I get this error:

ORA-01753:列定义与群集列定义不兼容

ORA-01753: column definition incompatible with clustered column definition

我想知道将群集添加到组合键上的正确方法:名字，姓氏，地址.

I'm wondering how the correct way would be to add the cluster on the composite key: firstname, lastname, address.

我正在使用SQL plus.

I am using SQL plus.

谢谢

推荐答案

表列必须与集群列具有相同的数据类型.在您的示例中，这可以正常工作:

The table column needs to be the same datatype as the cluster column. In your example, this works fine:

create table test1 (
  id int
) cluster abc_clus(id);
Table TEST1 created.

如果数据类型匹配，则即使复合键也有效:

Even a composite key works, if the datatype matches:

create table test2 (
  a int,
  b int,
  primary key(a, b)
) cluster abc_clus(a);
Table TEST2 created.

但是，如果数据类型不同，则会收到错误消息:

However, if the datatype is different, you get your error message:

create table test3 (
  vc varchar2(7)
) cluster abc_clus(vc);
ORA-01753: column definition incompatible with clustered column definition

并且数据类型必须完全相同，即使int和number不兼容:

And the datatype has to be exactly the same, even int and number are not compatible:

create table test4 (
  n NUMBER
) cluster abc_clus(n);
ORA-01753: column definition incompatible with clustered column definition

您甚至可以拥有复合集群:

You can even have composite clusters:

创建集群idc_clus( 我是 d日期 );

create cluster idc_clus ( i int, d date );

在群集idc_clus上创建索引idc_clus_idx;

create index idc_clus_idx on cluster idc_clus;

创建表test5( 我是 d日期， 主键(i，d) )丛集idc_clus(i，d);

create table test5 ( i int, d date, primary key (i,d) ) cluster idc_clus(i, d);

这篇关于如何在组合键上添加群集?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！