Avoid applying a function to an index column

Question

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.

"I cannot create a new index as I am not a DBA". That's a poor excuse - if only a DBA can create new indexes, and if indeed you can make a good case that a new index is needed, then you would instruct the DBA to create the index you need. If a lot of your queries filter on the length of a string in a column, a function-based index on that column may prove helpful, and it doesn't matter who will actually create it. On the other hand, if it's to support just one query, and the query is not critical, then don't create the index and let the query take as long as it takes. — , Apr 18 '19 at 20:41
I am aware creating an index on the length would allow me to use a different index. What i was asking is if there is a way around that. I was trying to avoid answers like this by being specific in the question. thank you for your input. — hadoo, Apr 18 '19 at 20:47
If a value in that column is greater than 12 characters do you want it to not pull that column or do you want just the first 12 characters of whatever is in that column? — derek.wolfe, Apr 18 '19 at 20:55
if the value in the column is greater than 12 i want to pull in the field — hadoo, Apr 18 '19 at 20:59
Is the query really as simple as what you posted, or does it also SELECT other things? If it is as simple as what you posted, the index WILL be used, if the column is declared `NOT NULL`. On the other hand, if the column is in fact nullable, then the index won't be used, because Oracle is not intelligent enough to say to itself, "if length(NAME) > 12 then NAME can't be NULL, so I am allowed to use the index." — , Apr 18 '19 at 21:02
"What I was asking is if there is a way around that." My point in my first comment is that in the post you give a reason why you want a workaround, but that reason that you give is bogus. If the best answer is an index on length, then don't look for a workaround. Get your DBA to create the index you need. — , Apr 18 '19 at 21:04
Id think a workaround would be easier than creating a new index. And no, the query is not that simple. I was just trying to keep things simple. The field is not nullable. Are you saying that if the field is not nullable the index will be maintained when the funtion is applied? Or is it a case by case situation depending on query complexity? Which still puts me back at the original question as that table is used within varying levels of complex queries . — hadoo, Apr 18 '19 at 21:11

score 1 · Answer 1 · answered Apr 19 '19 at 01:44

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.

APC · Accepted Answer · 2019-04-19T06:22:25.247

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.

you've provided some very interesting insight. I was under the impression from what I'd read that any time a function was applied the index would never be used. This table is used within many queries often with many tables. The field in question is a case number. I will look more into the explain plans and see if I can learn more about what is actually happening. — hadoo, Apr 19 '19 at 10:36

Avoid applying a function to an index column

2 Answers2