GK One-Line Question & Answer

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

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