Would clustering these tables be a problem:
1)Patient and Pat_Doc_Treat via cluster key (patientNumb) -- I know that if there are excessive inserts, it will effect performance. patientNumb is going to be having lots of inserts since patients are going to increase.
2) Doctor and Pat_Doc_Treat via cluster key (doctorID) -- Good idea or not?
3) Treatment and Pat_Doc_Treat via cluster key (treatmentCode) -- Not a good idea even though it doesn't have much inserts.
In Pat_Doc_Treat, the composite primary key is made of patientNumb, doctorID, treatmentCode...if I plan to cluster any of the above tables, will it affect performance in any way?
About the composite primary key and automatically made into primary indexes...are they treated as one or separated such as below:
1)
(patientNumb, doctorID, treatmentCode)
2)
(patientNumb)
(doctorID)
(treatmentCode)
Code:
CREATE TABLE Doctor
(doctorID CHAR(4),
doctorName VARCHAR2(20) NOT NULL,
pagerNumb CHAR(11) NOT NULL,
CONSTRAINT pk_doctorID PRIMARY KEY(doctorID));
CREATE TABLE Patient
(patientNumb CHAR(4),
SS CHAR(9) NOT NULL,
patientName VARCHAR2(20) NOT NULL,
dateOfBirth DATE NOT NULL,
address VARCHAR2(25) NOT NULL,
dateAdmitted DATE NOT NULL,
clinic CHAR(1) NOT NULL
CHECK(clinic='A'),
CONSTRAINT pk_patientNumb PRIMARY KEY(patientNumb));
CREATE TABLE Treatment
(treatmentCode CHAR(4),
description VARCHAR2(20) NOT NULL,
CONSTRAINT pk_treatmentCode PRIMARY KEY(treatmentCode));
CREATE TABLE Pat_Doc_Treat
(patientNumb CHAR(4),
doctorID CHAR(4),
treatmentCode CHAR(4),
treatmentDate DATE NOT NULL,
comments VARCHAR2(20),
CONSTRAINT fk_patientNumb FOREIGN KEY(patientNumb) REFERENCES Patient(patientNumb),
CONSTRAINT fk_doctorID FOREIGN KEY(doctorID) REFERENCES Doctor(doctorID),
CONSTRAINT fk_treatmentCode FOREIGN KEY(treatmentCode) REFERENCES Treatment(treatmentCode),
CONSTRAINT pk_pat_doc_treat PRIMARY KEY(patientNumb,doctorID,treatmentCode));
CREATE TABLE Schedule
(doctorID CHAR(4),
scheduleDate DATE,
clinic CHAR(1)
CHECK(clinic='A'),
numbOfWorkHours NUMBER NOT NULL,
CONSTRAINT fk_schedule_doctorID FOREIGN KEY(doctorID) REFERENCES Doctor(doctorID),
CONSTRAINT pk_doctorSchedule PRIMARY KEY(doctorID,scheduleDate,clinic));
dBforums
>
Will clustering some of these tables be beneficial?
12-07-11, 12:50
Linear Mode
