Function based indexes [DOCS] have been part of Oracle Database since at least version 8. Instead of just indexing someone’s name, you could also index the values returned by a function call over that name.

Example I just made up, and may not have anyting to do with the real world:
Names are stored case sensitive, but our app always upper cases search terms for name. Create an index on UPPER(name) so an index can be used to optimize these searches.

Ok, so if have done a very thorough interrogation with your business users and maybe even your developers – or more likely, if I’ve built these systems before – I will KNOW to include these indexing schemes into the design.

How do I do that in Oracle SQL Developer Data Modeler?

If I pull up the table properties and go to the indexes page, I can create a new index.
If I pull up the table properties and go to the indexes page, I can create a new index.

Hmm, I don’t want to necessarily index a column. I want to index a function over a column. I wonder what that checkbox does?

Toggle the expression checkbox, and enter your function call
Toggle the expression checkbox, and enter your function call

Click ok.

There's our index! Give them good names PLEASE.
There’s our index! Give them good names PLEASE.

What’s that DDL look like now?

Don't index ALL the things. Be judicious. And Test.
Don’t index ALL the things. Be judicious. And Test.

So to answer Kent’s original question: Yes, there is a way.

P.S. Thanks to Heli for the assist!

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

1 Comment

Write A Comment