RE: Primary Constraints and Indexes

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Fri, 12 Jan 2001 14:49:28 -0800
Message-Id: <10739.126590@fatcity.com>

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_at_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

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2652.35">
<TITLE>RE: Primary Constraints and Indexes</TITLE>
</HEAD>
<BODY>

> -----Original Message-----
 > From: Arvind Aggarwal [<A =
HREF=3D"mailto:oradba9_at_yahoo.com">mailto:oradba9_at_yahoo.com</A>]
 > 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.
Received on Fri Jan 12 2001 - 16:49:28 CST