Thunderbolt Indexes

Thunderbolt Indexes

An index is a data base structure that organizes data in a way that facilitates efficient querying.

All of the basic field types can be indexed: X, N, I, ID, ID64, F, FD, DATE, DATETIME, LOGICAL. Only MEMO fields cannot be indexed. Fields up to 256 characters long can be indexed.

There is no limit to the number of indexes in a database.

Indexes are case insensitive by default.

Indexes can be created in batch mode, when a database is first created; and/or, created and modified dynamically as data is added or modified.

An index can be defined for a field that exists in only one table; in this case it is called a local key. Or an index can be defined for a field that exists in multiple tables; in this case it is called a global key.

It is not necessary to index a field in order to sort on that field.

The TB API functions ddlFind() and ddlMatch() use these indexes when they are available. However, they also work on non-indexed fields; but the performance of this mode degrades rapidly as the amount of data increases.

The traditional Thunderbolt indexing strategy (prior to Thunderbolt RAD Version 6.0 and Thunderbolt API 4.0), while suitable for many applications, has become increasingly unsuitable as databases became larger and as performance requirements increased. Thunderbolt API Version 4.0 includes a new type of indexing strategy that supports high insertion and update rates, fast query responses and efficient storage utilization. Refer to the Real-time Indexes document for more information.

Refer to the DataBase Definition document for information on creating an indexed field.

Refer to the Creating Structures document for information on creating and updating structures for an indexed field.

Summary of Index Characterizations:

Primary key

PRIMARY

Case sensitivity

SENSITIVE

Auto increment

IDENTITY [(<seed>,<increment>)]

Node architecture

FIXED [Default]	fixed length node
--------------------------	--------------------------
VARIABLE	variable length node

List architecture

STANDARD [Default]	two level list
--------------------------	--------------------------
REALTIME	list-tree hybrid

File architecture

INTEGRAL [Default]	Multiple indexes per file
--------------------------	--------------------------
DISCRETE	Single index per file

File block size (.ttt)

BUFF4 [Default]	4K block size
--------------------------	--------------------------
BUFF8	8K block size
--------------------------	--------------------------
BUFF16	16K block size
--------------------------	--------------------------
BUFF32	32K block size
--------------------------	--------------------------
BUFF64	64K block size

VARCHAR
trailing blank mode

NOT_SIGNIFICANT [Default]	Trailing Blanks Not Significant
--------------------------	--------------------------
SIGNIFICANT	Trailing Blanks Significant

Data connection

[Default]	Auto based on field
--------------------------	--------------------------
EXTERNAL VIRTUAL	User defined

List data content

[Default] EXTERNAL VIRTUAL	Record pointer
--------------------------	--------------------------
EXTERNAL PURE	32 bit integer