Chapter 8 Creating J2EE Applications for BEA WebLogic

Oracle SELECT HINTS

WebLogic Server supports an EJB QL extension that allows you to pass INDEX usage hints to the Oracle Query optimizer. With this extension, you can provide a hint to the database engine. For example, if you know that the database you are searching can benefit from an ORACLE_SELECT_HINT, you can define an ORACLE_SELECT_HINT clause that will take ANY string value and then insert that String value as an ORACLE HINT after the SQL SELECT statement.

To use this option, declare a query that uses this feature in the weblogic-ql element. This element is found in the weblogic-cmp-rdbms-jar.xml file. The weblogic-ql element specifies a query that contains a WebLogic specific extension to the ejb-ql language.

The WebLogic QL keyword and usage is as follows:

SELECT OBJECT(a) FROM BeanA AS a WHERE a.field > 2 
	ORDERBY a.field 
SELECT_HINT '/*+ INDEX_ASC(myindex) */'

This statement will generate the following SQL with the optimizer hint for Oracle:

SELECT /*+ INDEX_ASC(myindex) */ column1 FROM .... (etc)

In the WebLogic QL ORACLE_SELECT_HINT clause, whatever is between the single quotes ( ' ' ) is what is inserted after the SQL SELECT. It is the query writer's responsibility to be sure that what is inside quotes makes sense to the Oracle database.

Copyright (C) 2005. Sybase Inc. All rights reserved.