Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2011

    Unanswered: Will clustering some of these tables be beneficial?

    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:
    (patientNumb, doctorID, treatmentCode)


    (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),
    patientName VARCHAR2(20) NOT NULL,
    dateOfBirth DATE NOT NULL,
    address VARCHAR2(25) NOT NULL,
    dateAdmitted DATE NOT NULL,
    clinic CHAR(1) NOT NULL
    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)
    numbOfWorkHours NUMBER NOT NULL,
    CONSTRAINT fk_schedule_doctorID FOREIGN KEY(doctorID) REFERENCES Doctor(doctorID),
    CONSTRAINT pk_doctorSchedule PRIMARY KEY(doctorID,scheduleDate,clinic));

  2. #2
    Join Date
    Jul 2003
    if you already know the advantages and disadvantages then you can probably evaluate the design implications.

    Personally I see more negative than positive out of clustering the tables but honestly i never use it so ...
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts