GK One-Line Question & Answer

The optimal sequence in which to join multiple tables; join order is critical because different orders produce dramatically different intermediate result set sizes and the optimizer must find the order minimizing total data processed

ON: allows any join condition including different column names (ON a.dept_id = b.id); USING: shorthand for equality join on same-named columns in both tables (USING(dept_id)) - eliminates duplicate columns in result

A technique to reduce data transfer in distributed joins: instead of shipping all of relation R to the site of relation S first compute the semi-join S semi-join R (much smaller) ship that to R site compute the join there - reduces network traffic

Left-deep: each inner side of a join is a base table (chain structure: ((A join B) join C) join D) - allows pipelining but limited join orders; Bushy: inner sides can be intermediate results ((A join B) join (C join D)) - more join orders can exploit parallelism

The fraction of rows in the cross product that actually match the join condition (result_rows / (|R| times |S|)); low selectivity (few matches) is better; optimizer uses statistics to estimate selectivity and choose between join methods

A join where for each row in the outer table the DBMS uses an index on the inner tables join key to directly look up matching rows - efficient when: outer table is small inner table has an appropriate index on the join column and join selectivity is low

When the query optimizer removes a JOIN from the execution plan because it can prove the join does not affect the result (typically: joining to a unique FK parent table when only the child columns are selected) reducing unnecessary I/O

Each matching combination of rows produces a row in the result - if table A has 3 rows with dept_id=5 and table B has 2 rows with id=5 the join produces 3 times 2=6 rows for dept_id=5 (multiplicative not additive)

An optimization for queries against star schemas: filter dimension tables first (highly selective) then use bitmap indexes to intersect the fact table rows matching ALL dimension filters avoiding full fact table scans

A hash join variant that partitions BOTH relations into buckets based on hash of join key (partitioning phase) then independently hash-joins matching partition pairs that fit in memory (probing phase) - handles relations larger than RAM by avoiding the need to hold everything in memory at once

Low cardinality join columns have poor index selectivity (index not beneficial for equality joins - will return many rows) cause data skew in hash joins (one bucket overloaded) and may require special handling like bitmap indexes or skew-aware hash distribution

Automatically joins on ALL columns with the same name in both tables using equality without requiring explicit ON clause - risky because schema changes (adding a new column with same name in both tables) silently change join semantics

Build side: the SMALLER relation - loaded entirely into hash table in memory. Probe side: the LARGER relation - each row is used to probe the hash table. Optimizer assigns smaller relation as build side to minimize memory requirements

All rows from RIGHT table (with NULLs for unmatched left side) and only matching rows from LEFT table - functionally equivalent to reversing table order and using LEFT OUTER JOIN (which is more readable and universally preferred)

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