Home » RDBMS Server » Performance Tuning » how to avoid multiple Like operators in a single query
how to avoid multiple Like operators in a single query [message #436230] Tue, 22 December 2009 04:31 Go to next message
bubblyaruna
Messages: 1
Registered: December 2009
Location: bangalore
Junior Member
when we r using multiple like operators on same column say "bankaccno" in a single query it reduces the performance. So can u please provide me an alternative solution for that
Re: how to avoid multiple Like operators in a single query [message #436239 is a reply to message #436230] Tue, 22 December 2009 05:21 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Either you need it either you don't need it, performances is irrelevant here (in the terms you posted your problem).

Regards
Michel
Re: how to avoid multiple Like operators in a single query [message #436300 is a reply to message #436239] Tue, 22 December 2009 21:03 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Using my crystal ball to deduce your problem, I guess you are running a query such as:
SELECT *
FROM   tab
WHERE  col LIKE :this
OR     col LIKE :that


Which is slower than the individual queries:
SELECT *
FROM   tab
WHERE  col LIKE :this

SELECT *
FROM   tab
WHERE  col LIKE :that


You can try:
SELECT /*+USE_CONCAT*/ *
FROM   tab
WHERE  col LIKE :this
OR     col LIKE :that

which will expand the OR out into a UNION statement, or you can recode it as a UNION yourself.

Ross Leishman
Re: how to avoid multiple Like operators in a single query [message #438295 is a reply to message #436300] Thu, 07 January 2010 22:02 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
hay Rleishman ,

I have tried your suggestion and follwing are the resuts.



 create table c_test ( A varchar(100) );

 insert into c_test select tname from tab;

 commit;


select A from c_test where a like 'A%' or a like 'B%'  

Statistics
----------------------------------------------------------
          1  recursive calls 
          0  db block gets
         10  consistent gets 
          0  physical reads
          0  redo size
       1003  bytes sent via SQL*Net to client
        261  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         39  rows processed


select /*+USE_CONCAT*/ A from c_test where a like 'A%' or a like 'B%' 

Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         18  consistent gets 
          0  physical reads
          0  redo size
       1003  bytes sent via SQL*Net to client
        261  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         39  rows processed



dont you think so sometimg using USE_CONCAT is more costly ?

-Rahul
Re: how to avoid multiple Like operators in a single query [message #438347 is a reply to message #438295] Fri, 08 January 2010 04:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Interesting - I don't get that - I get the same number of consistent reads for both queries.

You can rewrite the query like this:
select a from c_test where regexp_like(a,'^[AB]');
Re: how to avoid multiple Like operators in a single query [message #438415 is a reply to message #438347] Fri, 08 January 2010 15:53 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Without an Explain Plan, it is very hard to say what is going on in any of these queries. But I suspect that your first query is benefiting from your very small table size (10 blocks).

The OP had a query with multiple LIKE clauses and was complaining about the performance. My deduction from this is that the LIKE clauses performed well individually, but not so good in combination.

Given THIS set of events, it is likely that Oracle is performing a single scan on just one of the columns, and USE_CONCAT can help encourage Oracle to index scan on both predicates, thereby giving performance comparable to the individual predicates.

I don't recall saying that USE_CONCAT should be standard operating procedure for any arbitrarily engineered multi-LIKE query. But if you want a broad sweeping statement, here's one: over large data volumes, if you have two very selective predicates combined in an OR statement, they will benefit from transformation into either an In-list iterator or a concatenation.

Ross Leishman
Re: how to avoid multiple Like operators in a single query [message #439435 is a reply to message #436230] Sat, 16 January 2010 16:05 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes, with such pittifully small amounts of data, some would say performance comparisons are meaningless.

The point made here however is this:

There are basically several variations on a theme when thinking about how to rewrite your query to make it go faster.

1) user union queries
2) use a hint to simluate same
3) use OR and let oracle rewrite your query with the same idea

4) also, you can insert your values into a work table and join to the work table. The drawback with this solution is it makes your problem a two step process but its benefit is it is generic and can handle any number or values and can be optimized by the oracle optimizer using most of its tricks.

You will have to do testing to determine which of these variations (or possibly something else entirely different) is the best solution for you.

Good luck, Kevin
Re: how to avoid multiple Like operators in a single query [message #439438 is a reply to message #439435] Sat, 16 January 2010 17:30 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Kevin Meade wrote on Sun, 17 January 2010 09:05
4) also, you can insert your values into a work table and join to the work table. The drawback with this solution is it makes your problem a two step process but its benefit is it is generic and can handle any number or values and can be optimized by the oracle optimizer using most of its tricks.


Kevin, that last one is a bit of a stretch. It assumes that the ORed predicates are 1) equality conditions, and 2) on the same column. Neither of these might be true. In fact, if driving the query off the these predicates is optimal, an INLIST Iterator is typically the best plan.

Ross Leishman
Re: how to avoid multiple Like operators in a single query [message #439441 is a reply to message #436230] Sat, 16 January 2010 17:45 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
All true Ross. And I suppose it could be considered a stretch. I am glad you clarified this point for us.

In my defense however, whenever #4 is workable, it does in general provide the best performance because it turns the query into a true join and thus opens up join strategies for optimization.

I adint however, my position is tenuous. Let us leave it at #4 is an interesting alternative for those who wish to explore it. My expriences have been positive when I have used it.

Kevin
Previous Topic: Query optimisation, table partitionning and lack of index
Next Topic: Database slow problem
Goto Forum:
  


Current Time: Wed Sep 28 04:02:47 CDT 2016

Total time taken to generate the page: 0.12923 seconds