Oracle LIMIT and 1000 column restriction

Question

I have a SQL query that looks like this:

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

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

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.

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

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.

"*selecting over 1000 columns*", there seems to be something really strange about your data model. — , Nov 09 '12 at 19:53
@a_horse_with_no_name: If by strange, you mean that I have over 1000 pieces of discrete data about a single entity that are all relevant and necessary for my particular domain, but that this is extremely uncommon across the collection of all domains, then you're correct. Wholly unhelpful, but correct. — FtDRbwLXw6, Nov 09 '12 at 20:04
In 30 years of database design I have never seen an entity requiring over a 1000 columns. I wouldn't be surprised if your model could be optimized and thus solving the root cause of your problem rather than fighting symptoms — , Nov 09 '12 at 20:13
Is there a key on your table?. I'm guessing you could do pagination by joining against the same table, but selecting only the key column and your `ROW_NUMBER` and doing your filter afterwards. Doesn't sound very optimal, but is a way — Lamak, Nov 09 '12 at 20:20
Is there a reason you are using `ROW_NUMBER() OVER (ORDER BY NULL) rnum` instead of `ROWNUM rnum`? You might find that using the `rownum` pseudocolumn results in a simpler plan than the analytic `row_number()`. Neither expression imposes an order on the results and neither will guarantee stability of ordering from one page to the next. — Shannon Severance, Nov 09 '12 at 20:22
@ShannonSeverance: In reality, the query is ordered by a unique set of columns, not NULL (sorry for the confusion - I modified the pseudocode). I've experimented with some other expressions that didn't use analytics, but they all seem to run into the same column limit. — FtDRbwLXw6, Nov 09 '12 at 20:25
@Lamak: There are keys on all the tables, but I'm not sure I follow your suggestion. — FtDRbwLXw6, Nov 09 '12 at 20:27
This is very common question. Look at [here][1] [1]: http://stackoverflow.com/questions/11680364/oracle-faster-paging-query/11692804#11692804 — Alessandro Rossi, Nov 10 '12 at 14:21
@AlessandroRossi: I don't think you actually read my question. — FtDRbwLXw6, Nov 10 '12 at 16:41
@drrcknlsn Yes, you got it!! I really can't stand on the 1000 column limit, we're in the 2012!! It's very surprising that Oracle can't handle more than that, it must be a very old limitation still alive I suppose. Probably the best way to get out of your problem is to list the columns used in your query, if it is easily possible. — Alessandro Rossi, Nov 14 '12 at 14:36

score 3 · Answer 1 · edited Nov 09 '12 at 20:38

3

Okay, this will perform worse than what you were planning, but my point is that you could try pagination this way:

WITH CTE AS
(
    ... original SQL goes here ...
)

SELECT A.*
FROM CTE A
INNER JOIN (SELECT  YourKey,
                    ROW_NUMBER() OVER (ORDER BY ...) rnum
            FROM CTE) B
ON A.YourKey = B.YourKey
WHERE rnum BETWEEN 1 AND 10;

edited Nov 09 '12 at 20:38

answered Nov 09 '12 at 20:31

Lamak

69,480
12
108
116

I understand now, thanks for your answer. I tried this method and I'm still getting the same 1000 column limit error. I assume this is due to these queries using the joined data as a subquery (Oracle tries to create a temp view?), and so the column limit is being applied. – FtDRbwLXw6 Nov 09 '12 at 20:51
@drrcknlsn I see, then I would need to try with a table with 1000 columns myself to see if I come up with something – Lamak Nov 09 '12 at 20:57
Just for clarification, none of my tables have over 1000 columns (they're all much less). I'm just joining a bunch of different tables and the total of all the columns across all the tables is over 1000. – FtDRbwLXw6 Nov 09 '12 at 21:01

score 2 · Accepted Answer · answered Nov 09 '12 at 20:50

2

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;

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

BBB = pagesize. AAA = start point

answered Nov 09 '12 at 20:50

DazzaL

21,638
3
49
57

For clarification, is `f.c1` in this example supposed to be the key field? And is the `order by whatever` supposed to be where I would put the columns that the result set should be ordered by? I assumed so, but the results I'm getting are not ordered by what I put there. I have records dated today, and the first `order by` column I specified was the modification date, descending, but the results I'm seeing are ordered starting in 2011. I assume this somehow has to do with the inner most `order by`. – FtDRbwLXw6 Nov 09 '12 at 21:08
the main order by is in the inner sql `order by f.c1` put whatever you want there (the outer one should probably logically be the same order by) – DazzaL Nov 09 '12 at 21:14
yes f.c1 = f2.a1 is just the join key (you may have a few). i put two tables there as you said in your example there were many tables. so put all your tables that you'd want there..join them all up , order them however you see fit..all in that inner sql. the first rows hint is to tell oracle that you only are interested in the first top few results (to disuade it from a full scan it can avoid it). – DazzaL Nov 09 '12 at 21:17

score -1 · Answer 3 · answered Nov 09 '12 at 19:52

-1

Is the problem pagination or just returning the first 10 rows? If it is the latter, you can do:

SELECT foo "c0",
       bar "c1",
       baz "c2",
       ...
FROM   some_table
WHERE  ... and
       rownum <= 10

answered Nov 09 '12 at 19:52

Gordon Linoff

1,242,037
58
646
786

1

This is for pagination, so I need to be able to return a page of results at a time, beginning at an arbitrary offset, not just the first 10 every time. – FtDRbwLXw6 Nov 09 '12 at 19:59

Oracle LIMIT and 1000 column restriction

3 Answers3

Linked

Related