Use of SUBSTR with an index
I need to run an update that restricts on the first 20 characters of a TEXT field. I added a INDEX ont he fiirst 20 character sof the text field but it does not get used.
The query in select form looks like this
SELECT * FROM CORAX_EVENT, CORAX_CAPITAL_CHANGE
WHERE PROCESSED=1 AND
CORAX_EVENT.CAP_CHNG_MARKET_LVL_ID = CORAX_CAPITAL_CHANGE.CAP_CHNG_MARKET_LEVEL_ID
AND (LOWER(SUBSTR(EVENT_DESCRIPTION, 0, 20)) LIKE 'buyback%' OR LOWER(SUBSTR(EVENT_DESCRIPTION, 0, 20)) LIKE 'share buyback%'
OR LOWER(SUBSTR(EVENT_DESCRIPTION, 0, 20)) LIKE 'buy-back%' OR LOWER(SUBSTR(EVENT_DESCRIPTION, 0, 20)) LIKE 'buy back%'
OR LOWER(SUBSTR(EVENT_DESCRIPTION, 0, 20)) LIKE 'share buy-back%' OR LOWER(SUBSTR(EVENT_DESCRIPTION, 0, 20)) LIKE 'share buy back%'
OR LOWER(SUBSTR(EVENT_DESCRIPTION, 0, 20)) LIKE 'share repurchase%' OR LOWER(SUBSTR(EVENT_DESCRIPTION, 0, 20)) LIKE 'repurchase%')
AND LOWER(SUBSTR(EVENT_DESCRIPTION, 0, 20)) NOT LIKE '1)%'
Ideally the final part of the restriction clause would look like this NOT LIKE '%1)%' but I was concerned it would not use the index if a leading wildcard was used. This was needless as it does not use the new index anyway. There is an index on CAP_CHNG_MARKET_LVL_ID which is utilised and CAP_CHNG_MARKET_LEVEL_ID is the 2nd column in the primary key index of CORAX_CAPITAL_CHANGE which is not utilised.
Suggestion on getting the index involved?
Thx,
David