Database Design and Modeling

6.

Physical Database Design

6.1.

Physical Design Objectives

6.1.1.

Performance Optimization

6.1.2.

Storage Efficiency

6.1.3.

Access Path Selection

6.1.4.

Resource Utilization

6.2.

Storage Structures

6.2.1.

File Organization Methods

6.2.1.1.

6.2.1.1.1.

Unordered Storage

6.2.1.1.2.

Simple Implementation

6.2.1.1.3.

Sequential Access

6.2.1.2.

Sequential Files

6.2.1.2.1.

Ordered Storage

6.2.1.2.2.

6.2.1.2.3.

6.2.1.3.

6.2.1.3.1.

6.2.1.3.2.

Multiple Access Paths

6.2.1.4.

6.2.1.4.1.

Key-Based Access

6.2.1.4.2.

Uniform Distribution

6.2.1.4.3.

6.2.2.

6.2.2.1.

Fixed-Length Records

6.2.2.2.

Variable-Length Records

6.2.2.3.

Record Clustering

6.2.3.

Block and Page Management

6.2.3.1.

Buffer Management

6.2.3.2.

Page Replacement Policies

6.2.3.3.

Block Size Considerations

6.3.

Indexing Strategies

6.3.1.

Index Fundamentals

6.3.1.1.

Purpose and Benefits

6.3.1.2.

Access Path Optimization

6.3.1.3.

Query Performance Impact

6.3.2.

6.3.2.1.

Primary Indexes

6.3.2.1.1.

Clustered Organization

6.3.2.1.2.

Physical Ordering

6.3.2.2.

Secondary Indexes

6.3.2.2.1.

Non-Clustered Organization

6.3.2.2.2.

Logical Ordering

6.3.2.3.

Dense vs Sparse Indexes

6.3.2.3.1.

Coverage Differences

6.3.2.3.2.

Storage Trade-offs

6.3.3.

Index Structures

6.3.3.1.

6.3.3.1.1.

Balanced Tree Structure

6.3.3.1.2.

Range Query Support

6.3.3.1.3.

Maintenance Operations

6.3.3.2.

B+ Tree Indexes

6.3.3.2.1.

Leaf Node Linking

6.3.3.2.2.

Sequential Access

6.3.3.2.3.

Most Common Type

6.3.3.3.

6.3.3.3.1.

Equality Searches

6.3.3.3.2.

6.3.3.3.3.

Limited Range Support

6.3.3.4.

6.3.3.4.1.

Low Cardinality Data

6.3.3.4.2.

Boolean Operations

6.3.3.4.3.

Data Warehouse Use

6.3.4.

Multi-Column Indexes

6.3.4.1.

Composite Indexes

6.3.4.2.

Column Order Importance

6.3.4.3.

Partial Index Usage

6.3.5.

Index Selection Guidelines

6.3.5.1.

Query Pattern Analysis

6.3.5.2.

Selectivity Considerations

6.3.5.3.

Maintenance Overhead

6.3.5.4.

Storage Requirements

6.4.

Data Types and Storage

6.4.1.

Numeric Data Types

6.4.1.1.

6.4.1.1.1.

6.4.1.1.2.

6.4.1.1.3.

6.4.1.1.4.

6.4.1.2.

Floating Point Types

6.4.1.2.1.

6.4.1.2.2.

6.4.1.2.3.

Precision Considerations

6.4.1.3.

6.4.1.3.1.

6.4.1.3.2.

6.4.1.3.3.

Fixed-Point Arithmetic

6.4.2.

Character Data Types

6.4.2.1.

Fixed-Length Strings

6.4.2.1.1.

6.4.2.1.2.

Storage Padding

6.4.2.2.

Variable-Length Strings

6.4.2.2.1.

6.4.2.2.2.

Storage Efficiency

6.4.2.3.

6.4.2.3.1.

6.4.2.3.2.

6.4.2.3.3.

Large String Storage

6.4.2.4.

Unicode Support

6.4.2.4.1.

Character Set Considerations

6.4.2.4.2.

Collation Rules

6.4.3.

Date and Time Types

6.4.3.1.

6.4.3.2.

6.4.3.3.

6.4.3.4.

6.4.3.5.

Time Zone Handling

6.4.4.

Binary Data Types

6.4.4.1.

6.4.4.2.

6.4.4.3.

6.4.4.4.

Large Object Storage

6.4.5.

Specialized Types

6.4.5.1.

6.4.5.2.

6.4.5.3.

6.4.5.4.

6.5.

Table Partitioning

6.5.1.

Partitioning Benefits

6.5.1.1.

Performance Improvement

6.5.1.2.

Maintenance Efficiency

6.5.1.3.

Parallel Processing

6.5.2.

Partitioning Methods

6.5.2.1.

Range Partitioning

6.5.2.1.1.

6.5.2.1.2.

6.5.2.1.3.

Partition Pruning

6.5.2.2.

List Partitioning

6.5.2.2.1.

Discrete Values

6.5.2.2.2.

6.5.2.3.

Hash Partitioning

6.5.2.3.1.

Even Distribution

6.5.2.3.2.

6.5.2.4.

Composite Partitioning

6.5.2.4.1.

Multiple Methods

6.5.2.4.2.

Sub-Partitioning

6.5.3.

Partition Management

6.5.3.1.

Adding Partitions

6.5.3.2.

Dropping Partitions

6.5.3.3.

Partition Maintenance

6.6.

Views and Materialized Views

6.6.1.

6.6.1.1.

6.6.1.2.

Query Abstraction

6.6.1.3.

Security Benefits

6.6.2.

6.6.2.1.

6.6.2.2.

6.6.2.3.

Updatable Views

6.6.2.4.

Read-Only Views

6.6.3.

Materialized Views

6.6.3.1.

Physical Storage

6.6.3.2.

Query Performance

6.6.3.3.

Refresh Strategies

6.6.3.3.1.

Complete Refresh

6.6.3.3.2.

Incremental Refresh

6.6.3.3.3.

On-Demand Refresh

6.6.3.3.4.

Automatic Refresh

6.6.4.

View Optimization

6.6.4.1.

Query Rewriting

6.6.4.2.

6.6.4.3.

Predicate Pushdown

Previous

5. Normalization Theory and Practice

Go to top

Next

7. Advanced Data Modeling Concepts