B-Tree Indexes
| Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences |
|---|---|---|---|
|
|
|
N/A |
N/A |
Oracle Usage
B-Tree indexes (B stands for balanced), are the most common index type in a relational database and are used for a variety of common query performance enhancing tasks. You can define B-Tree indexes as an ordered list of values divided into ranges. They provide superior performance by associating a key with a row or range of rows.
B-Tree indexes contain two types of blocks: branch blocks for searching and leaf blocks for storing values. The branch blocks also contain the root branch, which points to lower-level index blocks in the B-Tree index structure.
B-Tree indexes are useful for primary keys and other high-cardinality columns. They provide excellent data access performance for a variety of query patterns such as exact match searches and range searches. B-Tree indexes are the default when you create a new index.
Examples
Create a B-Tree index.
CREATE INDEX IDX_EVENT_ID ON SYSTEM_LOG(EVENT_ID);
For more information, see CREATE INDEX
PostgreSQL Usage
When you create an index in PostgreSQL, a B-Tree index is created by default. This behavior is similar to the behavior in the Oracle Database. PostgreSQL B-Tree indexes have the same characteristics as Oracle and these types of indexes can handle equality and range queries on data. The PostgreSQL optimizer considers using B-Tree indexes especially when using one or more of the following operators in queries: >, >=, <, ⇐, =.
In addition, you can achieve performance improvement when using IN, BETWEEN, IS NULL or IS NOT NULL.
Examples
Create a PostgreSQL B-Tree Index.
CREATE INDEX IDX_EVENT_ID ON SYSTEM_LOG(EVENT_ID); OR CREATE INDEX IDX_EVENT_ID1 ON SYSTEM_LOG USING BTREE (EVENT_ID);
For more information, see CREATE INDEX
