dotConnect for Oracle supports Oracle optimizer hints in Entity Framework - a feature for flexible tuning of SQL queries, generated by a user application.

Oracle supports more than 60 different Oracle optimizer hints. You can see the complete list of Oracle optimizer hints and recommendations on their use in the Oracle Database documentation.

It is better to perform query optimization in external application, such as dbForge Fusion for Oracle, or any other tool for Oracle that you prefer. Queries, generated by Entity Framework, can be traced with dbMonitor and then copied and pasted to your SQL development application, where you can evaluate query performance, study execution plan, and choose optimizer hints to use.

Using Oracle Optimizer Hints as Oracle Function

Entity Framework provides not so many extensibility points for 3rd-party Entity Framework providers, so, to implement support for Oracle optimizer hints, we use database-specific functions feature in a non-standard way by adding the Devart.Data.Oracle.HINTS(string hints) function.

You can call this function directly in LINQ to Entities queries using the OracleFunctions class from the Devart.Data.Oracle.Entity assembly.

var originalQuery = from emp in ctx.Emps
                    // ...
                    select emp;
var queryWithHints = originalQuery.Where(e => 
    OracleFunctions.Hints("INDEX(\"Extent1\" index1)"));
var result = queryWithHints.ToList();

Dim originalQuery = From emp In ctx.Emps _ ' ...
                    Select emp
Dim queryWithHints = originalQuery.Where(Function(e) 
    OracleFunctions.Hints("INDEX(""Extent1"" index1)"))
Dim result = queryWithHints.ToList()

Using Oracle Optimizer Hints as Extension Method

If you use ObjectContext, not DbContext, you can wrap this Devart.Data.Oracle.HINTS function and create an extension method, using the query builder feature of ObjectQuery.

public static class OracleExtensions {
 
    public static IQueryable<T> OracleHints<T>(
        this System.Data.Objects.ObjectQuery<T> source, string hints
    )
      where T : class {
      if (source == null)
        throw new ArgumentNullException("source");
      return source.Where("Devart.Data.Oracle.HINTS('" + hints + "')");
    }
}

Module OracleExtensions
    <Extension()>
    Function OracleHints(Of T As Class)( _
        ByVal source As System.Data.Objects.ObjectQuery(Of T), ByVal hints As String
    ) As IQueryable(Of T)
        If source Is Nothing Then Throw New ArgumentNullException("source")
        Return source.Where("Devart.Data.Oracle.HINTS('" & hints & "')")
    End Function
End Module

Then you can use it in the following way:

var query = from emp in ctx.Emps.OracleHints("FULL(emp) PARALLEL(emp, 5)")
            // ...
            select emp;
var result = query.ToList();

Dim query = From emp In ctx.Emps.OracleHints("FULL(emp) PARALLEL(emp, 5)") _ ' ...
            Select emp
Dim result = query.ToList()

Using INDEX_HINT Function

The above examples show how to use the HINTS function to instruct the optimizer to use an index scan for the query table. However, in this the HINTS function requires you to specify the alias of the queried table, and this is not always convenient, especially if you are querying data from several tables. In order to solve this problem, we have added the INDEX_HINT function, that allows you to specify any entity property mapped to the column of the table, the index we are using belongs to, instead of the table alias.

The above examples of using Oracle optimizer hints as the Oracle function will look like the following if we use the INDEX_HINT function instead of HINTS.

var originalQuery = from emp in ctx.Emps
                    // ...
                    select emp;
var queryWithHints = originalQuery.Where(e =>
    OracleFunctions.IndexHint(e => e.Id, "index1"));
var result = queryWithHints.ToList();

Dim originalQuery = From emp In ctx.Emps _ ' ...
                    Select emp
Dim queryWithHints = originalQuery.Where(Function(e) _
    OracleFunctions.IndexHint(Function(e) e.Id, "index1"))
Dim result = queryWithHints.ToList()

Specifics and Limitations of Oracle Optimizer Hints in dotConnect for Oracle

We decided to always place hints after the first, upper-level SELECT keyword because structure of a generated SQL query does not always correspond to the structure of the LINQ to Entities query structure, and there is no unambiguous way to specify hints for a specific subquery.

var query = from dept in ctx.Depts.OracleHints("FULL(dept) NOCACHE(dept)")
            // ...
            select dept;

Dim query = From dept In ctx.Depts.OracleHints("FULL(dept) NOCACHE(dept)") ' ...
            Select dept

SELECT /*+ FULL(dept) NOCACHE(dept) */ deptno, dname, location
  FROM dept;
...
  

If several hints are used, they are united in a single one.

var query = ctx.Depts
            // ...
            .Select(dept => dept);
query = query.Where(e => OracleFunctions.Hints("FULL(dept)"));
query = query.Where(e => OracleFunctions.Hints("NOCACHE(dept)"));

Dim query = ctx.Depts _ ' ...
            .[Select](Function(dept) dept)
query = query.Where(Function(e) OracleFunctions.Hints("FULL(dept)"))
query = query.Where(Function(e) OracleFunctions.Hints("NOCACHE(dept)"))

SELECT /*+ FULL(dept) NOCACHE(dept) */ deptno, dname, location
  FROM dept;
...
  

You cannot use string variables as OracleFunctions.Hints parameters, only string literals are allowed. For example, the following literal can be used:

var query = ctx.Emps.Where(e => OracleFunctions.Hints(
     "PARALLEL_INDEX(emp, index1, 3)"
));

Dim query = ctx.Emps.Where(Function(e) OracleFunctions.Hints( _
    "PARALLEL_INDEX(emp, index1, 3)") _
)

and the following variable cannot:

string hints = "PARALLEL_INDEX(emp index1, 3)";
var query = ctx.Emps.Where(e => OracleFunctions.Hints(hints);

Dim hints As String = "PARALLEL_INDEX(emp index1, 3)"
Dim query = ctx.Emps.Where(Function(e) OracleFunctions.Hints(hints))