krislioe_at_gmail.com (xtanto) wrote in message news:<e1c9bd55.0410130133.76fb0378_at_posting.google.com>...
> Hi gurus,
>
> In our custom HR application, the employee master table has about 3,8
> million rows. Many transactions table has FK referring to the Big
> master table.
> (attached is the script sample)
> Question :
> Since the master table is big enough, should I still normalize here ?
> Means I have to JOIN to employee table to get the ENAME with the risk
> of slow join to big reference table ??
> Or this is the case where we should sacrifice normalization for
> performance?
>
> Thank you for your help,
> xtanto
>
>
> CREATE TABLE EMP
> ( EMPNO NUMBER(4) NOT NULL PRIMARY KEY,
> ENAME VARCHAR2(30),
> bla..bla.. another 50 columns );
>
> CREATE TABLE PROMOTION
> ( PROMOTION_ID NUMBER(6) NOT NULL PRIMARY KEY,
> EMPNO NUMBER(4) NOT NULL ,
> ENAME VARCHAR2(30), bla..bla..bla );
> ALTER TABLE PROMOTION ADD (
> CONSTRAINT FK_EMPNO FOREIGN KEY (EMPNO) REFERENCES EMP (EMPNO));
Most of applications performance problems stem from poor design. But
excessively normalized data require extensive joins to produce
necessary output.
You can partitioning master table and indexes, index-organized tables,
reverse key, ..., and, of course - use a right optimizer.
Dan
Received on Wed Oct 13 2004 - 13:49:34 CDT
