Physical Tuning Options for Commonweather Corporation

Choosing Physical Tuning Options › Physical Tuning Options for Commonweather Corporation

Physical Tuning Options for Commonweather Corporation

Assign entities to areas

You need to assign entities to database areas to provide for efficient application runtime processing:

ORG-DEMO-REGION can hold all nonclustered entities. The DEPARTMENT, OFFICE, JOB, SKILL, and INSURANCE PLAN entities can be stored in this area of the database.
EMP-DEMO-REGION holds all entities clustered around the EMPLOYEE entity. The EMPLOYEE, EMPOSITION, EXPERTISE, and PROJECT entities should be stored together in this area.
INS-DEMO-REGION holds all entities clustered around the COVERAGE entity. The COVERAGE, NON-HOSPITAL CLAIM, HOSPITAL CLAIM, and DENTAL CLAIM entities can be stored in this area.

By placing Commonweather entities in separate areas, we enable programs to prepare only the area or areas required for a particular operation rather than the entire database. In addition, we reduce the likelihood of contention for heavily-used entities.

You might want to assign entities and indexes to separate areas.

Compress entities

The JOB and INSURANCE PLAN entities each contain a data element that provides descriptive information about a particular entity occurrence (JOB DESCRIPTION and PLAN DESCRIPTION). As such, these entities are good candidates for compression.

Relationship options

All relationships are linked to provide most efficient access. Since this is not a large database, it is not necessary to eliminate relationships between areas.

All clustered relationships are chained; all nonclustered relationships are indexed. This reduces I/O when accessing nonclustered relationships and reduces CPU when accessing clustered relationships.

The following relationships are sorted with the unique option to eliminate indexes used only to enforce unique constraints:

New Sorted Relationship	Sort Key	Index Eliminated
EMP-EMPOSITION	START DATE	JOB-NDX
EMP-EXPERTISE	SKILL CODE	EMP-NDX
NHC-PROC	PROCEDURE NUMBER	NON-HOSP-NDX
DC-PROC	PROCEDURE NUMBER	PROC-NDX

The following relationships are sorted to avoid sorting retrieval occurrences:

Sorted Relationship	Sort Key
DEPT-EMPLOYEE	EMP LAST NAME EMP FIRST NAME
OFFICE-EMPLOYEE	EMP LAST NAME EMP FIRST NAME
SKILL-EXPERTISE	SKILL LEVEL

All sorted relationships are order ascending except:

SKILL-EXPERTISE, since usually employees holding a skill should be listed such that those with the highest rating appear first
EMP-EMPOSITION, since position START DATEs are usually increasing in value