如何在 SubSonic 2.2 中执行 Oracle top-n（分页）查询？

Question

（免责声明：出于安全原因，我在此处更改/混淆了一些变量/表/列名称。如果某些内容看起来有点不对劲，请原谅我。）

我正在构建一个Oracle 10g 数据库的前端，我正在尝试获取分页数据。除了分页之外，以下 SubSonic 2.2 代码按照我想要的顺序为我提供了我想要的内容：

var q = new Select()
  .From(AppDb.MyTable.Schema)
  .Where(AppDb.MyTable.DisabledDateColumn).IsNull()
  .OrderDesc(AppDb.MyTable.CreatedDateColumn.ColumnName)

System.Console.Out.Writeline(q.BuildStatement());

这产生了以下 SQL：

SELECT
    MYSCHEMA.MYTABLE.ID,
    MYSCHEMA.MYTABLE.DISABLED_DATE
FROM
    MYSCHEMA.MYTABLE
WHERE
    MYSCHEMA.MYTABLE.DISABLED_DATE IS NULL
ORDER BY
    CREATED_DATE DESC

然后我尝试引入分页：

var q = new Select()
  .From(AppDb.MyTable.Schema)
  .Where(AppDb.MyTable.DisabledDateColumn).IsNull()
  .OrderDesc(AppDb.MyTable.CreatedDateColumn.ColumnName)
  .Paged(0, 10);

它消除了我的 WHERE 和 ORDER BY 子句：

SELECT * FROM (
    SELECT
        MYSCHEMA.MYTABLE.ID,
        MYSCHEMA.MYTABLE.DISABLED_DATE,
        ROWNUM as row_number
    FROM
        MYSCHEMA.MYTABLE
)
WHERE
    row_number BETWEEN 1 AND 10

我是品牌SubSonic 是新手，所以我不知道这是否是一个错误，或者我只是没有按照首选方式执行此操作，或者 Oracle 是否要求以不同的、更以 Oracle 为中心的方式完成此操作。 （Oracle 似乎要求所有内容都如此。）

我想要的是，如果不是很明显，我想要的是每个后续页面都包含 10 个下一个最近创建的非禁用记录。我怎样才能在 SubSonic 2.2 中做到这一点？

Answer 1

看起来您的查询没有生成与当前 Oracle Data Provider for SubSonic 应该。以下是相关代码（从第 852 行开始）：

    if(qry.PageIndex < 0)
        query = String.Format("{0} {1} FROM {2}.{3} {4} {5}",select ,columns, table.SchemaName, table.Name, where, order);
    else
    {
        int start = qry.PageIndex * qry.PageSize;
        int end = (qry.PageIndex + 1) * qry.PageSize;

        const string cteFormat =
                    "WITH pagedtable AS (SELECT {0}, ROW_NUMBER () OVER ({1}) AS rowindex FROM {2}.{3} {4}) SELECT {5}, rowindex FROM pagedtable WHERE rowindex >= {6} AND rowindex < {7} ORDER BY rowindex";
        query = string.Format(cteFormat, columns, order,table.SchemaName, table.Name, where, columns.Replace(table.Name + ".", String.Empty), start, end);
    }
    return query;

也许更新当前源代码就可以解决问题。如果实际提供程序出现问题，您可以将其与 SqlDataProvider 进行比较，以获取可能存在问题的提示。

Answer 2

我在使用 NET 2.0 和 Oracle 9i R2 的环境中，遇到了同样的问题。我正在使用 SubSonic 2.2。

我从 GitHub 下载了源代码，这就是我发现的：

@ranomore (OracleDataProvider.GetSelectSql()) 引用的代码仅在使用 SubSonic.Query 对象时才会被调用。由于 OP 和我自己使用从更新且更强大的 SubSonic.SqlQuery 对象派生的 Select 对象，因此 OracleDataProvider.GetSelectSql() 永远不会被调用。相反，OracleGenerator.BuildPagedSelectStatement()被调用并生成您在 OP 发布的 SQL。此代码存在错误，因为它从未将 WHERE 和 ORDER BY 子句添加到其最终生成的分页查询中。

我用基于 ANSISqlGenerator.BuildSelectStatement() 的内容替换了 BuildPagedSelectStatement() 的内容：

public override string BuildPagedSelectStatement()
{
    int startnum = query.PageSize * query.CurrentPage + 1;
    int endnum = query.PageSize * query.CurrentPage + query.PageSize;
    string orderBy = String.Empty;

    if (this.query.OrderBys.Count > 0)
        orderBy = GenerateOrderBy();

    //The ROW_NUMBER() function in Oracle requires an ORDER BY clause.
    //In case one is not specified, we need to halt and inform the caller.
    if(orderBy.Equals(String.Empty))
        throw new ArgumentException("There is no column specified for the ORDER BY clause", "OrderBys");

    System.Text.StringBuilder sql = new System.Text.StringBuilder();

    //Build the command string
    sql.Append("WITH pagedtable AS (");
    sql.Append(GenerateCommandLine());

    //Since this class is for Oracle-specific SQL, we can add a hint
    //which should help pagination queries return rows more quickly.
    //AFAIK, this is only valid for Oracle 9i or newer.
    sql.Replace("SELECT", "SELECT /*+ first_rows('" + query.PageSize + "') */");

    sql.Append(", ROW_NUMBER () OVER (");
    sql.Append(orderBy);
    sql.Append(") AS rowindex ");
    sql.Append(Environment.NewLine);
    sql.Append(GenerateFromList());
    sql.Append(GenerateJoins());

    sql.Append(GenerateWhere());

    if (query.Aggregates.Count > 0)
    {
        sql.Append(GenerateGroupBy());
        sql.Append(Environment.NewLine);
        sql.Append(GenerateHaving());
    }

    sql.Append(") SELECT * FROM pagedtable WHERE rowindex >= ");
    sql.Append(startnum);
    sql.Append(" AND rowindex < ");
    sql.Append(endnum);
    sql.Append(" ORDER BY rowindex");

    return sql.ToString();
}

上面的内容对我有用。希望这对其他人有帮助！

Answer 3

如果内部 SQL 由第一个语句（包括 where 和 order by）组成，Oracle 就可以使用 Top-N 查询。

所以，我想说 Oracle 没有特定的理由来忽略它们。

从未使用过亚音速，不知道是否需要在那里做不同的事情。

就性能而言，DISABLED_DATE、CREATED_DATE 上的索引应该可以解决问题（请参阅：http://blog.fatalmind.com/2010/07/30/analytic-top-n-queries/）。