Message-Id: <10739.126590@fatcity.com> From: Jacques Kilchoer Date: Fri, 12 Jan 2001 14:49:28 -0800 Subject: RE: Primary Constraints and Indexes This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C07CE9.EB519370 Content-Type: text/plain; charset="iso-8859-1" > -----Original Message----- > From: Arvind Aggarwal [mailto:oradba9@yahoo.com] > Sent: vendredi, 12. janvier 2001 13:24 > > you cannot create 2 indexes with the same columns on a > table. Well, you can if you vary the order of the columns! :) SQL> create table t (n number, d date, v varchar2 (30)) ; Table created. SQL> create index i on t (n, d) ; Index created. SQL> create index i2 on t (d, n) ; Index created. One interesting thing - in Oracle 8 and above (I think I have the versions right) if you have an index on the columns in the reverse order of the columns in the primary key constraint, Oracle won't create another index for the primary key. SQL> create table t (n number, d date, v varchar2 (30)) ; Table created. SQL> create index i on t (d, n) ; Index created. SQL> -- Creating a primary key on n, d. SQL> -- The columns are in the reverse order in the index, but SQL> -- Oracle will use the existing index to enforce the pk SQL> -- constraint. SQL> alter table t add constraint t_pk primary key (n, d) ; SQL> select table_name, index_name, column_name 2 from user_ind_columns 3 where table_name = 'T' 4 order by table_name, index_name, column_position TABLE_NAME INDEX_NAME COLUMN_NAM ---------- ---------- ---------- T I D T I N ------ any ignorant comments made are the sole responsibility of J. R. Kilchoer and should not reflect adversely upon my employer. Jacques R. Kilchoer (949) 754-8816 Quest Software, Inc. 8001 Irvine Center Drive Irvine, California 92618 U.S.A. http://www.quest.com ------_=_NextPart_001_01C07CE9.EB519370 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: Primary Constraints and Indexes

> -----Original Message-----
> From: Arvind Aggarwal [mailto:oradba9@yahoo.com]
> Sent: vendredi, 12. janvier 2001 13:24
>
> you cannot create 2 indexes with the same = columns on a
> table.

Well, you can if you vary the order of the columns! = :)


SQL> create table t (n number, d date, v varchar2 = (30)) ;

Table created.

SQL> create index i on t (n, d) ;

Index created.

SQL> create index i2 on t (d, n) ;

Index created.


One interesting thing - in Oracle 8 and above (I = think I have the versions right) if you have an index on the columns in = the reverse order of the columns in the primary key constraint, Oracle = won't create another index for the primary key.

SQL> create table t (n number, d date, v varchar2 = (30)) ;

Table created.

SQL> create index i on t (d, n) ;

Index created.

SQL> -- Creating a primary key on n, d.
SQL> -- The columns are in the reverse order in = the index, but
SQL> -- Oracle will use the existing index to = enforce the pk
SQL> -- constraint.
SQL> alter table t add constraint t_pk primary = key (n, d) ;
SQL> select table_name, index_name, = column_name
  2  from user_ind_columns
  3  where table_name =3D 'T'
  4  order by table_name, index_name, = column_position

TABLE_NAME INDEX_NAME COLUMN_NAM
---------- ---------- ----------
T          = I          D
T          = I          N

------
any ignorant comments made are the sole = responsibility of J. R. Kilchoer and should not reflect adversely upon = my employer.

 
Jacques R. Kilchoer
(949) 754-8816
Quest Software, Inc.
8001 Irvine Center Drive
Irvine, California 92618
U.S.A.