Here is the details:-
Select * from tableA where columnA = columnB
I would like to create index for this where clause "columnA=columnB" in Oracle database, is it possible to do that?
Hope it help you ^^
Creating a Function-Based Index
Function-based indexes facilitate queries that qualify a value returned by a function or expression. The value of the function or expression is precomputed and stored in the index.
In addition to the prerequisites for creating a conventional index, if the index is based on user-defined functions, then those functions must be marked DETERMINISTIC. Also, you just have the EXECUTE object privilege on any user-defined function(s) used in the function-based index if those functions are owned by another user.
Additionally, to use a function-based index:
The table must be analyzed after the index is created.
The query must be guaranteed not to need any NULL values from the indexed expression, since NULL values are not stored in indexes.
Note: CREATE INDEX stores the timestamp of the most recent function used in the function-based index. This timestamp is updated when the index is validated. When performing tablespace point-in-time recovery of a function-based index, if the timestamp on the most recent function used in the index is newer than the timestamp stored in the index, then the index is marked invalid. You must use the ANALYZE INDEX...VALIDATE STRUCTURE statement to validate this index. To illustrate a function-based index, consider the following statement that defines a function-based index (area_index) defined on the function area(geo):
CREATE INDEX area_index ON rivers (area(geo));
In the following SQL statement, when area(geo) is referenced in the WHERE clause, the optimizer considers using the index area_index.
SELECT id, geo, area(geo), desc
WHERE Area(geo) >5000;
Table owners should have EXECUTE privileges on the functions used in function-based indexes.
Because a function-based index depends upon any function it is using, it can be invalidated when a function changes. If the function is valid, you can use an ALTER INDEX...ENABLE statement to enable a function-based index that has been disabled. The ALTER INDEX...DISABLE statement lets you disable the use of a function-based index. Consider doing this if you are working on the body of the function
Query to create index in Oracle:
CREATE INDEX idx ON test(id)
idx is the name of the index
test is the table name
id is the column name
idxs_indx is the tablespace name
If you want to store all the indexes in a separate tablespace, then tablespace name is required. Else you can just use the query:
CREATE INDEX idx ON test(id);
To view the created indexes, you can use the following query:
select * from all_ind_column where table_name = 'test';
For your question, I believe the answer would be something like this:
create index idx on test(id) where columnA=columnB;
Let me know, if it works.
I think, probably the below links helps to achieve what you want
Does Oracle have a filtered index concept?