Re: performance database oracle

B.Coenen_at_gmail.com wrote:
> 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?

Alternative 2 will *only* be faster is a record for alternative 1 doesn't fit in one database block.
You should make a correct relational design. The initial design should *NOT* be influenced by technical considerations, or you will end up with all kind of kludges.
(Like the one you describe above)

-- 
Sybrand Bakker
Senior Oracle DBA