CODEWITHSUNDEEP
sql-server-2005oracle11gsql-like
KOT
KOT

Reputation: 2116

Trick to make SQL query do LIKE or = depending on bind variable?

right now I have the following query in a stored procedure.

select * from table A where A.indexed_char_column LIKE :param1

:param1 can contain any type of wildcharacters, but sometimes it might not. '%abcdef%' usually wants a full table scan and 'abcdef' should do a index range scan. So I would like the SQL engine to use two different execution plans depending on this parameter.

Is there anyway I can make this behaviour possible? I would like answers for both Oracle 11gr2 and SQL Server 2005. I'm thinking if I could include a dummy parameter in the query (like a comment or something) that makes the SQL engine think of the two queries, that are actually identical, not to be identical.

select /* use table scan */ * from table A where A.indexed_char_column LIKE :param1

select /* use index */ * from table A where A.indexed_char_column LIKE :param1

But I don't know how to accomplish this? Any other suggestions? Should I just use two stored procedures?

Upvotes: 0

Views: 653

Answers (3)

Andrew Carmichael
Andrew Carmichael

Reputation: 3113

In SQL Server.... If you've done some testing and you're sure that it will benefit you to have two execution plans, then Joel's suggestion of OPTION (RECOMPILE) is the way to go.

However, if the index is narrow and selective, then it's probably best to use the index for the LIKE query as well as the equals query.

Conversely, if the index isn't very selective, then the equals query should probably just go straight for the table scan.

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132630

If you have the ability to change the hint/comment according to whether the value contains wildcards or not, why not change the statement instead?

if (contains wildcards) then
    select * from table A where A.indexed_char_column LIKE :param1;
else
    select * from table A where A.indexed_char_column = :param1;

Upvotes: 2

Joel Coehoorn
Joel Coehoorn

Reputation: 416039

For sql server you can use table hints or query hints (look at OPTION RECOMPILE). For either of those you'll probably have to be smart enough to know what hints to include in the query yourself. I'm not sure what the oracle equivalents are.

Upvotes: 1

Related Questions