performance database oracle

From: <B.Coenen_at_gmail.com>
Date: 6 Jul 2006 00:40:03 -0700
Message-ID: <1152171603.118864.116590@q16g2000cwq.googlegroups.com>

Which case will be better for the performance to find a record? In total there will approximately 3.000.000 records. Where we need to keep information for the coming 30 years. So after 30 years there will be 30 times 3.000.000 records. So then it will hold 90.000.000 records.

Case 1

CREATE TABLE PERSON
(

  PERSONRECORDID                          INTEGER                  NOT

NULL, PRIMARY KEY

  YEAR                                                DATE
         NOT NULL, ALTERNATIVE KEY

  PERSONID                                        INTEGER

   NOT NULL, ALTERNATIVE KEY   AND THEN ABOUT 40 ATTRIBUTES OF A PERSONS )

When we want to find a person we search this in this table where the persons are defined and all the attributes are stated.

case 2

CREATE TABLE PERSONS
(

  PERSONRECORDID                           INTEGER                 NOT

NULL, PRIMARY KEY

  YEAR                                                DATE
         NOT NULL, ALTERNATIVE KEY

  PERSONID                                        INTEGER

   NOT NULL, ALTERNATIVE KEY AND THEN NO ATTRIBUTES )

CREATE TABLE PERSONS_ATTRIBUTES
(

  PERSONRECORDID                           INTEGER                  NOT

NULL, PRIMARY KEY   AND THEN ABOUT 40 ATTRIBUTES OF A PERSON )

ALTER TABLE PERSONS_ATTRIBUTES ADD ( CONSTRAINT FK01 FOREIGN KEY (PERSONRECORDID) REFERENCES PERSONS (PERSONRECORDID)) When we want to find a person with its attributes we search the person in table PERSONS where the person is defined and then get all the attrbutes that are stated for the person in table PERSONS_ATTRIBUTES.

But what will be faster? to find a legal unit in an table with all the attributes stated or in a table with no attributes and then go to the table with all the attributes to get the attributes? Received on Thu Jul 06 2006 - 02:40:03 CDT