Foreign Keys

Supported

Joins

Nested Loop Join Hash Join Sort-Merge Join

The query optimizer picks the join order with the longest path and lowest estimated cost in terms of I/O operations and processing, in which a join path is defined as a sequence of joins. The cost of the join method is considered in the computation of the join cost.

Firebird uses nested loop join, sort-merge join, and hash join. The join algorithm is chosen as follows. If the join is an outer join, the join conditions are indexed, or the join conditions are inequalities, the nested loop join is used. If the join is an inner join and the join conditions are both equalities and not indexed, the sort-merge or hash join is used.

Isolation Levels

Read Committed Snapshot Isolation

Firebird supports snapshot isolation, snapshot table stability isolation, and read committed isolation. Snapshot isolation is the default isolation level and allows the transaction to see only changes committed before it started. Snapshot table stability isolation is similar to snapshot isolation but more restrictive. Once this level has started, other transactions cannot modify any tables that have changes pending.

Read committed isolation allows the current transaction to see all data changes by transactions which have committed since the current transaction started. There are two kinds of conflict resolution used in read committed isolation: one that reads the last committed version of a row and thus is non-blocking, and one that makes the transaction unable to modify any row that has pending updates from another transaction.

Views

Virtual Views

Firebird supports three types of virtual views: naturally updatable views, which can be updated automatically without needing to write triggers; updatable views, which are updated through underlying triggers and can accept UPDATE, INSERT, and DELETE statements; and non-updatable views.

Query Interface

SQL

Concurrency Control

Multi-version Concurrency Control (MVCC)

Data Model

Relational

Storage Architecture

Disk-oriented

Logging

Not Supported

Firebird does not include transaction logging facilities, but logging software is available from third-party vendors.

Storage Model

N-ary Storage Model (Row/Record)

Indexes

B+Tree BitMap

Firebird uses B-trees for indexes. Each index has dedicated index pages in the database and contains record locators in the leaf nodes.

When using an index to access a table, Firebird retrieves the necessary index information in one pass and creates a sparse bitmap, in which the true bits correspond to the page address and record offsets of records in the query. Multiple indexes can be used on a table by ANDing and ORing the bitmaps created from the indexes.

System Architecture

Shared-Everything

Compression

Prefix Compression

Firebird uses prefix and suffix compression for index keys.