2

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

Praveen
  • 415
  • 5
  • 9
sriman narayana
  • 359
  • 2
  • 20
  • Could you show me the current execution plans of two queries? – Anh Duc Ng Apr 25 '21 at 04:47
  • `| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 361K| 613M| 625K (1)| 00:00:25 | |* 1 | TABLE ACCESS FULL| CUSTOMER_DATA | 361K| 613M| 625K (1)|` – sriman narayana Apr 25 '21 at 05:23
  • for both the queries full table scan is happend – sriman narayana Apr 25 '21 at 05:26
  • As you mentioned, `length(imsi)<15` gives output in milliseconds. What about `length(imsi) > 15` ? – Anh Duc Ng Apr 25 '21 at 05:31
  • both as individual it gives output in milliseconds where as with or clause full table scan is happening – sriman narayana Apr 25 '21 at 05:41
  • Have you tried two solutions below: 1) Use index hint: https://stackoverflow.com/questions/1838094/force-index-use-in-oracle 2) UNION to combine two queries – Anh Duc Ng Apr 25 '21 at 05:45
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/231560/discussion-between-anh-duc-ng-and-sriman-narayana). – Anh Duc Ng Apr 25 '21 at 05:57

1 Answers1

2

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

Aman Singh Rajpoot
  • 1,451
  • 6
  • 26