Modeling Function Based Indexes in Oracle

@thatjeffsmith Is there a way to define a function-based index in #SQLDevModeler?

— Kent Graziano (@KentGraziano) October 29, 2014

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?

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?

Click ok.

What’s that DDL look like now?

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

P.S. Thanks to Heli for the assist!