Escape calculation on Indexed columns

If the indexed column is been utilized on a function in the WHERE condition then the optimizer will not use the index on the column. So avoid calculations on indexed columns, so the best way to handel this is by applying function and concatinate it to the indexed column.
But only MIN and MAX functions will utilize the index on the columns.

Example:

Less efficient:
SQL>SELECT * FROM oe_order_holds_all WHERE SUBSTR(order_hold_id,1,1) = ‘6’;

More efficient:
SQL>SELECT * FROM oe_order_holds_all WHERE order_hold_id LIKE ‘6%’;

Advertisements

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