CODEWITHSUNDEEP
sqloracle-databaseperformancequery-optimization
hadoo
hadoo

Reputation: 213

Avoid applying a function to an index column

I need to filter out data that exceeds a certain length but the column that contains the data is an indexed column. If I apply a function to the column I lose the benefit of the index.

I cannot create a new index or alter the column as I am not an admin to the database. I would prefer not to drop the data after the fact.

I know of a few ways to filter the column but all would use some kind of function.

select
table.name
from 
table
where
length(table.name)>12
;

The field table.name is not nullable.

Upvotes: 0

Views: 1079

Answers (2)

APC
APC

Reputation: 146239

If I apply a function to the column I lose the benefit of the index.

Ah, but what is the benefit of an index?

Consider these two values:

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ

Are they both longer than 12 characters? Yes. Are they likely to be adjacent in the index? Of course not. Therefore the only way for Oracle to use an index to find those values is to execute a Full Fast Scan over the index and evaluate the length of each entry. Now Oracle can do that, but is it worthwhile?

Your posted query is selecting just name. In a comment you say name is not nullable. In that case it would be efficient for Oracle to use the index, because there is no need to read the table records: the index has sufficient information to satisfy the query.

However.

In that comment you also say:

the query is not that simple

If your actual query includes other columns in the projection then the database does have to visit the table to get those values. At which point the rule of thumb for indexed reads kicks in: if the result set of the query is greater than 1-2% of all the rows in the table it's more efficient to do a Full Table Scan than use an index. So the number of records in the table becomes pertinent, and especially the proportion of records where length(name) > 12. If 99% of the records have short names then it is probably still more efficient to Full Fast Scan the index. But if it's only 90% using the index would probably be deadly to performance.

Likewise, if your actual query applies additional criteria in the WHERE clause it may be more efficient to do a Full Table Scan (because the database needs to read the records to evaluate those filters) to to use a different index, if there is an appropriate one.

So, while the index would be useful for the toy query you posted in your question it may not help with your actual query, and indeed could lead to a sub-optimal access path.

is it a case by case situation depending on query complexity?

Yes. The answer is always, it depends. That's why database tuning professionals can charge the fat consultancy fees they do. If you don't provide the whole query the best we can do is point you at this post which explains to ask performance tuning questions and wish you good luck.

Upvotes: 1

WW.
WW.

Reputation: 24291

If the column is NOT NULL, then Oracle can answer the query using a full index scan. It will need to read every row in the index in order to find only those rows with the length greater than 12. If the index is smaller than the table this is faster than a full scan.

You are only selecting the indexed column so Oracle would not need to visit the table but can get the result entirely from the index. If you were to select other columns there were not in that index Oracle would also need to read the table row having first located the row in the index.

There is no way around this without adding a more suitable index or otherwise changing the database schema.

Upvotes: 1

Related Questions