How to use SQL Hints on Java query

Question

Just heard about 'SQL Hints' to optimize query's result or processing time at typing SQL queries. I have found plenty of information about how to implement this concept into Hibernate, but nothing about how to use it with plain Java code. Can anyone help me with this?

We are using the following code:

String value = "someValueToUseAsFilter";
String query =  "SELECT /*+ opt_param('_optimizer_cost_model','io') opt_param('optimizer_index_cost_adj',20) opt_param('optimizer_index_caching',65) PARALLEL(4)*/ "+
                        " T.field AS table_field "+
                        " FROM table T "+
                        " WHERE T.field = ? "+
                        "/";
ResultSet rs = null;
PreparedStatement stmt = null;
try {
        stmt = this.connection.prepareStatement(query);
        stmt.setQueryTimeout(this.timeout);
        stmt.setString(1, value);
        rs = stmt.executeQuery();
}
catch (Exception e){
        e.printStackTrace();        
}

Next, eclipse is throwing the following exception:

java.sql.SQLSyntaxErrorException: ORA-00936: missing expression

The query has been tested and if launched directly against the database it works fine. But keeps returning this error when using it on code. Can anyone explain me if this is even possible? If so, How can I use it in this context?

Thank you.

Remove the `/` at the end of your statement. For details see: https://stackoverflow.com/a/10207695/330315 — , Jul 18 '18 at 10:06
IHMO EclipseLink/TopLink is more Oracle oriented, they have some kind of extension for it. PS: Semicolon ";" not slash "/" are not par of Oracle SQL grammar. They denote the end of sql statement for sqlplus tool. — ibre5041, Jul 18 '18 at 10:32
Is Oracle DB itself not reachable? Why try implement (with "hints") data extraction within java, when better approach (by "good practice" concept) is to do the "dirty" work in DB itself (by procedure/function) and with it receive data in java? — Ychdziu, Jul 18 '18 at 10:42
@Ychdziu due to enterprise environment, we have no access to the DB itself and we are directly asked to implement some of these type of queries into an already finished development — Sergio A., Jul 18 '18 at 10:59
@a_horse_with_no_name thanks! I got myself confused because of hinted queries — Sergio A., Jul 18 '18 at 11:16
@SergioA., stay strong! The company, that exists by this model - wont last long.. — Ychdziu, Jul 18 '18 at 11:19
@Ychdziu Unfortunately, the company is way too big to fall off; but we both agree that this approach is not the right one to speed up some queries — Sergio A., Jul 18 '18 at 11:24
Be avare that hints are not automatically *optimizing* directives. A query reading one row will degradate to few seconds elaped time if you deploy `PARALLEL` hint. Check the [documentation](https://docs.oracle.com/database/121/TGSQL/tgsql_influence.htm#TGSQL260) before use;) — Marmite Bomber, Jul 18 '18 at 11:25

score 3 · Accepted Answer · answered Jul 18 '18 at 10:38

As explained in the answer @ahorse_with_no_name linked to, the / is a client directive to end and execute the statement. Your error is nothing to do with the hints or Java, you just need to remove that trailing slash.

You can see the same effect running your statement other ways, e.g. as dynamic SQL in an anonymous block:

set serveroutput on
declare
  query varchar2(4000);
begin
  query := q'[
SELECT /*+ opt_param('_optimizer_cost_model','io') opt_param('optimizer_index_cost_adj',20) opt_param('optimizer_index_caching',65) PARALLEL(4)*/
T.field AS table_field
FROM your_table T
WHERE T.field = :var
/
]';
  dbms_output.put_line(query);
  execute immediate query;
end;
/

The generated statement is printed as:

SELECT /*+ opt_param('_optimizer_cost_model','io') opt_param('optimizer_index_cost_adj',20) opt_param('optimizer_index_caching',65) PARALLEL(4)*/
T.field AS table_field
FROM your_table T
WHERE T.field = :var
/

with that trailing slash, and as you say that will run directly in a client/IDE; but the execute immediate gets the same error you do:

ORA-00936: missing expression
ORA-06512: at line 12

The slash is being seen purely as a division symbol, so it's expecting another expression after that - to make the clause WHERE T.field = :var / <something>.

If you remove the slash:

declare
  query varchar2(4000);
begin
  query := q'[
SELECT /*+ opt_param('_optimizer_cost_model','io') opt_param('optimizer_index_cost_adj',20) opt_param('optimizer_index_caching',65) PARALLEL(4)*/
T.field AS table_field
FROM your_table T
WHERE T.field = :var
]';
  dbms_output.put_line(query);
  execute immediate query using 42;
end;
/

the generated statement now doesn't have it (obviously), and you don't get the error:

PL/SQL procedure successfully completed.

(I should be selecting into something - the query is only parsed here, not executed, but that doesn't matter for this example).

Note that you also don't need a semicolon on the end of the statement, as that is a statement separator; adding one would cause a different error like ORA-00911 invalid character.

How to use SQL Hints on Java query

1 Answers1