GK One-Line Question & Answer

DROP TABLE fails with an error if the specified table does not exist; DROP TABLE IF EXISTS silently succeeds (no error) if the table does not exist - useful in scripts that should be idempotent (safe to run multiple times) like migration scripts or setup scripts that may run on databases where the table may or may not already exist

A column whose value is automatically derived from an expression involving other columns in the same row; restrictions include: expression must be deterministic (no RAND() or NOW()), cannot reference other generated columns, cannot reference other tables, STORED generated columns cannot be updated directly by users

INSERT IGNORE: on unique key violation, silently skips the insert and keeps the original row unchanged (no error); INSERT OR REPLACE (REPLACE INTO): on unique key violation, deletes the conflicting row then inserts the new one (old row is gone, new auto-increment value generated, ON DELETE triggers fire)

Row-level locking: only the rows being modified are locked allowing other transactions to modify different rows concurrently (high concurrency, higher overhead). Table-level locking: the entire table is locked during DML (simple, low overhead, but blocks all other DML on the table); appropriate for bulk operations

In a nested loop join the driving table (outer loop) is the table whose rows are iterated one by one with the inner table looked up for each outer row; the optimizer chooses the smaller or more filtered table as the driving table to minimize iterations; an index on the inner table's join column is critical

Moving filter conditions (WHERE predicates) to be applied as early as possible in the execution plan - ideally directly on the base table scan before the join - so that fewer rows participate in the join operation; this is one of the most impactful query optimizer transformations and can reduce join input size by orders of magnitude

When the build-side relation is too large to fit in the allocated hash join memory, the DBMS performs a grace hash join: partitions both relations to disk using the hash function (ensuring matching rows land in same partition files), then processes each partition pair independently in memory; this increases I/O but prevents out-of-memory errors

The process of maintaining records of which views depend on which base tables (and which views depend on other views), enabling DBAs to identify the impact of schema changes before making them, determine the correct order to drop/recreate objects, and automatically invalidate dependent objects when base schemas change

Deep view nesting adds query parsing and optimization overhead; the query optimizer must recursively expand all view definitions before planning; deep nesting can prevent some optimizer transformations (predicate pushdown, join reordering) if intermediate view definitions use features that block merging such as aggregates, DISTINCT, or LIMIT

The query optimizer estimates how many rows an index access will return using column statistics (histogram, distinct value count, null fraction); stale statistics cause the optimizer to generate suboptimal plans such as choosing a full table scan when a highly selective index exists or choosing an index scan when most rows match and a full scan would be faster

Index range scan: uses the B-tree to navigate to the start of a range and reads sequentially until the range end; efficient for selective range conditions. Full index scan: reads all leaf pages of the index from start to end; used when ORDER BY matches index, covering index answers query without heap access, or for small tables

A compensation transaction is a semantically inverse operation that undoes the effects of a previously committed transaction when a later step in a distributed saga or workflow fails; unlike physical undo (only possible for uncommitted transactions), compensation is a new positive transaction that reverses the business effect such as issuing a refund to compensate for a completed charge

The lost update problem: T1 reads value X=100, T2 reads value X=100, T1 writes X+=50 (X=150), T2 writes X+=30 (X=130 based on its stale read) - T1 update is lost. Prevented by: REPEATABLE READ (read locks prevent T2 from reading until T1 commits), SERIALIZABLE, or optimistic locking (T2 update fails because X changed since T2 read it)

Lock contention occurs when multiple transactions compete for the same locks causing waiting and reducing throughput; strategies to reduce it: shorter transactions, access patterns that minimize lock scope, optimistic locking, MVCC (readers never block writers), partitioning hot data across shards, and batch versus row-level updates

An extension to SELECT FOR UPDATE that skips (does not return or wait for) rows that are already locked by other transactions; enables non-blocking queue-like processing where multiple workers can each claim and process different rows without contention - perfect for job queues, task processing systems, and any pattern where multiple workers process items from a shared pool

Deadlock probability increases approximately as O(n^2) where n is the number of locks held per transaction: each transaction holding n locks has n potential conflicts with each other transaction; as transaction size (locks held) grows the probability of circular wait increases because each lock held is a potential blocker and each lock needed is a potential waiter

PL/SQL NATIVE: compiles PL/SQL code to native machine code via an external C compiler; eliminates interpreter overhead; best for CPU-intensive computations and complex algorithms. INTERPRETED: PL/SQL bytecode executed by the PL/SQL VM; faster compilation, simpler deployment; best for I/O-bound code (most DB code waits on SQL, not PL/SQL computation)

PL/SQL profiling measures execution time and call counts for each line/procedure in PL/SQL code; Oracle provides DBMS_PROFILER (line-level timing), DBMS_HPROF (hierarchical profiler showing call trees and cumulative times), and PL/SQL Developer/SQL Developer GUI tools that visualize profiling data; essential for identifying slow procedures and optimization targets

The PL/SQL RESULT_CACHE stores function results in the SGA (System Global Area) shared across all sessions; Oracle automatically invalidates cached results when any dependent database table or view is modified (DML commit), ensuring cache consistency without any manual intervention required

Mismatch between data structures of programming languages and the relational model

Conceptual schema

Concurrency

Data warehouses are optimized for analytical read-heavy workloads; DBMS for transactional write-heavy workloads

System catalog / Metadata repository

Shielding users from physical storage complexity while maintaining logical clarity

DDL defines schema structure; DML retrieves and manipulates data

Ability to change internal/physical schema without changing the conceptual schema

Database Administrator (DBA)

View/External level

Data redundancy and inconsistency

Any data not stored is assumed to be false/non-existent

Ensuring accuracy, consistency, and validity of data throughout its lifecycle

The overall design or logical structure of the database

The actual data stored in the database at a particular moment in time

Query Optimizer

The ripple effect of changes in one schema level propagating to another

It can automatically trigger actions via triggers in response to database events

A table

NULL represents an unknown, missing, or inapplicable value

Data independence between different levels of database abstraction