| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Cluster index / Constraint Index conflict?
A copy of this was sent to "Sean Zhang" <sean_at_cnfei.com>
(if that email address didn't require changing)
On Sat, 29 May 1999 01:24:05 GMT, you wrote:
>Hello, Everyone,
>
>The following is a problem with Oracle Index I can't resolve, Please
>give me your valuable advises. Thank you.
>
>Enviroment: Oracle 8i in Sun Solaris 2.6.
>Problem: Suppose I have 2 tables to be clustered together like the
>following:
> Master table : <invoices> with columns like : ( invoice# Number(7)
>Primary key, ....)
> Detail table: <Items> with columns like: ( invoice# Constraint
>F_key references Invoices(invoice#),
>
>part# varchar2(20),..........
>
>........
>
>Constraint P_key primary key ( invoice#, part#)
>
> ).
>
> If I want to Cluster the 2 tables on column <Invoice#>, an index must be
>created on
> the cluster key <INVOICE#>. But in order to enable the Primary key on
>the detail table,
> a second index on <INVOICE#, PART#> must be created. I understand the
>same key
> <Invoice#> can only be indexed once, so How can I have the cluster and at
>the same time
> be able to Enable the Primary key in detail table?
>
don't confuse the cluster index with an index on a table. Consider the following example:
SQL> CREATE CLUSTER c
2 ( invoice# NUMBER(10) )
3 SIZE 512
4 STORAGE (INITIAL 100K NEXT 50K PCTINCREASE 10);
Cluster created.
SQL>
SQL> CREATE TABLE po
2 (invoice# NUMBER(10) primary KEY )
3 cluster c(invoice#);
Table created.
SQL>
SQL> CREATE TABLE li
2 (invoice# NUMBER(10),
3 line# number,
4 primary key(invoice#,line#),
5 foreign key (invoice#) references po(invoice#)
6 )
7 CLUSTER c(invoice#);
Table created.
Datatypes for Table tkyte.po
Data Data
Column Name Type Length Nullable
------------------------- ------------------------------ ----------- --------
INVOICE# NUMBER (10,0) not null
Indexes on tkyte.po
Index Is Name Unique Indexed Columns ------------------------------ ------ ----------------------------------- SYS_C002767 Yes INVOICE#Datatypes for Table tkyte.li
Data Data
Column Name Type Length Nullable
------------------------- ------------------------------ ----------- --------
INVOICE# NUMBER (10,0) not null
LINE# NUMBER not null
Indexes on tkyte.li
Index Is Name Unique Indexed Columns ------------------------------ ------ ----------------------------------- SYS_C002768 Yes INVOICE#, LINE#
The indexes on the table are different from the cluster index and you will be able to do what you want.
>
>Sean Zhang
>mailto:sean_at_cnfei.com
>
>
>
>
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Fri May 28 1999 - 20:35:08 CDT
 |
 |