CODEWITHSUNDEEP
sqloracle-databasequery-optimization
sriman narayana
sriman narayana

Reputation: 359

oracle create index on column length with condition

I had a column name imsi and the length of column should be 15 characters, can we create an index based on length of column with condition

i already created an index on length(imsi) , but i want all the records where length(imsi)<>15, but the query is taking long time, where as for length(imsi)<15 gives output in milliseconds. but i want the output where length(imsi) <> 15

select * from msisdn_data where length(imsi)<> 15

or

select * from customer_data where length(imsi)>15 or length(imsi)<15

both the queries takes long time

Upvotes: 2

Views: 279

Answers (1)

Aman Singh Rajpoot
Aman Singh Rajpoot

Reputation: 1479

Try

ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED; 
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

  • The function invoked involve in the index expression must be deterministic. It means that for the same input, the function always returns the same result.

  • The query optimizer can use a function-based index for cost-based optimization, not for rule-based optimization. Therefore, it does not use a function-based index until you analyze the index itself by invoking either DBMS_STATS.GATHER_TABLE_STATS or DBMS_STATS.GATHER_SCHEMA_STATS.

Or you can use Hint.

SELECT /*+ index(msisdn_data imsi_idx) */ * 
FROM msisdn_data WHERE LENGTH(imsi) <> 15;

For more information on why the function-based index is not used in your query, refer the link

http://www.dba-oracle.com/t_statistics_function_based_index.htm

Upvotes: 2

Related Questions