Oracle LIMIT 和 1000 列限制 [英] Oracle LIMIT and 1000 column restriction

问题描述

我有一个如下所示的 SQL 查询:

I have a SQL query that looks like this:

SELECT foo "c0",
       bar "c1",
       baz "c2",
       ...
FROM   some_table
WHERE  ...

为了应用限制，并且仅从该查询返回记录的子集，我使用以下包装 SQL:

In order to apply a limit, and only return a subset of records from this query, I use the following wrapper SQL:

SELECT *
FROM   (
    SELECT t.*,
           ROW_NUMBER() OVER (ORDER BY ...) rnum
    FROM   (
        ... original SQL goes here ...
    ) t
)
WHERE rnum BETWEEN 1 AND 10

我的问题是原始查询选择了 1000 多列跨大量连接到其他表的列.Oracle 对每个表或视图有 1000 列的内部限制，显然我用来限制结果集的包装 SQL 正在创建一个临时视图，该限制应用于该视图，导致整个事情失败.

My problem is that the original query is selecting over 1000 columns across a large number of joins to other tables. Oracle has an internal limit of 1000 columns per table or view, and apparently the wrapper SQL I'm using to limit the result set is creating a temporary view to which this limit is applied, causing the whole thing to fail.

是否有另一种分页方法不会创建这样的视图，或者不会受到 1000 列限制的影响?

Is there another method of pagination that doesn't create such a view, or wouldn't otherwise be affected by the 1000 column limit?

我对将工作分解成块而不是选择 > 1000 列等的建议不感兴趣，因为我已经完全了解所有这些方法.

I'm not interested in suggestions to break the work up into chunks, not select > 1000 columns, etc., as I'm already fully aware of all of these methods.

推荐答案

你不能拥有 1000 多列的视图，所以作弊一点.

you cant have a view with 1000+ columns, so cheat a little.

select *
  from foo f, foo2 f2
 where (f.rowid, f2.rowid) in (select r, r2
                                 from (select r, r2, rownum rn
                                         from (select /*+ first_rows */ f.rowid r, f2.rowid r2
                                                 from foo f, foo2 f2
                                                where f.c1 = f2.a1 
                                                  and f.c2 = '1'
                                                order by f.c1))
                                where rn >= AAA
                                  and rownum <= BBB)


order by whatever;

现在把任何 where 子句放在最里面的位(例如我把 f.c1 = '1').

now put any where clauses in the innermost bit (eg i put f.c1 = '1').

BBB = 页面大小.AAA = 起点

BBB = pagesize. AAA = start point

这篇关于Oracle LIMIT 和 1000 列限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！