topic Re: Help to optimize select stament in Application Development and Automation Discussions

Help to optimize select stament

Former Member — Thu, 06 Jan 2011 16:08:50 GMT

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

Re: Help to optimize select stament

volker_borowski2 — Thu, 06 Jan 2011 17:11:45 GMT

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

Re: Help to optimize select stament

brad_bohn — Thu, 06 Jan 2011 17:25:29 GMT

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?

Re: Help to optimize select stament

Former Member — Thu, 06 Jan 2011 19:47:06 GMT

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

Re: Help to optimize select stament

Former Member — Thu, 06 Jan 2011 19:50:08 GMT

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

Re: Help to optimize select stament

Former Member — Thu, 06 Jan 2011 22:03:39 GMT

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