On May 29, 4:45 pm, Jeremy Smith <godto..._at_hotmail.com> wrote:
> Trying to lower the cost of the query below, do to the large disk read
> for the table services.
>
> SELECT SERVER_ID,SERVER_NAME
> FROM serverdb.servers
> WHERE status_id = 1
> AND server_id IN (SELECT server_id
> FROM services
> WHERE svc_display_name LIKE '%Microsoft Exchange
> %')
> AND server_id IN (SELECT server_id
> FROM services
> WHERE svc_display_name LIKE '%TSM%')
>
> I thought about doing a text index on the column svc_display_name and
> updating the query to do a contain, but this table has a lot of
> delete, insert and updates. So I wasn't sure how creating a text
> index would slow down all of the transactions on the table services.
>
> My goal is to do a search on the services table only once and not 2
> times.
>
> Any thoughts would be helpful! thanks
What version of Oracle are you using? If its 10G a combination of
regexp_instr and a function based index may help you (assuming your
problem is inability to use a normal index on svc_display_name table
due to the %string% syntax you are using and the services table is
quite large and not only a handful of rows).
Received on Tue May 29 2007 - 12:23:48 CDT
