GK One-Line Question & Answer

The optimizer ensures that cross products (joins with no condition between two tables) are avoided or placed last in the join tree since they produce n times m rows with no filtering - immediately followed by a filter is converted to a join

They are semantically identical in standard SQL - both produce the same result; but INNER JOIN (explicit syntax) is preferred because it clearly separates join conditions from filter conditions making code more readable and maintainable

A join algorithm that sorts both input relations on the join attribute then merges them in a single linear scan; most efficient when: both inputs are already sorted on the join attribute or when sorting is needed anyway (the sort cost is amortized) or when the join result is large

Hash join requires memory to store the entire smaller relation (build side) in a hash table - memory sensitive; merge join requires memory only for sort buffers - can spill to disk more gracefully; but merge join has higher CPU cost from sorting

When joining tables with M:N or 1:N relationships the result set can be much larger than the input tables due to row multiplication - e.g. joining Customers (1000 rows) to Orders (10000 rows) on 1:N relationship produces 10000 result rows not 1000

View materialization: physically compute and store the view result before executing the main query; Query modification: substitute the view definition into the query and process as one combined query - materialization is better for reused views modification is simpler for single use

A view is updatable if: it is based on a single base table (no joins) has no DISTINCT or GROUP BY/HAVING/aggregate functions no subqueries in SELECT list no UNION/INTERSECT/EXCEPT and the WHERE clause allows identification of the base table rows

Triggers defined on views that fire INSTEAD OF the attempted INSERT/UPDATE/DELETE allowing custom logic to translate the DML operation on the view into appropriate operations on the underlying base tables - enabling updates on otherwise non-updatable views

Simple view (single table no aggregates no DISTINCT): typically supports DML (INSERT/UPDATE/DELETE) through the view. Complex view (multiple tables aggregates GROUP BY DISTINCT UNION): generally NOT directly updatable since the DBMS cannot unambiguously translate DML to base tables

The state where a materialized view no longer reflects the current state of its base tables due to subsequent changes; managed by: immediate refresh (on commit) deferred refresh (on demand/scheduled) or fast/incremental refresh (log-based only propagate changes)

When modifying or dropping a base table affects (invalidates or drops) all views and views-on-views that depend on it - requiring careful dependency tracking and potentially cascading updates to multiple view definitions

Views restrict what data a user can see/modify: create view restricted_emp AS SELECT name dept FROM employee WHERE dept=accounting; grant SELECT on restricted_emp to accounting_user. User cannot access salary other departments or the base table directly - view provides a security perimeter

The query optimizer automatically recognizes when a user query can be answered using a pre-computed materialized view (even if the query does not reference the view directly) and rewrites the query to use the materialized view for improved performance

Indexed views have a unique clustered index built on top of the stored result allowing the optimizer to use the view like a regular indexed table for lookups range scans and sort operations - not just aggregations

It binds the view to the schema of its base tables preventing changes to base tables (DROP column DROP table ALTER type) that would invalidate the view - required for creating indexed views

Real-time views: always current but require resources for each base table change (incremental refresh triggers on every write - adds write overhead); Batch/periodic views: may be stale (hours/days old) but no write overhead refreshed efficiently on schedule

Yes views can reference other views creating nested hierarchies; the query processor must resolve all view definitions recursively before execution which can cause: deep dependency chains difficulty debugging and cascading failures if intermediate views change

Horizontal view: restricts ROWS (WHERE clause on base table) exposing a subset of rows to a user group. Vertical view: restricts COLUMNS (SELECT only certain columns) limiting which attributes are visible - both used for security and interface simplification

CREATE OR REPLACE VIEW atomically replaces an existing view definition without dropping it first preserving dependent object permissions and grants (GRANT/REVOKE on the view survive the replace); DROP+CREATE loses all grants requiring them to be re-applied

Ensuring that a snapshot of data used to compute a materialized view is consistent (taken at one point in time) so that the view does not contain data from different points in time - achieved through snapshot isolation or table locking during refresh

Multiple restrictions including: SCHEMABINDING required no non-deterministic functions no SELECT * no outer joins no subqueries no DISTINCT (in some cases) and base tables must be referenced with 2-part names

When a view is defined over a partitioned table queries through the view can benefit from partition pruning - the optimizer eliminates irrelevant partitions based on query predicates even when querying through the view abstraction

An optimization where Oracle replaces a reference to a view in a query with the underlying view definition (merges view into the main query) allowing the optimizer to optimize the combined query holistically and apply transformations like predicate pushdown into the view

Functions that accept parameters and return a table result set enabling parameterized view-like behavior that regular views cannot support (regular views cannot accept parameters) - e.g. get_orders_for_customer(cust_id INT) RETURNS TABLE

It explicitly prevents DML operations (INSERT UPDATE DELETE) on the view even if the view would otherwise be updatable according to the rules - used to enforce that a view is intentionally read-only and prevent accidental data modifications through the view

A view is a named stored query that presents data from one or more base tables (possibly with transformations filtering or joins); a synonym is simply an alias for another object (table view stored procedure sequence) without any data transformation capability

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