Oracle: How to use function based index in container generated jpa query WHERE clause

Question

We have a data table where it has a function based index (database is Oracle). But in our Java EE ejb application, system accesses the data using jpa queries.

But in order to force the oracle to use the function based index instead of doing a full table scan, I need a way to tell the jpa container to generate the sql with the function defined in the index in the WHERE clause of the sql query.

As far as I know, we have to use native queries for creating customizations in the generated queries like that. But in order to use native queries we have to do many code changes. Could anyone please suggest any other work around for solving this issue?

Index: (We needed to enforce unique constraint for Dev_Id + Dev_Type, but sometimes Dev_Id can be null while Dev_Type can be duplicated.)

 (NVL2(Dev_Id, Dev_Id, NULL), NVL2(Dev_Id, Dev_Type, NULL))

Query we need: (In order to use the index)

Select * from some_table where NVL2(Dev_Id, Dev_Id, NULL) = 'some_val';

Container generated query:

Select * from some_table where Dev_Id = 'some_val';

Isn't NVL2(Dev_Id, Dev_Id, NULL) redundant? And can't you just add an index on Dev_Id? — eaolson
– eaolson, Commented Oct 31, 2012 at 4:16
@eaolson our DBA said it didn't work for some valid values, But I am wondering why that happened, because NVL2(Dev_Id, Dev_Id, NULL) and Dev_Id is basically same. I couldn't get the values which he talked about. I will check this again. thanks. — Manjula
– Manjula, Commented Oct 31, 2012 at 6:24
@eaolson DBA said we cannot add a unique index like (Dev_Id, NVL2(Dev_Id, Dev_Type, NULL)), because then oracle won't allow NULL values for Dev_Id. That is strange, but I didn't have the time to test it to confirm yet. — Manjula
– Manjula, Commented Nov 1, 2012 at 10:13

Thilo · Accepted Answer · 2012-10-31 03:49:50Z

1

Maybe creating a view and having JPA go through that is an option?

 CREATE VIEW SOME_VIEW AS SELECT SOME_TABLE.*,  
    NVL2(Dev_Id, Dev_Id, NULL) AS NVL_DEV_ID FROM SOME_TABLE;


 SELECT * FROM SOME_VIEW WHERE NVL_DEV_ID = ?

Or a virtual column on the table that evaluates to the function?

answered Oct 31, 2012 at 3:49

Thilo

264k107 gold badges527 silver badges674 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gpeche · Accepted Answer · 2016-01-25 14:09:03Z

1

JPA 2.1 includes a FUNCTION operator that allows to invoke DB-specific functions:

from SomeEntity where function('NVL2', devId, devId, null) = 'some_val';

If you don't have JPA 2.1 available but are using Eclipselink >= 2.4, you can also use FUNCTION, as it is available as a Eclipselink extension.

If you have Eclipselink 2.3.x, you do not have FUNCTION, but you can use the equivalent FUNC that is available as a Eclipselink extension.

edited Jan 25, 2016 at 14:09

answered Jan 22, 2016 at 13:02

gpeche

22.7k5 gold badges40 silver badges52 bronze badges

Collectives™ on Stack Overflow

Oracle: How to use function based index in container generated jpa query WHERE clause

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related