
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 level for queries
        - readers of data can block writers of data (at default locking level)
        - writers of data can block readers of data (at default locking level)
        - can use different types of locks on index and data pages in one query
        - 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
                - merge join
                - semi-join for star queries
                - list any others:
        - indicies
                - B-tree
                - clustered (data pages ordered by primary key value)
                - allow secondary indices on a clustered index table
                - bitmap
                - hash
                - list any others:
                - can scan indices in reverse
- optimizer
        - cost-based
        - rule-based
        - can use multiple indices in a single query
        - can use just the 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 plans can 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 or only a shared network connection
                - primary and standby servers can run different operating systems
                - failover server can be actively used (active/active configuration)
                - client connection libraries support automatic failover to standby server
        - ability to guarantee service (QoS)
                - query resource consumption 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)
        - allow use 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 


Mon, 31 Jan 2000 17:36:00 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
PCLabs Survey, Part II: Server Engine