1/35
Comprehensive vocabulary flashcards covering Database Performance Tuning, Query Optimization, and Distributed Database Management Systems based on Chapters 11 and 12.
Database Performance Tuning
The collection of activities used to make a database management system (DBMS) respond efficiently while using system resources effectively.
Query Optimisation
The DBMS process of evaluating alternative execution strategies and selecting an efficient plan for executing a SQL statement.
Bottleneck
The specific resource or process—such as CPU, memory, disk I/O, network traffic, or locking—that limits overall system performance.
Performance Baseline
A recorded measurement of normal system behaviour under a known workload, against which later metrics are compared to detect deterioration and evaluate tuning changes.
Response Time
A performance metric that measures the total elapsed time from the initial submission of a request to the completed response.
Throughput
A performance metric measuring the number of transactions or requests completed by the system per unit of time.
Database Statistics
Information describing the size and distribution of stored data—such as row counts, page counts, distinct values, histograms, and index depths—used by the optimiser to evaluate cost and selectivity.
Parsing Phase
The query-processing phase that checks SQL syntax and semantics, validates object names and privileges, translates the query into an internal format, and generates an execution plan.
Execution Phase
The query-processing phase that runs the chosen plan by opening tables and indexes, reading data pages, applying filters, performing joins and sorting, and computing results.
Fetching Phase
The query-processing phase that transfers the generated result rows across the network to the requesting client or application, typically in batches.
Query Execution Plan
The specific operational roadmap chosen by the optimiser, detailing access paths, join order, join methods, sorting, and parallelism for a SQL statement.
Access Path
The technique selected by the query optimiser to locate and retrieve rows from a table, such as a full table scan, index scan, or index seek.
Composite Index
An index defined on two or more columns of a table, where the order of columns matters and the leading columns should correspond to common, selective search predicates.
Index Selectivity
A measure of how effectively an index narrows a search, commonly approximated by the ratio total rowsdistinct values.
Sargable Condition
A search argument that is formulated in an index-friendly way, allowing the DBMS to utilize an index directly rather than executing full scans or applying functions to indexed columns.
Distributed Database
A single logical database whose data is physically stored across two or more network-connected sites while presenting as one unified database to users.
Distributed Processing
The division of database-processing work among two or more networked computers, which may operate against a single centralised database or across multiple sites.
Distributed Database Management System (DDBMS)
Software that governs the storage and processing of logically related data across interconnected computer sites, abstracting the complexity of distribution from end users.
Transaction Processor (TP)
The DDBMS component that accepts client requests, validates them, determines required sites, coordinates multi-site execution, and returns final results.
Data Processor (DP)
The DDBMS component that manages local storage, executes local data retrieval and update operations, and coordinates with other sites.
Homogeneous DDBMS
A distributed system that integrates multiple sites using the same or closely compatible DBMS software, data model, operating platform, and query language.
Heterogeneous DDBMS
A distributed system that integrates different DBMS products, data models, query languages, or operating systems through translation layers or middleware.
Distribution Transparency
The feature of a DDBMS that conceals the physical distribution, storage, replication, and transaction mechanics of data, presenting the system as a centralised database.
Remote Request
A distributed operation where a single SQL statement accesses data located at a single remote site.
Remote Transaction
A distributed operation consisting of multiple SQL statements within a single transaction that all access data stored at a single remote site.
Distributed Transaction
A transaction that includes multiple SQL statements accessing data across two or more different remote sites.
Distributed Request
The most complex distributed operation, wherein a single SQL statement accesses data stored across two or more distinct sites.
Two-Phase Commit Protocol (2PC)
A distributed consensus protocol that preserves transaction atomicity across multiple sites via a Prepare/Vote phase followed by an all-or-nothing Decision phase.
Horizontal Fragmentation
A distributed database design method that divides a table horizontally into subsets of rows based on specific conditions or attributes.
Vertical Fragmentation
A distributed database design method that breaks a table into subsets of columns, preserving the primary key in each fragment to enable full reconstruction.
Mixed Fragmentation
A data partitioning approach that combines horizontal and vertical fragmentation by dividing a table by both rows and columns.
Completeness Rule
A fragmentation correctness rule stating that every data item belonging to the original relation must appear in at least one fragment without loss.
Reconstruction Rule
A fragmentation correctness rule stating that it must be possible to rebuild the original table without data loss using relational operations such as joins or unions.
Disjointness Rule
A fragmentation correctness rule requiring that each data item appear in only one fragment, with the exception of primary keys replicated for vertical reconstruction.
Data Allocation
The design process of assigning table fragments and their replicas to specific physical storage sites based on access frequencies, communication costs, and reliability needs.
CAP Theorem
A distributed computing theorem stating that in the presence of a network partition, a system must make a trade-off between Consistency and Availability.