sql - Why is there a significant time difference between two similar ROWNUM queries in Oracle? - Stack Overflow

admin管理员组

文章数量:1332373

I have two queries in Oracle that seem very similar but show a significant difference in execution time.

Here are the queries:

-- Query 1: ROWNUM with ORDER BY
SELECT *
FROM coupang.PRODUCT as p
JOIN ( -- Subquery 1
    SELECT id
    FROM ( -- Subquery 2
        SELECT id, ROWNUM AS rn
        FROM coupang.PRODUCT
        ORDER BY id
    )
    WHERE rn BETWEEN 5000000 AND 5000030
) AS t
ON p.id = t.id;

-- Query 2: ROWNUM without ORDER BY
SELECT *
FROM coupang.PRODUCT as p
JOIN ( -- Subquery 1
    SELECT id
    FROM ( -- Subquery 2
        SELECT id, ROWNUM AS rn
        FROM coupang.PRODUCT
    )
    WHERE rn BETWEEN 5000000 AND 5000030
) AS t
ON p.id = t.id;

Key Observations:

Both queries use for filtering rows.ROWNUM The first query includes an clause in Subquery 2, while the second query does not.ORDER BY id The column is indexed.id Despite the similarity in structure and the presence of an index on the column, the execution time of the first query is significantly longer than the second query.id

What I Have Noticed:

I initially thought the operation would take a similar amount of time for both queries because the column is indexed.ORDER BYid When I measured the execution time for Subquery 2, it indeed showed similar performance in both queries. However, the execution time for the entire query differs significantly.

My Questions:

Why does the in Subquery 2 cause such a significant performance difference in the entire query, even when the column is indexed?ORDER BYid How can I optimize the first query to reduce the execution time while keeping the ?ORDER BY

Additional Context:

The table has tens of millions of rows. The execution plan for the first query shows a operation in Subquery 2.SORT ORDER BY I want to understand why the full query performance differs so much when the subquery performance is similar.

I executed an EXPLAIN PLAN query in Oracle, but the result only provides a simplified output regarding execution time.

The results show differences in the execution of an INDEX FAST FULL SCAN. Specifically, Query 1 takes more time than Query 2, despite their structural similarity.

I have two queries in Oracle that seem very similar but show a significant difference in execution time.

Here are the queries:

-- Query 1: ROWNUM with ORDER BY
SELECT *
FROM coupang.PRODUCT as p
JOIN ( -- Subquery 1
    SELECT id
    FROM ( -- Subquery 2
        SELECT id, ROWNUM AS rn
        FROM coupang.PRODUCT
        ORDER BY id
    )
    WHERE rn BETWEEN 5000000 AND 5000030
) AS t
ON p.id = t.id;

-- Query 2: ROWNUM without ORDER BY
SELECT *
FROM coupang.PRODUCT as p
JOIN ( -- Subquery 1
    SELECT id
    FROM ( -- Subquery 2
        SELECT id, ROWNUM AS rn
        FROM coupang.PRODUCT
    )
    WHERE rn BETWEEN 5000000 AND 5000030
) AS t
ON p.id = t.id;

Key Observations:

Both queries use for filtering rows.ROWNUM The first query includes an clause in Subquery 2, while the second query does not.ORDER BY id The column is indexed.id Despite the similarity in structure and the presence of an index on the column, the execution time of the first query is significantly longer than the second query.id

What I Have Noticed:

I initially thought the operation would take a similar amount of time for both queries because the column is indexed.ORDER BYid When I measured the execution time for Subquery 2, it indeed showed similar performance in both queries. However, the execution time for the entire query differs significantly.

My Questions:

Why does the in Subquery 2 cause such a significant performance difference in the entire query, even when the column is indexed?ORDER BYid How can I optimize the first query to reduce the execution time while keeping the ?ORDER BY

Additional Context:

The table has tens of millions of rows. The execution plan for the first query shows a operation in Subquery 2.SORT ORDER BY I want to understand why the full query performance differs so much when the subquery performance is similar.

I executed an EXPLAIN PLAN query in Oracle, but the result only provides a simplified output regarding execution time.

The results show differences in the execution of an INDEX FAST FULL SCAN. Specifically, Query 1 takes more time than Query 2, despite their structural similarity.

• sql
• database
• oracle-database

Share Follow edited Nov 22, 2024 at 7:20 jps 22.6k1616 gold badges8888 silver badges106106 bronze badges asked Nov 22, 2024 at 6:06 CoDuckCoDuck 2111 silver badge11 bronze badge 4

• 1 An ORDER BY on millions of rows may have a similar/slightly different execution plan, but the work the database needs to do to actually retrieve these millions of rows and put them in the correct order is a lot more than just "return any and all rows in the order you have read them from your database files". Hope this makes sense. – Martin Schapendonk Commented Nov 22, 2024 at 8:08
• Please edit the question and include a minimal reproducible example with: the CREATE TABLE and CREATE INDEX statements and any relevant constraints for your table; and the EXPLAIN PLAN for your two queries. – MT0 Commented Nov 22, 2024 at 9:16
• Very similar? Doesn't different result count? – jarlh Commented Nov 22, 2024 at 12:10
• 1 On a sidenote: This syntax should not be used anymore. Subquery results are unordered by definition, but you rely on getting them in the order of IDs. You can do so, because Oracle guarantees to keep the order when using ROWNUM. This is not standard compliant, however. Oracle did so, because they introduced ROWNUM way before featuring ROW_NUMBER() OVER (...) and FETCH FIRST/NEXT ROWS that we use nowadays. Nowadays you should use the standard SQL features instead. – Thorsten Kettner Commented Nov 22, 2024 at 16:11

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

The fastest and easiest solution should be the row_limiting_clause clause:

SELECT *
FROM coupang.PRODUCT
ORDER BY id
OFFSET 5000000 ROWS FETCH NEXT 30 ROWS WITH TIES;

Your premise is wrong - subquery performance is not similar. It only looks similar, probably because when you tested it you didn't retrieve all the millions of rows in the table. Whatever tool you used, such as Oracle SQL Developer, by default only retrieves the first 100 rows or so. For 100 rows, the overhead of your IDE or network will overshadow the the tiny performance difference between walking a b-tree index 100 times versus grabbing the first 100 random rows found from a full table scan.

Using ROWNUM with an ORDER BY to create top-N reporting will incur a relatively large cost eventually. But you may not see that full cost when you only run tiny pieces of the query.

本文标签： sqlWhy is there a significant time difference between two similar ROWNUM queries in OracleStack Overflow