Oracle implements function-based indexing

Oracle implements function-based indexing

Use Cases:
When a query runs slowly. By checking the where clause, it is found that the SQL lower function is applied to a column, and the lower function prevents the use of existing indexes on the column. You want to create a function-based index to support this query, as shown below:

SQL> select index_name, column_name from user_ind_columns where table_name = 't1 ';

INDEX_NAME COLUMN_NAME
-------------------------------------------------------
T1_PK OBJECT_ID

SQL> set autotrace trace explain;
SQL> select * from t1 where lower (object_name) = 'I _ undo1 ';

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 908 | 101K | 436 (1) | 00:00:01 |
| * 1 | table access full | T1 | 908 | 101K | 436 (1) | 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id ):
---------------------------------------------------

1-filter (LOWER ("OBJECT_NAME") = 'I _ undo1 ')
From the above we can see that even if the table has an index, it is not used.

Solution
1. Create a function-based index
2. If you use Oracle database 11g or later, create an index virtual Column

The following describes how to implement function-based indexes.

To create an index, You can estimate the space used by the index and the space to be allocated.

SQL> set serveroutput on
SQL> var used_bytes number;
SQL> var allo_bytes number;
SQL> exec dbms_space.create_index_cost ('create index t1_object_name on t1 (lower (object_name) ',: used_bytes,: allo_bytes );
 
PL/SQL procedure successfully completed
Used_bytes
---------
2269350
Allo_bytes
---------
4194304

SQL> create index idx_lower on t1 (lower (object_name) tablespace index_nocompress;

SQL> select * from t1 where lower (object_name) = 'I _ undo1 ';

Execution Plan
----------------------------------------------------------
Plan hash value: 2274688371

Bytes -------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -------------------------------------------------------------------------------------------------
| 0 | select statement | 908 | 101K | 193 (0) | 00:00:01 |
| 1 | table access by index rowid batched | T1 | 908 | 101K | 193 (0) | 00:00:01 |
| * 2 | index range scan | IDX_LOWER | 363 | 3 (0) | 00:00:01 |
Bytes -------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id ):
---------------------------------------------------

2-access (LOWER ("OBJECT_NAME") = 'I _ undo1 ')

Note: you cannot directly modify a column created based on function indexes. You need to delete the index, modify the column, and then recreate the index. Otherwise, a ora-30556 error will be reported (a function index or bitmap join index is defined on the column to be modified)

View function-based index definitions dba/all/user_ind_expressions

SQL> select index_name, column_expression from user_ind_expressions;

INDEX_NAME COLUMN_EXPRESSION
---------------------------------------------------------------------------
IDX_LOWER LOWER ("OBJECT_NAME ")

Create an index in the virtual Column

Use Cases

Currently, we are using a function-based index. To achieve better performance, we want to replace the function-based index with a virtual column, create an index on the virtual Column (11 GB environment or later is required ).

SQL> alter table t1 add (lower_object_name generated always as (lower (object_name) virtual );
SQL> create index idx_lower on t1 (lower_object_name) tablespace index_nocompress;

Understanding ArcSDE indexes by Oracle Indexes

How to create an optimal index using Oracle Indexing Technology

Test example of the table where the NULL value of the Oracle index column triggers the execution plan

Oracle index primary key affects query speed

Oracle index Scanning