Xref: alice comp.databases.oracle.server:20290
Path: alice!news-feed.fnsi.net!news.idt.net!news-peer.gip.net!news.gsl.net!gip.net!news-peer.sprintlink.net!news-nysernet-5.sprintlink.net!news.sprintlink.net!128.122.253.90!newsfeed.nyu.edu!newsfeed.sgi.net!pitt.edu!newsfeed.pitt.edu!news
From: Joe Condle <condle@med.pitt.edu>
Newsgroups: comp.databases.oracle.server
Subject: Composite Key Problem?
Date: Tue, 05 May 1998 13:02:53 -0400
Organization: University of Pittsburgh
Lines: 58
Message-ID: <354F463D.45CA@med.pitt.edu>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Mailer: Mozilla 3.01 (X11; I; SunOS 5.5.1 sun4m)

I am trying to design queries to help determine different
levels of matching records.  I have a table of the following form

TABLE 1

 ID             NOT NULL VARCHAR2(32)
 LNAME          NOT NULL VARCHAR2(32)
 FNAME          NOT NULL VARCHAR2(32)
 MNAME          NOT NULL VARCHAR2(32)
 DOB            NOT NULL DATE
 SEX            NOT NULL VARCHAR2(32)
 RACE           NOT NULL VARCHAR2(32)
 ADDRESS        NOT NULL VARCHAR2(128)
 PHONE          NOT NULL VARCHAR2(32)
 ACCT           NOT NULL VARCHAR2(32)
 SSN            NOT NULL VARCHAR2(32)

This table has about 500,000 entires

   I have created a new table based on the first with this form

TABLE 2  
 
 IDA            NOT NULL VARCHAR2(32)
 IDB            NOT NULL VARCHAR2(32)
 LNAME          NOT NULL VARCHAR2(32)
 FNAME          NOT NULL VARCHAR2(32)
 MNAME          NOT NULL VARCHAR2(32)
 DOB            NOT NULL DATE
 SEX            NOT NULL VARCHAR2(32)
 RACE           NOT NULL VARCHAR2(32)
 ADDRESS        NOT NULL VARCHAR2(128)
 PHONE          NOT NULL VARCHAR2(32)
 ACCT           NOT NULL VARCHAR2(32)
 SSN            NOT NULL VARCHAR2(32)
 
Table 2 has a composite key of (IDA, LNAME, FNAME, DOB, SEX)
 
I then run my plsql program that marches down a cursor made up of
table1 and does a select against another cursor made up of table1
in an inner loop.  The second cursor select statement looks like this: 
select * from table1 
where a.lname = b.lname  
and   a.fname = b.fname
and     a.dob = b.dob 
and     a.sex = b.sex
and    a.mrn != b.mrn
 
Without the primary constraint on the table I get 25,964 of records.
With the primary key constraint I get 72.  This can not be right.
Can anyone shed some light on this.  

Thanks

Joe Condle
System Engineer
Medical ARchival Systems Inc.
University of Pittsburgh Medical Center