How to avoid this nested query

Question

I have a category table and need to do pagination for the same. I am using JSF, and query in XML. Is there any way to optimize this query better.

SELECT
    *
FROM
    (
        SELECT
            categories.*,
            ROWNUM rn
        FROM
            (
                SELECT
                    *
                FROM
                    (
                        SELECT
                            *
                        FROM
                           categories
                        WHERE
                          name LIKE 'FAMILY%'
                        ORDER BY
                            id
                    )
                WHERE
                    ROWNUM < 21
            ) categories
    )
WHERE
    rn > 10;

Which version of Oracle are you using – George Joseph Feb 06 '20 at 06:34 — George Joseph, Feb 06 '20 at 06:34

ekochergin · Answer 1 · 2020-02-06T07:12:24.047

2

On the database version 12 an newer there are offset and fetch features available, so your query might be looking like the following

SELECT *
  FROM categories
 WHERE name LIKE 'FAMILY%'
 ORDER BY id
OFFSET 10 rows -- skips first 10 rows of the result
 FETCH 11 rows only;

Try that one please

The DB version can be checked using the following query

select * from v$version;

edited Feb 06 '20 at 07:12

answered Feb 06 '20 at 06:49

ekochergin

4,109
2
12
19

1

It is supported from oracle 12c and higher – Popeye Feb 06 '20 at 06:55
1

@Tejash damn you're right. Updated the answer. Thanks! – ekochergin Feb 06 '20 at 06:58
It amazes me that Oracle waited until 12c to provide basic OFFSET/LIMIT features. – patrick3853 Feb 06 '20 at 07:00

score -1 · Answer 2 · answered Feb 06 '20 at 06:37

-1

Are you looking for a query like this?

SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY id) ROWNUM,* FROM categories
WHERE name LIKE 'FAMILY%') as categories where ROWNUM between 10 and 21

answered Feb 06 '20 at 06:37

Sreepu

123
1
1
14

this worked for me but between 1 to 10+ only data is populated, if i start from between 2 to 20 empty data is populated. Is there any issue with ROWNUM? – jef Feb 06 '20 at 08:23
This query won't compile in Oracle for several reasons: `ROWNUM` is a pseudo-column and you cannot use it as an alias; using `AS` before a table/sub-query alias is syntactically invalid; and you need to prefix the `*` with the table name/alias when you are also selecting other columns. – MT0 Feb 06 '20 at 08:26

How to avoid this nested query

2 Answers2