To actually use function-based indexes and not only be able to create them, the database parameter (INIT.ORA) which name is like
'QUERY_REWRITE_ENABLE' (my spelling is probably incorrect) must be TRUE.
Then it will appear in your execution plan (if that makes it cheaper, of course).
Is there any way to set the parameter query_rewrite_enabled to 'true' using JDBC?
I don't think, sending "alter session set query_rewrite_enabled=true;" as an SQL statement via JDBC will work properly.
Neither I think, my DBA lets me set the parameter for the whole DB.
Would there be a way to set it only for one specific schema?
I don't know about that JDBC thing. My collegue thinks that sending the command 'ALTER SESSION ...' should work just fine.
Did you try setting it for your session an did the index appear in the execution plan?
It's not really a big thing though, for a DBA to change that for a database.
If you have a version older than 9i the database must 'bounce', but most of the databases still do that occasionally.
It's not like 'Could you double the SGA and give me the SYS password'.