1

I have a table that has among others, two columns

SPONSOR_ID     number
NR_CERTIFICATE char(11)

And there's a index for these two columns

NR_CERTIFICATE + SPONSOR_ID

This table has hundreds of thousands of rows, and the problem is, when we filter by a specific certificate, the select takes nanoseconds as expected:

SELECT * FROM TBL_XXX WHERE NR_CERTIFICATE = '33300123456'

the problem is when we use LIKE to filter:

SELECT * FROM TBL_XXX WHERE NR_CERTIFICATE LIKE '%123456'

Then the select takes around 15 seconds.

I know that in this case, the index is discarded and a table scan takes place. Is there a way to force ORACLE to use the index in this case? Or is there a way to speed up this select?

Aldwoni
  • 1,168
  • 10
  • 24
Valmir Cinquini
  • 371
  • 7
  • 17

2 Answers2

0

You can "force" the index use with optimizer hints :

SELECT /*+INDEX(TBL_XXX,YOUR_INDEX_NAME)*/ * 
FROM TBL_XXX 
WHERE NR_CERTIFICATE LIKE '%123456'

That answers the question but not sure if that will solve your issue. You should show the table struct and explain plan for further help

Thomas G
  • 9,886
  • 7
  • 28
  • 41
0

If you force the use of the index with an hint, Oracle will do a full index scan. It will not be interesting for you.If your query has a sense, maybe your data model is not appropriate. Maybe a good way is to split you column nr_certificate in two columns. If it's possible, it will be easy to you after to have the good and quick query.

eliatou
  • 744
  • 5
  • 12