Hi All,
I had already post a thread related to this thread but there is little bit difference in both threads.
I had a query like below:
select concept_a, raw_concept_a, relation_name, concept_b, raw_concept_b from discovered_relations where lower(concept_a) like '%consumer%' and lower(concept_a) like '%confidence%' ;
That query taking too much time to execute and its coast high.
cost and execution time : 1155K and 03:51:11
I changed above query with the help of Forum's gurus, below is changed query:
select concept_a, raw_concept_a, relation_name, concept_b, raw_concept_b
from discovered_relations
where regexp_instr(concept_a, '(consumer.*confidence)|(confidence.*consumer)',1,1,1,'i') > 0;
I created index for changed query as well:
create index SIDEV.IDX_reg_instr on SIDEV.DISCOVERED_RELATIONS(REGEXP_INSTR ("CONCEPT_A",'(consumer.*confidence)|(confidence.*consumer)',1,1,1,'i'));
After that cost and time of query get reduce to :
After optimization : 40001 and 08:01
Now the issue is every time my query will vary like condition it means that, string for search may be vary to any value.
So I need a function based index like above's index which can work for any string in like operator, now my created index is only working fine for fixed string..
thanks,