0

I am new to the database. I would like to implement pagination on large tables using rowid but what I found that rowid can be alphanumeric digits and not necessarily they are in any order so I wonder if we can use them to paginate the tables. The table from which I would like to get the records in pagination is very large table having more than 2 billion records and if we execute the query based on offset and length then that comes up with an error Ora1652 unable to extend temp segment in tablespace temp. So is there way to get the results in pagination using rowid.

Any help would be appreciated thank you in advance.

Edited:

Let's suppose I have a Table say LARGE_ROWS having ID and RANDOM two columns whereas ID is the primary key column.

SELECT /*+ ordered use_nl(p s) */
    *
FROM
    (
        SELECT
            ROWNUM RN,
            RD
        FROM
            (
                SELECT
                    ROWID RD
                FROM
                    large_rows
                ORDER BY
                    large_rows.id
            ) T
        WHERE
            ROWNUM < 1000
    ) P,
    large_rows S
WHERE
    RN > 900
    AND P.RD = S.ROWID;
3
  • When using rowid you will get the rows in the order they are stored in the table, so you can’t sort them any other way. Jun 29, 2019 at 9:20
  • 1
    I would say, the primary solution should be: Increase your TEMP tablespace Jun 30, 2019 at 15:23
  • Usually Nested Loops (NL) are intended for small tables. What happens when you remove the hint? Jun 30, 2019 at 15:26

1 Answer 1

1

You need to try following by yourself multiple times to see if you are getting any improvement in performance with the correct result.

I tried different approaches and Following is giving good performance in my case.

DROP TABLE STUDENT;

CREATE TABLE student (RNO NUMBER, NAME VARCHAR2(1000));

-- I have just added 2,000,000 records
INSERT INTO STUDENT
SELECT LEVEL, 'STUDENT - ' || LEVEL
FROM DUAL CONNECT BY LEVEL <= 2000000;


-- selecting the 10th page of length 100 each
SELECT /*+ ordered use_nl(p s) */
    *
FROM
    (
        SELECT
            ROWNUM RN,
            RD
        FROM
            (
                SELECT
                    ROWID RD
                FROM
                    STUDENT
                ORDER BY
                    RNO
            ) T
        WHERE
            ROWNUM < 1000
    ) P,
    STUDENT S
WHERE
    RN > 900
    AND P.RD = S.ROWID;

A Rowid is made by OOOOOOFFFBBBBBBRRR O is object id,F is file id,B is block id,R is row number. Starting from 11g some new mechanism is introduced. If the next rowid is in the same block with the current row, then the current block can be reused. Hence, "Consistent gets" increase.

I hope you will get direction in solving your issue.

Cheers!!

4
  • Thanks. Can you please edit your example based on my Table details? I do not understand two things in your example: Do I need to add a NUMBER type column to get this work? Can't it work with any Primary Key of the column, even it's not NUMBER type? Jun 29, 2019 at 13:20
  • Number datatype is not needed, You can use your primary keys instead of RNO of my example
    – Popeye
    Jul 1, 2019 at 5:34
  • Is order by necessary? Jul 1, 2019 at 12:30
  • Yes, Otherwise same data may appear in different pages (repetitive data)
    – Popeye
    Jul 1, 2019 at 12:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.