The query plan can be used to check if an index is used in SELECT queries

[ayshakhatun3113]

Despite its potential pitfalls, the B-tree index remains an indispensable tool for optimizing database queries and increasing performance. To create a B-tree index in Oracle, the following code must be executed:CREATE INDEX index_name ON table_name(attribute_name);

It is also possible to create indexes using multiple attributes of a table. However, it should be noted that the order of the specified attributes is important. Performance optimization can only be achieved through the defined index if the specified attributes are also used in the filter operations (WHERE) of the SELECT statement. If this is not the case and only some of the attributes are filtered in the WHERE statement, the query optimizer performs a full table scan or possibly uses other, smaller indexes. To create an index from multiple attributes in Oracle, the following code is used:CREATE INDEX index_name ON table_name(attribute_name_1, attribute_name_2);

Best Practices
When using B-tree indexes, there are several aspects to consider to avoid undesirable reductions in query speed. The most important rules and best practices for using B-tree indexes are summarized below:

Primary key and foreign key: To improve the performance of bc data queries with one or more joins on tables, it is recommended to create indexes for primary and foreign keys. This means that joins can be executed more quickly and a full table scan is not required for large tables.
High cardinality: The attributes selected for the index should have high cardinality, that is, contain many unique values. For example, the date of birth attribute has high cardinality, and the gender attribute has very low cardinality. If you create a B-tree index with low cardinality, the Query Optimizer will probably not use this index. Other types of indexes, such as the bitmap index, are more suitable here.
Correct operators: In order to use an index in a query, the correct comparison operators must be used in the WHERE condition. These are, in particular, equality operators (greater than, less than, equal to) and range operators (BETWEEN). Negation operators such as NOT or "!=" prevent the use of an index.
Small query sets: An index is only used by the Query Optimizer if the result represents a small subset of the total data volume. It is ideal if the selected data represents less than 1% of the total available data volume. Between one percent and ten percent, the index can still be used; for more than ten percent, a full table scan may be faster.
Use case: To create optimal indexes, it is useful to know the expected SQL queries and tailor the indexes accordingly.
Multiple attributes: For complex queries and joins, it can be useful to create an index from multiple attributes. Attributes with high cardinality should be placed first to allow for faster traversal of the index tree.
Number of indexes: You should avoid creating too many indexes, as this consumes memory and can slow down SQL operations such as INSERT, UPDATE, and DELETE.
Query Plan: This shows whether a full table scan or an index is used for the attributes to be filtered.