Function-Based Indexes
You can create indexes on functions and expressions that involve one or more columns in the table being indexed. A function-based index computes the value of a function or expression involving
one or more columns and stores it in the index. A function-based index can be either a B-tree or a bitmap index.
The function used for building the index can be an arithmetic expression or an expression that contains a SQL function, user-defined PL/SQL function, package function, or C callout. For example,
a function could add the values in two columns.
Uses of Function-Based Indexes
Function-based indexes are efficient for evaluating statements that contain functions in their WHERE clauses. The database only uses the function-based index when the function is included
in a query. When the database processes INSERT and UPDATE statements, however, it must still evaluate the function to process the statement.
For example, suppose you create the following function-based index:
CREATE INDEX emp_total_sal_idx
ON employees (12 * salary * commission_pct, salary, commission_pct);
The database can use the preceding index when processing queries such as Example 3–6 (partial sample output included).
SELECT employee_id, last_name, first_name,
12*salary*commission_pct AS "ANNUAL SAL"
FROM employees
WHERE (12 * salary * commission_pct) < 30000
ORDER BY "ANNUAL SAL" DESC;
Function-based indexes defined on the SQL functions UPPER(column_name) or LOWER(column_name) facilitate case-insensitive searches. For example, suppose that the first_name column in employees
contains mixed-case characters. You create the following function-based index on the hr.employees table:
CREATE INDEX emp_fname_uppercase_idx
ON employees ( UPPER(first_name) );
The emp_fname_uppercase_idx index can facilitate queries such as the following
SELECT *
FROM employees
WHERE UPPER(first_name) = 'AUDREY';
A function-based index is also useful for indexing only specific rows in a table. For example, the cust_valid column in the sh.customers table has either I or A as a value. To index only the
A rows, you could write a function that returns a null value for any rows other than the A rows. You could create the index as follows:
CREATE INDEX cust_valid_idx
ON customers ( CASE cust_valid WHEN 'A' THEN 'A' END );
分享到:
相关推荐
idea of function-based indexes—indexes on “data that don’t really exist.” This gets us to the point of understanding what Oracle can do, but we can (and do) go further with what we can do with ...
<1>.creating function-based indexes sql> create index summit.item_quantity on summit.item(quantity-quantity_shipped); <2>.create a B-tree index sql> create [unique] index index_name on table_...
Performance indexes in training of neural networks 2.5. Selected classes of learning methods 2.5.1. Gradient-based optimization of multivariable functions 2.5.2. Perceptron learning rule ...
蒋志强,周炜星,We have performed detailed multifractal analysis on the minutely volatility of two indexes and 1139 stocks in the Chinese stock markets based on the partition function approach....
apply function over image values -gamma value level of gamma correction -gaussian-blur geometry reduce image noise and reduce detail levels -geometry geometry preferred size or location of the ...
Quantile-Based Plots - Continuous Distributions Q-Q Plot Quantile Plots Quantile Plots - Discrete Distributions Poissonness Plot Binomialness Plot Box Plots 5.3 Exploring Bivariate and ...
Quantile-Based Plots - Continuous Distributions Q-Q Plot Quantile Plots Quantile Plots - Discrete Distributions Poissonness Plot Binomialness Plot Box Plots 5.3 Exploring Bivariate and ...
Quantile-Based Plots - Continuous Distributions Q-Q Plot Quantile Plots Quantile Plots - Discrete Distributions Poissonness Plot Binomialness Plot Box Plots 5.3 Exploring Bivariate and ...
Quantile-Based Plots - Continuous Distributions Q-Q Plot Quantile Plots Quantile Plots - Discrete Distributions Poissonness Plot Binomialness Plot Box Plots 5.3 Exploring Bivariate and ...
DDL for indexes on virtual columns would specify the column expression instead of the column name Fixed Session > Set Main Connection menu icon transparency issue Using the Search Next function in a ...
- FIX: The PointOnLine() function calulations have "single" type numbers overflow problem (changed to "double"). - FIX: The pfJoin and pfClose flags incorrectly calculates in GetEditPathCaps(). ...
Describe the different types of indexes and how indexes can be used to improve performance. Describe what statistics are used for and how they can help in optimizing query performance. ...
Because of this fundamental yet hidden function, I always experience a certain sense of awe when thinking about databases, not unlike the awe one might feel when walking across a suspension bridge ...
Table An entire table, including all data and indexes. Extent A contiguous group of data pages or index pages. Page An 8-KB data page or index page. Key Row lock within an index. Key-range A key-...
Even faster Key/Value store nosql embedded database engine utilizing the new MGIndex data structure with MurMur2 Hashing and WAH Bitmap indexes for duplicates. See Also More like this More by this...
* When multiple indexes are included with the data from a provider, the IndexDef entries in the TClientDataset will show duplicate ‘Fields‘ and ‘DescFields‘ values (Quality Central 7543)....