CODEWITHSUNDEEP
sqloracle-databaseperformancequery-optimization
LittleBitDeveloper
LittleBitDeveloper

Reputation: 17

How to reduce the cost?

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?

Upvotes: 1

Views: 96

Answers (2)

leftjoin
leftjoin

Reputation: 38335

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.

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 143003

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';

Upvotes: 1

Related Questions