How to reduce the cost?

user_17633003 0
Viewed 49
select * from XXCNN_AR_FIN016_MONTH_END
where period_name like '%-15'

I tried to create index on column period name but cost will not reduce .

Cost of this query is 12702.

After creating index, explain plan is showing options as storage full. For this I want to ask you the question: after creating index, why its not showing index scan or other scan?

How do I reduce the cost and time for this?

query-optimization oracle sql performance
2 Answers

If leading characters are specified in the LIKE pattern, index can be used (for patterns like this '51-%').

The solution is to create functional index using REVERSE function

CREATE INDEX IDX_XXCNN_AR_FIN016_MONTH_END_reverse ON XXCNN_AR_FIN016_MONTH_END(REVERSE(period_name ));

And then query like this:

select * from XXCNN_AR_FIN016_MONTH_END
where REVERSE(period_namex) like REVERSE('%-15');

Oracle will recognize REVERSE() function in the query predicate and will use index.

user_27003440

Too few data to answer, I'm afraid.

Though, you didn't say what kind of an index you created. Did you try a function-based index?

CREATE INDEX i1_xx
   ON xxcnn_ar_fin016_month_end (SUBSTR (period_name, -2));

and rewrite query to

SELECT *
  FROM xxcnn_ar_fin016_month_end
 WHERE SUBSTR (period_name, -2) = '15';
user_90979060
Related