Logical Indexes
| Info |
|---|
In a database, a logical index is an index structure that replaces the usual pointers to physical table blocks with the actual primary key values of the rows in the table. In traditional index structures like B-trees or B+ trees, each index entry typically contains a pointer to the corresponding data block where the indexed row resides. However, in a logical index, these pointers are replaced with the primary key values of the rows. |
Logical indexes are always secondary indexes, meaning they are additional indexes created on columns other than the primary key of the table. A primary index is automatically created on the primary key of the table by the database management system (DBMS) itself. So, a logical index is a secondary index that complements the primary index.
...
A logical index is a type of secondary index where pointers to table blocks are replaced with primary key values. It complements the primary index, which is automatically created on the primary key of the table. To locate a row based on a column value using a logical index, the database first uses the logical index to find the associated primary key value and then uses the primary index to locate the actual row in the table.
Functional Indexes
Afunctional index is a type of index that is created on the result of an expression or function applied to one or more columns in a table. Instead of directly indexing the column values themselves, the functional index stores the values resulting from the application of a specified function on the columns. This allows for efficient querying and data retrieval based on the results of the function.
Here's how a functional index works:
Expression or Function: To create a functional index, you define an expression or function that operates on one or more columns in the table. This function can be a simple arithmetic operation, string manipulation, date calculation, or any other valid expression supported by the database system.
Index Creation: Once the expression or function is defined, you create the functional index based on the result of that expression. The index stores the result values and their corresponding row pointers.
Improved Query Performance: The primary advantage of a functional index is that it accelerates query performance when the same expression is used in search conditions. Instead of calculating the expression for every row during query execution, the database can utilize the functional index to quickly locate the rows that satisfy the specified expression.
Usage Scenarios: Functional indexes are particularly useful in situations where queries often involve complex calculations or transformations on columns. They can be beneficial for queries involving aggregation functions, date operations, or even custom-defined functions.
Considerations: While functional indexes can improve query performance in specific scenarios, they also come with some considerations:
Overhead: Creating and maintaining functional indexes can introduce additional overhead during data modifications, as the index must be updated whenever relevant data changes.
Selectivity: The selectivity of the function or expression used in the index should be carefully considered. Highly selective functions lead to smaller index sizes and more efficient index use.
Here's an example of a functional index:
Suppose we have a table named "sales" with columns "quantity" and "price_per_unit." We want to frequently query the total sales amount for each transaction, which can be calculated as "quantity * price_per_unit." To speed up this calculation, we can create a functional index on the expression "quantity * price_per_unit." This index would store the results of this calculation along with the corresponding row pointers, making it efficient to retrieve total sales amounts in queries.
A functional index in databases is an index that is created on the result of an expression or function applied to one or more columns. It improves query performance by allowing the database to use the index to quickly locate rows that satisfy the specified expression, rather than calculating the expression for each row during query execution.