NCM-DB flashcards

32 cards. Read the term and the definition here. The deck at the top of this page is optional.

Logical processing order

SQL clauses are evaluated as FROM and joins, WHERE, GROUP BY, HAVING, window functions, SELECT list, DISTINCT, ORDER BY, then LIMIT. This order explains why SELECT aliases are unavailable in WHERE.

Third normal form

A table is in 3NF when it is in 2NF and no non-key column depends transitively on the primary key. Each non-key attribute must depend only on the key, not on another non-key attribute.

Primary key versus unique constraint

A primary key enforces uniqueness and NOT NULL and is limited to one per table. A unique constraint enforces uniqueness but permits one NULL unless the column is also declared NOT NULL.

Range versus hash partitioning

Range partitioning assigns rows to partitions by value intervals, enabling partition pruning for date predicates. Hash partitioning distributes rows by a hash function, spreading write contention but preventing range-based pruning.

Read committed isolation

Each statement in a transaction sees a snapshot taken at statement start, so repeated reads within one transaction may return different rows. Non-repeatable reads and phantoms are permitted at this level.

Deferrable foreign key

A constraint declared deferrable postpones validation until transaction commit rather than each statement. This allows temporary ordering violations during bulk loads but rejects the transaction if unresolved at commit.

Configuration precedence order

Effective settings resolve command line first, then environment variables, then the instance configuration file, then compiled defaults. Always confirm the running value from the instance rather than reading the file on disk.

Static versus dynamic parameters

Dynamic parameters take effect immediately for new or existing sessions without downtime. Static parameters require an instance restart, so changing them must be scheduled inside a maintenance window.

Graceful versus immediate shutdown

Graceful shutdown stops accepting connections, drains active sessions, checkpoints, and flushes dirty blocks. Immediate shutdown skips draining and flushing, forcing crash recovery from redo on the next startup.

Instance identity

An instance is identified by its data directory combined with its listening port. Running two instances on one host therefore requires distinct data directories and distinct ports.

Memory allocation rule

Allocate the buffer pool first from total RAM, then reserve headroom for per-connection work memory and OS cache. Cap maximum connections so worst-case work memory still fits in available RAM.

Storage hierarchy

A tablespace contains segments, a segment contains extents, an extent contains contiguous blocks, and a block is the unit of I/O and caching. Row migration occurs when an update outgrows block free space.

Locally managed tablespace

Free extents are tracked in a bitmap stored inside the tablespace itself rather than in the data dictionary. This removes catalog contention on allocation and is the default for new tablespaces.

Bitmap index use case

Bitmap indexes suit low-cardinality columns in read-mostly or reporting workloads, where they compress well and combine efficiently. They perform poorly on high-concurrency update paths because of row-level locking granularity.

Composite index leading column

A composite index on columns A, B, and C can serve predicates on A, or A and B, or A, B, and C. A query filtering only on B or C cannot use the index efficiently.

PCTFREE setting

PCTFREE reserves a percentage of each block for future row growth during updates. A high value reduces row migration in frequently updated tables but lowers scan density for full-table reads.

NULL comparison semantics

Comparisons involving NULL evaluate to UNKNOWN rather than true or false, so IS NULL and IS NOT NULL are required. NOT IN against a list containing NULL returns no rows at all.

LEFT JOIN filter trap

A predicate on the right table placed in the WHERE clause removes NULL-extended rows, silently converting a LEFT JOIN into an INNER JOIN. Move the predicate into the ON clause to preserve outer rows.

Correlated subquery cost

A correlated subquery re-executes for each candidate outer row, multiplying scan cost. Rewriting it as a join or a window function usually eliminates the repeated access path.

Window function evaluation

Window functions compute aggregates across a partition without collapsing rows into groups. They run after WHERE and GROUP BY but before ORDER BY and LIMIT, so they cannot filter rows.

RPO versus RTO

Recovery point objective defines the maximum acceptable data loss, driving backup frequency and log shipping. Recovery time objective defines the maximum acceptable downtime, driving restore parallelism and standby readiness.

ARCHIVELOG mode

Archived redo logs preserve every change after a backup, allowing recovery to the last committed transaction after media failure. Without them, recovery is limited to the most recent consistent backup.

Point-in-time recovery

Restore a base backup, roll forward archived and online redo logs to a target timestamp, then open the database. The target must fall within the retained log range or recovery cannot complete.

Failover versus switchover

Failover promotes a standby after an unplanned primary failure and may lose unsynchronized data. Switchover is a planned, reversible role exchange that preserves all committed data.

Backup retention chain

Retention must keep one full backup plus every log required to reach it. Deleting archived logs earlier than the oldest retained full backup breaks the recovery chain permanently.

Buffer pool hit ratio

The ratio equals one minus physical reads divided by logical reads. A low value indicates either insufficient cache or a scan-heavy workload, so confirm the cause before adding memory.

Wait event analysis

A wait event names the resource a session is blocked on, such as I/O or a lock. CPU consumption is not a wait, so high CPU with low waits points to inefficient execution.

Estimated versus actual rows

Comparing estimated to actual row counts per plan operator reveals optimizer misestimation. The operator with the largest discrepancy usually explains a poor join order or a wrong access path.

Root blocker identification

Blocking-session chains show which session holds the lock and which sessions merely wait. Resolving or terminating the root blocker clears the chain; killing waiters alone leaves contention intact.

Least privilege with roles

Grant privileges to roles and assign roles to users rather than granting object privileges directly. A user then holds only the rights needed for the current task and loses them on role revocation.

Privilege granted to PUBLIC

PUBLIC is a default role held by every account, so any privilege granted to it is effectively granted to all users. Such grants must be inventoried and audited explicitly.

Fine-grained auditing

Standard auditing records statement or privilege use, while fine-grained auditing captures row-level access using predicates and conditions. Audit records must reside where audited users cannot alter them.