Help to optimize select stament

[Former Member]

Hi All,

In table LIKP we have the field LIFEX and we storage data is this field as you can see in the below examples:

LIFEX

123456789 65478947

235689777 54899999

Two values separated by sapces, the issue is, users can search using only one of the values, therefore I have to build a select stament that has wildcards in the where condition, something like this: where lifex like '* value*".

As you know table likp can have millions of records so the search is too slow. If I only put the wildcard at the end of the value ('value*') it works fine, but if I put the wildcar at begin is also too slow, I think because the index is not used.

Any ideas about how to build this search (select statment)?

Regards,

Eduardo Campos

[Former Member]

Is it a heavily used field in searches or report driven selections for your company? Why not index the individual (split) values in a Z-table by delivery number via user exit?

[volker_borowski2]

Hi,

what exactly is the search pattern?

- 10 digits being either the "right" or the "left" on field LIFEX (That would be good)

- or i.e. any 3-7 digits from either "right" or "left" at any position?

Do you need to stay OPEN SQL (i.e. for coding guideline reasons) or can you use native sql as well?

What is your DB type?

Volker

[Former Member]

Hi Volker,

The values don´t have a pattern, sometimes they contain 8 or more digits any of them, but always separated by space.

Our DB is oracle, we can execute open SQL from de ABAP code.

Regards,

Eduardo Campos

[brad_bohn]

Is it a heavily used field in searches or report driven selections for your company? Why not index the individual (split) values in a Z-table by delivery number via user exit?

[Former Member]

Hi Brad,

We cannot split the values, I mean is possible but we have to change several programs and applications that use table, so in this momment separete the values is not an option.

Regards,

Eduardo Campos

[Former Member]

Hi Eduardo,

Normally in case which you start with a wildcard the optimizer ignore the index.

I borrow from an external page two options for you.

http://www.dba-oracle.com/t_sql_like_clause_index_usage.htm

You can try the option to force your LIFEX field index to take advantage of full scan at index level which should be faster than a table full scan.

But even if this works for your case you should change the application to check if you really need force the index or not, in other words, only force LIFEX index for case with pattern start with *.

Regards, Fernando Da Ros

-

---

Additional experts opnion:

Edited by: Fernando Ros on Jan 6, 2011 11:07 PM