Shulou(Shulou.com)06/01 Report--

Use the scene:

When a query runs slowly. By checking the where clause, it is found that one of the columns applies the sql lower function and the lower function.

Prevents the use of existing indexes on this column. You want to create a function index to support this query, as follows

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) = 'iTundo1'

Carry out the plan

Plan hash value: 3617692013

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 908 | 101 K | 436 (1) | 00:00:01 |

| | * 1 | TABLE ACCESS FULL | T1 | 908 | 101 K | 436 (1) | 00:00:01 |

Predicate Information (identified by operation id):

1-filter (LOWER ("OBJECT_NAME") = 'iTundo1')

You can see from the above that even if there is an index in the table, 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 implements a function-based index

To create an index, you can estimate the space used by the index and the space that needs to be allocated in the following ways

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) = 'iTundo1'

Carry out the plan

Plan hash value: 2274688371

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 908 | 101 K | 193 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 908 | 101 K | 193 (0) | 00:00:01 |

| | * 2 | INDEX RANGE SCAN | IDX_LOWER | 363 | | 3 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

2-access (LOWER ("OBJECT_NAME") = 'iTundo1')

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

View the function-based index definition dba/all/user_ind_expressions

SQL > select index_name,column_expression from user_ind_expressions

INDEX_NAME COLUMN_EXPRESSION

IDX_LOWER LOWER ("OBJECT_NAME")

Then implement to create an index in the virtual column

Working with scen

You are now using a function-based index, but for better performance, you want to replace the function-based index with a virtual column, and then create the index on the virtual column (11g 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.