Server Engine
- units of execution (granularity of multi-tasking engine)
- computes statistics while building index
- can run operations without logging
- can undo database structure modification (Data Definition Layer) commands
- can undo committed transactions if required
- locking - levels of locking granulatity available (options are row, page, table,etc.) - default locking
levelfor queries - readers of data can block writers of data (at default locking level) - writers of data
canblock readers of data (at default locking level) - can use different types of locks on index and data pages in
onequery - can escalate many fine-grained locks to one coarser lock
- can use multiple CPUs - within a query (intra-query parallelism) - on database load - on database
index - on database backup and restore - on insert ... select ... - on update - on delete
- join and index selection - joins - nested loop join - hash join -
mergejoin - semi-join for star queries - list any others: - indicies -
B-tree - clustered (data pages ordered by primary key value) - allow secondary indices on a
clusteredindex table - bitmap - hash - list any others: - can
scanindices in reverse
- optimizer - cost-based - rule-based - can use multiple indices in a single query - can use
justthe index to satisfy the query (covered indices) - automatically maintains necessary optimizer statistics
- cache and memory management - dynamic SQL statements parameterized and cached for later use - execution
planscan be compiled once and then shared among all users - can define dedicated caches for particular tables or
tablespaces - can mark tables as memory-resident - dynamic memory resource sharing (e.g. cache can grow or
shrink)
- scalability - number of CPUs supported per server - multi-machine clustering for performance
- clustered servers require shared disk subsystem - clustered machines can run different operating
systems
- availability - failover server option - primary and standby servers require shared disk subsystem
oronly a shared network connection - primary and standby servers can run different operating systems
- failover server can be actively used (active/active configuration) - client connection
librariessupport automatic failover to standby server - ability to guarantee service (QoS) - query
resourceconsumption governor - can assign priorities to different groups of users
- backup and restore - restore to a particular point-in-time (partial restore) - incremental backup
- recovery - can control frequency of checkpointing by number of operations or estimated recovery time (list
which)
- storage engine - default disk data block size - users can choose disk block size (list options) -
allowuse of unformatted disks for extra speed - parallel disk operations - automatic disk read-ahead
-multi-task (asynchronous) disk operations - data and log devices can grow when needed
- Year 2000 certified
- Euro currency support
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org