chapter 5: physical database design and performance
DESCRIPTION
Modern Database Management. Chapter 5: Physical Database Design and Performance. Define terms Describe the physical database design process Choose storage formats for attributes Select appropriate file organizations Describe three types of file organization - PowerPoint PPT PresentationTRANSCRIPT
CHAPTER 5:CHAPTER 5:PHYSICAL DATABASE DESIGN AND PHYSICAL DATABASE DESIGN AND PERFORMANCEPERFORMANCE
1
Modern Database Management
OBJECTIVESOBJECTIVES
Define termsDefine terms Describe the physical database design Describe the physical database design
processprocess Choose storage formats for attributesChoose storage formats for attributes Select appropriate file organizationsSelect appropriate file organizations Describe three types of file organizationDescribe three types of file organization Describe indexes and their appropriate useDescribe indexes and their appropriate use Translate a database model into efficient Translate a database model into efficient
structuresstructures Know when and how to use denormalizationKnow when and how to use denormalization
2
PHYSICAL DATABASE PHYSICAL DATABASE DESIGNDESIGN
Purpose–translate the logical Purpose–translate the logical description of data into the description of data into the technical technical specificationsspecifications for storing and for storing and retrieving dataretrieving data
Goal–create a design for storing data Goal–create a design for storing data that will provide that will provide adequate adequate performanceperformance and ensure and ensure database database integrityintegrity, , securitysecurity, and , and recoverabilityrecoverability
3
PHYSICAL DESIGN PROCESSPHYSICAL DESIGN PROCESS
4
Normalized relations
Volume estimates
Attribute definitions
Response time expectations
Data security needs
Backup/recovery needs
Integrity expectations
DBMS technology used
Inputs
Attribute data types
Physical record descriptions (doesn’t always match logical design)
File organizations
Indexes and database architectures
Query optimization
Leads to
Decisions
PHYSICAL DESIGN FOR PHYSICAL DESIGN FOR REGULATORY REGULATORY COMPLIANCECOMPLIANCE
Sarbanes- Oxley Act (SOX) – protect Sarbanes- Oxley Act (SOX) – protect investors by improving accuracy and investors by improving accuracy and reliabilityreliability
Committee of Sponsoring Organizations Committee of Sponsoring Organizations (COSO) of the Treadway Commission(COSO) of the Treadway Commission
IT Infrastructure Library (ITIL)IT Infrastructure Library (ITIL) Control Objectives for Information and Control Objectives for Information and
Related Technology (COBIT)Related Technology (COBIT)
5
Regulations and standards that impact physical design decisions
6
Figure 5-1 Composite usage map (Pine Valley Furniture Company)
6
7
Figure 5-1 Composite usage map (Pine Valley Furniture Company) (cont.)
Data volumes
7
8
Figure 5-1 Composite usage map (Pine Valley Furniture Company) (cont.)
Access Frequencies (per hour)
8
9
Figure 5-1 Composite usage map (Pine Valley Furniture Company) (cont.)
Usage analysis:14,000 purchased parts accessed per hour 8000 quotations accessed from these 140 purchased part accesses 7000 suppliers accessed from these 8000 quotation accesses
9
10
Figure 5-1 Composite usage map (Pine Valley Furniture Company) (cont.)
Usage analysis:7500 suppliers accessed per hour 4000 quotations accessed from these 7500 supplier accesses 4000 purchased parts accessed from these 4000 quotation accesses
10
DESIGNING FIELDSDESIGNING FIELDS
Field: smallest unit of Field: smallest unit of application data recognized by application data recognized by system softwaresystem software
Field design Field design Choosing data typeChoosing data type Coding, compression, encryptionCoding, compression, encryption Controlling data integrityControlling data integrity
11
CHOOSING DATA TYPESCHOOSING DATA TYPES
12
13
Figure 5-2 Example of a code look-up table(Pine Valley Furniture Company)
Code saves space, but costs an additional lookup to obtain actual value
13
FIELD DATA INTEGRITYFIELD DATA INTEGRITY
Default value–assumed value if no Default value–assumed value if no explicit valueexplicit value
Range control–allowable value limitations Range control–allowable value limitations (constraints or validation rules)(constraints or validation rules)
Null value control–allowing or prohibiting Null value control–allowing or prohibiting empty fieldsempty fields
Referential integrity–range control (and Referential integrity–range control (and null value allowances) for foreign-key to null value allowances) for foreign-key to primary-key match-upsprimary-key match-ups
14
Sarbanes-Oxley Act (SOX) legislates importance of financial data integrity
HANDLING MISSING DATAHANDLING MISSING DATA
Substitute an estimate of the missing Substitute an estimate of the missing value (e.g., using a formula)value (e.g., using a formula)
Construct a report listing missing valuesConstruct a report listing missing values In programs, ignore missing data unless In programs, ignore missing data unless
the value is significant (sensitivity testing)the value is significant (sensitivity testing)
15
Triggers can be used to perform these operations.
DENORMALIZATIONDENORMALIZATION Transforming Transforming normalizednormalized relations into relations into non-non-
normalizednormalized physical record specifications physical record specifications Benefits:Benefits:
Can improve performance (speed) by reducing number of Can improve performance (speed) by reducing number of table lookups (i.e. table lookups (i.e. reduce number of necessary join reduce number of necessary join queriesqueries))
Costs (due to data duplication)Costs (due to data duplication) Wasted storage spaceWasted storage space Data integrity/consistency threatsData integrity/consistency threats
Common denormalization opportunitiesCommon denormalization opportunities One-to-one relationship (Fig. 5-3)One-to-one relationship (Fig. 5-3) Many-to-many relationship with non-key attributes Many-to-many relationship with non-key attributes
(associative entity) (Fig. 5-4)(associative entity) (Fig. 5-4) Reference data (1:N relationship where 1-side has data not Reference data (1:N relationship where 1-side has data not
used in any other relationship) (Fig. 5-5)used in any other relationship) (Fig. 5-5)
16
17
Figure 5-3 A possible denormalization situation: two entities with one-to-one relationship
17
18
Figure 5-4 A possible denormalization situation: a many-to-many relationship with nonkey attributes
Extra table access required
Null description possible
18
19
Figure 5-5A possible denormalization situation:reference data
Extra table access required
Data duplication
19
DENORMALIZE WITH DENORMALIZE WITH CAUTIONCAUTION
Denormalization canDenormalization can Increase chance of errors and inconsistenciesIncrease chance of errors and inconsistencies Reintroduce anomaliesReintroduce anomalies Force reprogramming when business rules Force reprogramming when business rules
changechange Perhaps other methods could be used to Perhaps other methods could be used to
improve performance of joinsimprove performance of joins Organization of tables in the database (file Organization of tables in the database (file
organization and clustering)organization and clustering) Proper query design and optimizationProper query design and optimization
20
PARTITIONINGPARTITIONING Horizontal PartitioningHorizontal Partitioning: Distributing the rows : Distributing the rows
of a logical relation into several separate tablesof a logical relation into several separate tables Useful for situations where different users need Useful for situations where different users need
access to different rowsaccess to different rows Three types: Key Range Partitioning, Hash Three types: Key Range Partitioning, Hash
Partitioning, or Composite PartitioningPartitioning, or Composite Partitioning Vertical PartitioningVertical Partitioning: Distributing the columns : Distributing the columns
of a logical relation into several separate of a logical relation into several separate physical tablesphysical tables Useful for situations where different users need Useful for situations where different users need
access to different columnsaccess to different columns The primary key must be repeated in each fileThe primary key must be repeated in each file
Combinations of Horizontal and VerticalCombinations of Horizontal and Vertical
21
PARTITIONING PROS AND CONSPARTITIONING PROS AND CONS Advantages of Partitioning:Advantages of Partitioning:
Efficiency: Records used together are grouped togetherEfficiency: Records used together are grouped together Local optimization: Each partition can be optimized for Local optimization: Each partition can be optimized for
performanceperformance Security: data not relevant to users are segregatedSecurity: data not relevant to users are segregated Recovery and uptime: smaller files take less time to back upRecovery and uptime: smaller files take less time to back up Load balancing: Partitions stored on different disks, reduces Load balancing: Partitions stored on different disks, reduces
contentioncontention Disadvantages of Partitioning:Disadvantages of Partitioning:
Inconsistent access speed: Slow retrievals across partitionsInconsistent access speed: Slow retrievals across partitions Complexity: Non-transparent partitioningComplexity: Non-transparent partitioning Extra space or update time: Duplicate data; access from Extra space or update time: Duplicate data; access from
multiple partitionsmultiple partitions
22
ORACLE’S HORIZONTAL ORACLE’S HORIZONTAL PARTITIONINGPARTITIONING Range partitioningRange partitioning
Partitions defined by range of field valuesPartitions defined by range of field values Could result in unbalanced distribution of rowsCould result in unbalanced distribution of rows Like-valued fields share partitionsLike-valued fields share partitions
Hash partitioningHash partitioning Partitions defined via hash functionsPartitions defined via hash functions Will guarantee balanced distribution of rowsWill guarantee balanced distribution of rows Partition could contain widely varying valued fieldsPartition could contain widely varying valued fields
List partitioningList partitioning Based on predefined lists of values for the partitioning Based on predefined lists of values for the partitioning
keykey Composite partitioningComposite partitioning
Combination of the other approachesCombination of the other approaches
23
DESIGNING PHYSICAL DATABASE DESIGNING PHYSICAL DATABASE FILESFILES Physical File: Physical File:
A named portion of secondary memory allocated A named portion of secondary memory allocated for the purpose of storing physical recordsfor the purpose of storing physical records
Tablespace–named logical storage unit in which Tablespace–named logical storage unit in which data from multiple tables/views/objects can be data from multiple tables/views/objects can be storedstored
Tablespace componentsTablespace components Segment – a table, index, or partitionSegment – a table, index, or partition Extent–contiguous section of disk spaceExtent–contiguous section of disk space Data block – smallest unit of storageData block – smallest unit of storage
24
25
Figure 5-6 DBMS terminology in an Oracle 11g environment
25
FILE ORGANIZATIONSFILE ORGANIZATIONS Technique for physically arranging records of a file Technique for physically arranging records of a file
on secondary storageon secondary storage Factors for selecting file organization:Factors for selecting file organization:
Fast data retrieval and throughputFast data retrieval and throughput Efficient storage space utilizationEfficient storage space utilization Protection from failure and data lossProtection from failure and data loss Minimizing need for reorganizationMinimizing need for reorganization Accommodating growthAccommodating growth Security from unauthorized useSecurity from unauthorized use
Types of file organizationsTypes of file organizations SequentialSequential IndexedIndexed HashedHashed
26
27
Figure 5-7a Sequential file organization
If not sortedAverage time to find desired record = n/2
Records of the file are stored in sequence by the primary key field values.
If sorted – every insert or delete requires resort
27
INDEXED FILE INDEXED FILE ORGANIZATIONSORGANIZATIONS
Storage of records sequentially or Storage of records sequentially or nonsequentially with an index that allows nonsequentially with an index that allows software to locate individual recordssoftware to locate individual records
IndexIndex: a table or other data structure used to : a table or other data structure used to determine in a file the location of records that determine in a file the location of records that satisfy some conditionsatisfy some condition
Primary keys are automatically indexedPrimary keys are automatically indexed Other fields or combinations of fields can also Other fields or combinations of fields can also
be indexed; these are called secondary keys be indexed; these are called secondary keys (or nonunique keys)(or nonunique keys)
28
29
Figure 5-7b Indexed file organization
uses a tree searchAverage time to find desired record = depth of the tree
29
30
Figure 5-7cHashed file organization
Hash algorithmUsually uses division-remainder to determine record position. Records with same position are grouped in lists.
30
31
Figure 6-8 Join Indexes–speeds up join operations
a) Join index for common non-key columns
b) Join index for matching foreign key (FK) and primary key (PK)
31
3232
CLUSTERING FILESCLUSTERING FILES In some relational DBMSs, related In some relational DBMSs, related
records from different tables can be records from different tables can be stored together in the same disk areastored together in the same disk area
Useful for improving performance of join Useful for improving performance of join operationsoperations
Primary key records of the main table Primary key records of the main table are stored adjacent to associated foreign are stored adjacent to associated foreign key records of the dependent tablekey records of the dependent table
e.g. Oracle has a CREATE CLUSTER e.g. Oracle has a CREATE CLUSTER commandcommand
33
RULES FOR USING INDEXESRULES FOR USING INDEXES
1.1. Use on larger tablesUse on larger tables2.2. Index the primary key of each tableIndex the primary key of each table3.3. Index search fields (fields frequently Index search fields (fields frequently
in WHERE clause)in WHERE clause)4.4. Fields in SQL ORDER BY and GROUP Fields in SQL ORDER BY and GROUP
BY commandsBY commands5.5. When there are >100 values but not When there are >100 values but not
when there are <30 valueswhen there are <30 values
34
RULES FOR USING INDEXES RULES FOR USING INDEXES (CONT.)(CONT.)
6.6. Avoid use of indexes for fields with long Avoid use of indexes for fields with long values; perhaps compress values firstvalues; perhaps compress values first
7.7. If key to index is used to determine If key to index is used to determine location of record, use surrogate (like location of record, use surrogate (like sequence number) to allow even spread in sequence number) to allow even spread in storage areastorage area
8.8. DBMS may have limit on number of DBMS may have limit on number of indexes per table and number of bytes per indexes per table and number of bytes per indexed field(s)indexed field(s)
9.9. Be careful of indexing attributes with null Be careful of indexing attributes with null values; many DBMSs will not recognize values; many DBMSs will not recognize null values in an index searchnull values in an index search
35
QUERY OPTIMIZATIONQUERY OPTIMIZATION Parallel query processing–possible when working in Parallel query processing–possible when working in
multiprocessor systemsmultiprocessor systems Overriding automatic query optimization–allows for Overriding automatic query optimization–allows for
query writers to preempt the automated optimizationquery writers to preempt the automated optimization Oracle example:Oracle example:
/* */ /* */ clause is a hint to override Oracle’s default query planclause is a hint to override Oracle’s default query plan
36