Linq2db: Introduce (Oracle) optimizer hints (on select )

[jges42]

Oracle allows adding optimizer hint comments on selects that influence the execution plan of the query. With linq2db we can only add queryhints that get appended to the query but don't help in case of an Oracle DB.
I'd like to propose a feature that would look like this.

var result = db.TableName
   .Where(foo => foo.bar = "baz")
   .SelectWithHint("/* +NO_INDEX(TableName_bar) */", foo => foo.bar ).ToList();

this would create SQL like that:

SELECT /* +NO_INDEX(TableName_bar) */ bar  FROM foo WHERE bar = "baz";

Right now i don't see that this is possible. In case of an convoluted legacy DB this Oracle feature is essential.
Here are some more infos to Oracle query hints: https://docs.oracle.com/cd/B12037_01/server.101/b10752/hintsref.htm

[MaceWindu]

Yes, this is definitely one of features we want to implement.
It will need some research to gather information about hints in all databases we support, to design and implement proper API, so it will take some time.
I will add it to 3.0 roadmap

[filippobottega]

I'd like to emphasize that this feature is mandatory to change the optimizer behavior.
See The undocumented Oracle SQL materialize Hint

[filippobottega]

Hi @sdanyliv, do you know if there is a way to change the SQL generated by linq2db just before sending the query to the DB? I need a way to insert an hint to some select and I'd like to do it using regular expressions without waiting for the next linq2db release if possible.

[sdanyliv]

Try to override ExecuteReader and ExecuteReaderAsync in DataConnection and correct CommandText.

[filippobottega]

Thank you @sdanyliv for your suggestion. I'd like to share my code if someone else need to change the SQL used by DataConnection:
``` C#
using System.Data;
using System.Data.Common;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;

namespace DataModels.Wip
{
public partial class WipDB : LinqToDB.Data.DataConnection
{
protected override IDataReader ExecuteReader(IDbCommand command, CommandBehavior commandBehavior)
{
command.CommandText = Regex.Replace(command.CommandText, @"GetActivityLogStartBySfcs(\s)?((\s|.)?SELECT", "$0 /*+ materialize */");
return base.ExecuteReader(command, commandBehavior);
}

    protected override Task<DbDataReader> ExecuteReaderAsync(IDbCommand command, CommandBehavior commandBehavior, CancellationToken cancellationToken)
    {
        command.CommandText = Regex.Replace(command.CommandText, @"GetActivityLogStartBySfcs(\s)*?\((\s|.)*?SELECT", "$0 /*+ materialize */");
        return base.ExecuteReaderAsync(command, commandBehavior, cancellationToken);
    }
}

}
```
GetActivityLogStartBySfcs is the name of the Cte that I need to modify adding the /*+ materialize */ hint.