Skip to content

Oracle: Like Condition pitfall

12 April 2012

Interesting performance pitfall exists in Oracle queries when LIKE condition is used in WHERE clause.

When a wildcard is used to match the starting portion of a column the index cannot be used directly. Take a look at the explain plan for condition starting with %:

Oracle can use B*-Tree indexes with queries having LIKE operator only if the expression does not start with a leading wildcard (%,_). The optimizer can make effective use of the index only if the first characters of the index key are known.

Keep this in mind.

Advertisements

From → Oracle

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: