GK One-Line Question & Answer

Standard SQL supports CREATE RECURSIVE VIEW but most DBMS implement recursion through recursive CTEs (WITH RECURSIVE) used in view definitions; limitation: recursive depth may be bounded cyclic data can cause infinite loops requiring cycle detection

Security definer views execute with the privileges of the view owner/creator (allowing users to access data they would not normally have access to through the view); security invoker views execute with the privileges of the querying user (the view acts as a transparent shortcut)

A standard SQL catalog view that exposes metadata about all views in the database including: view name schema owner view definition (the SQL query) whether it is updatable whether it supports CHECK OPTION and various other properties

In some DBMS (Oracle) a join view can be DML-enabled if: only one of the joined tables has its key preserved (key-preserved table) and all DML operations affect only the key-preserved table - the DBMS knows which base table to modify because there is an unambiguous 1:1 mapping

A view is a persistent named query stored in the database catalog accessible to all users across multiple sessions; a CTE (WITH clause) is a temporary named result set that exists only for the duration of a single SQL statement - CTEs are not stored and are not reusable across statements

An index that contains all the columns needed to satisfy a query - when the query engine can find all required data directly in the index leaves without accessing the base table (heap lookup) dramatically improving query performance

An index type where each distinct value has a bit vector (one bit per table row) indicating which rows have that value - highly efficient for low-cardinality columns in read-heavy OLAP environments but expensive to maintain in concurrent write environments

The ratio of distinct values to total rows (cardinality/total_rows); high selectivity (close to 1 like unique ID) means the index filters effectively; low selectivity (close to 0 like boolean) makes the index inefficient - optimizer may choose full scan over low-selectivity index access

A balanced tree where internal nodes store keys for routing and ALL data pointers are in leaf nodes with leaf nodes linked in a doubly-linked list - enabling efficient: point queries (O(log n)) range scans (traverse linked leaves) insertions and deletions while maintaining balance

An optimization where the query optimizer uses a composite index on (A B) to answer a query filtering only on B (not A) by logically splitting the scan into one sub-scan per distinct value of A - useful when A has low cardinality

When the optimizer uses multiple separate indexes on a single table to satisfy a query and combines their results using intersection (AND) or union (OR) avoiding a full table scan when individual indexes are insufficient

A composite index on (A B C) can only be used efficiently when the query filters include the leftmost prefix of columns (A or A+B or A+B+C) but NOT when filtering on B or C alone without A - the optimizer cannot use the index without the leading column

An index that includes only rows satisfying a specified condition (WHERE clause) creating a smaller more efficient index for queries with that condition - e.g. CREATE INDEX ON orders(customer_id) WHERE status=pending

Indexes accumulate dead tuples from UPDATE/DELETE operations (MVCC keeps old versions) causing the index size to grow beyond necessary degrading performance - managed by VACUUM (cleans dead tuples) REINDEX or CREATE INDEX CONCURRENTLY as replacement

An index using a hash function to map key values to bucket positions - supports only equality lookups (O(1) average) but NOT range queries or ordering; superior for equality-only workloads with no range/ordering requirements

A table where the entire row data is stored within the index structure (B-tree leaf nodes) rather than in a separate heap file - eliminates the double storage of data (heap + index) provides fast primary key access but slower for full table scans and secondary index lookups

Each INSERT/UPDATE/DELETE on a table must also update ALL indexes on that table multiplying the write I/O: a table with 5 indexes requires writing to 6 places (heap + 5 indexes) per row change - significantly reduces write throughput in insert-heavy OLTP systems

A query execution method where all required data is found directly in the index pages without accessing the table heap - requires that all columns referenced in the query (SELECT WHERE GROUP BY ORDER BY) are included in the index (covering index)

The percentage of each index page that is filled with data at index creation time; setting below 100% (e.g. 80%) leaves free space in each page for future insertions reducing page splits - beneficial for frequently updated indexes at the cost of larger initial index size

An index built on the RESULT of an expression/function applied to one or more columns allowing efficient queries that filter/sort on computed values - e.g. CREATE INDEX ON users(LOWER(email)) enables case-insensitive email lookups to use the index

When a B-tree leaf page is full and a new key must be inserted the page splits into two pages (50/50 distribution) requiring: allocating new page redistributing keys updating parent pointer possibly cascading parent splits - causes write I/O overhead and index fragmentation

An index that the optimizer ignores (as if it does not exist) but is still maintained by DML operations - used to safely test the impact of dropping an index without actually removing it or to prepare a new index for testing before making it active

An index where the bytes of the key are reversed before storage in the B-tree distributing sequential key inserts (like auto-increment IDs) across all index leaf blocks instead of always inserting into the rightmost block - reduces right-hand-side contention in OLTP systems

Statistical information about index column value distribution (cardinality histograms null count average row size) that the query optimizer uses to estimate result sizes and costs for different execution plan choices - stale or inaccurate statistics lead to poor plan choices

Index seek: uses the B-tree to navigate directly to the matching key value(s) - O(log n) + matching rows only. Index scan: reads all (or most) leaf pages of the index sequentially - O(index_size). Seek is much faster for selective queries; scan used when large fraction of data is returned or index cannot be used for point lookup

Building an index without holding a table lock (allows concurrent reads AND writes during index build) - trade-offs: takes longer than normal index creation (multiple table scans needed) uses more CPU/I/O cannot be done in a transaction and may fail requiring manual REINDEX

An extensible index framework that supports a wide variety of data types and query operators beyond equality/comparison: geometric objects text search IP ranges JSON paths custom types - by allowing user-defined key types and split/union/penalty functions

A directive added to a SQL query that forces or suggests the query optimizer to use a specific index instead of choosing one automatically - should be used sparingly when the optimizer consistently makes poor choices due to inaccurate statistics or atypical query patterns

An automatically built in-memory hash index that InnoDB creates on top of frequently accessed B-tree index pages - when InnoDB detects that the same index lookups are performed repeatedly it builds a hash index for those values enabling O(1) lookup vs O(log n) B-tree traversal

A technique that reduces the storage size of index entries by: prefix compression (storing only the suffix that differs from the previous key for sorted keys) dictionary compression (replacing repeated values with integer codes) or key compression - smaller index means more pages fit in buffer pool improving cache efficiency and reducing I/O

A primary index is built on the primary key and determines the physical order of data (clustered); secondary indexes are built on non-primary key columns and do not determine physical data order (non-clustered in most DBMS) - secondary indexes reference the primary key to locate actual rows

A special index type that stores data by column rather than by row enabling high compression and vectorized batch processing - primarily used for OLAP/analytical workloads where queries aggregate over large datasets and touch only a few columns

The cost of updating indexes on every INSERT/UPDATE/DELETE which includes: additional I/O for each index page write, page splits causing fragmentation, and potential index bloat - managed by: choosing indexes carefully (only create indexes that pay off) rebuilding fragmented indexes (ALTER INDEX REBUILD) and reorganizing (ALTER INDEX REORGANIZE) on a scheduled basis

All operations in a transaction either ALL complete successfully (COMMIT) or ALL are undone (ROLLBACK) - implemented via the undo log: before each change the original value is written to the undo log enabling complete rollback if the transaction fails or is explicitly rolled back

Consistency means the transaction takes the database from one valid state to another - the DBMS enforces defined constraints (PK FK CHECK NOT NULL) but the APPLICATION is responsible for ensuring business-level constraints are upheld (the DB only enforces what it knows about)

Isolation ensures concurrent transactions execute as if they were serial; stronger isolation (SERIALIZABLE) prevents more anomalies but requires more locking/MVCC overhead reducing concurrency and throughput. Weaker isolation (READ COMMITTED) allows more anomalies but permits higher concurrency

Once a transaction is committed its changes persist even through system crashes; WAL implements this by writing log records to disk BEFORE applying changes to the database ensuring that committed changes can be replayed during recovery even if the data pages were not yet flushed

Reading uncommitted changes of another concurrent transaction - if that other transaction later rolls back the read data never existed. Only READ UNCOMMITTED isolation level allows dirty reads; all higher levels prevent them

Within one transaction reading the same row twice returns different values because another committed transaction modified that row between the two reads; prevented by REPEATABLE READ and SERIALIZABLE isolation levels

Within one transaction executing the same range query twice returns different sets of rows because another committed transaction inserted/deleted rows in the query range; SERIALIZABLE adds range locking (predicate locks) or snapshot validation beyond REPEATABLE READ which only locks existing rows