All bites
The whole library, newest first. Filter by what you are here for, or pick a topic if you already know.
4330 bites
Page 213
Joining a large table with a small one
With a tiny table the optimizer often picks a hash join, building a hash table on the small side in memory, then probing it once per row of the large table in a single pass.
Hash join versus sort-merge join
Hash join builds and probes a hash table, great for unsorted equality joins with enough memory; sort-merge sorts both inputs then merges, winning when inputs are already sorted or output must…
Sorting data larger than memory
External merge sort reads memory-sized chunks, sorts each in RAM and writes them as sorted runs to disk, then merges many runs together in passes until one sorted output remains.
How does a hash join handle memory overflow?
The build table is partitioned by hash and spilled to disk, then probe rows are partitioned the same way, and pairs are joined per partition.
How do you choose between relational and NoSQL databases?
Relational gives schema, joins, and ACID for structured related data; document gives flexible schema and horizontal scale for varied or denormalized data.
What is the CAP theorem?
Consistency, Availability, Partition tolerance; during a network partition you must choose between staying consistent or staying available.
Embed or reference likes in a document database?
Embedding is fast for small bounded lists but unbounded likes hit document size limits; referencing scales for high-cardinality, write-heavy likes.
What are eventual consistency and the BASE model?
Eventual consistency means replicas converge given no new writes; BASE is Basically Available, Soft state, Eventually consistent.
Why fit Cassandra to a high-read, high-write workload?
Consistent-hash partitioning spreads load, replication and no single master give availability, log-structured writes are fast, tunable consistency balances per query.
How do you keep consistency without multi-document transactions?
A Saga runs a sequence of local transactions, each with a compensating action to undo on failure, coordinated via choreography or orchestration.
What consistency do you sacrifice in an AP system?
You give up linearizability and often sequential consistency, accepting stale reads and conflicts, then mitigate with quorums, vector clocks or CRDTs, and…
What is the difference between OLTP and OLAP?
OLTP handles many short read-write transactions on normalized current data; OLAP runs few large analytical scans over denormalized historical data.
What is a star schema?
A central fact table of measures and foreign keys surrounded by denormalized dimension tables of descriptive attributes, joined in one hop for fast, simple analytical queries.
What is the difference between ETL and ELT?
ETL transforms before loading, on a separate engine; ELT loads raw then transforms inside a scalable warehouse. Choose ELT with cloud warehouses and large raw or schema-on-read data.
Star schema vs snowflake schema trade-offs?
Star keeps dimensions denormalized for fewer joins and faster simpler queries; snowflake normalizes dimensions into sub-tables saving space and easing maintenance but adding joins.
How does columnar storage speed up analytics?
Columnar stores each column contiguously, so aggregations read only needed columns, scan far less data, and compress better with vectorized execution.
What is a Type 2 slowly changing dimension?
An SCD handles dimension attributes that change over time; Type 2 inserts a new row per change with a surrogate key and validity dates, marking one current.
Why separate storage and compute in a cloud warehouse?
Data lives in cheap shared object storage while independent compute clusters scale separately, enabling elastic, concurrent, isolated workloads and pay-per-use.
What is an OLAP cube and its operations?
A cube pre-aggregates measures across dimensions; operations are slice, dice, drill-down, roll-up, and pivot.
What is database replication and why use it?
Replication keeps copies of data on multiple servers; primary benefits are high availability through failover and improved read scalability by spreading reads.