Limit rows examined in Oracle-CodePudding

My table has millions of records. In this query below, can I make Oracle 12c examine the first X rows only instead of doing a full table scan?

The value of X, I imagine should be Offset Fetch Next , so in this case 15

SELECT * FROM table OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;

Thanks in advance

CodePudding user response：

Although your FETCH clause may use a full table scan, Oracle will still only fetch the first X rows from the table.

In the following example, the "TABLE ACCESS FULL" operation does start to read the entire table, but it gets cutoff part of the way through by the "WINDOW NOSORT STOPKEY" operation. Not all full table scans actually scan the full table. You would see similar behavior if your code ended with WHERE ROWNUM <= 50.

CREATE TABLE some_table AS SELECT * FROM all_objects;

EXPLAIN PLAN FOR SELECT * FROM some_table OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;

SELECT * FROM TABLE(dbms_xplan.display);

Plan hash value: 2559837639
 
-------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |    15 |  7410 |     2   (0)| 00:00:01 |
|*  1 |  VIEW                  |            |    15 |  7410 |     2   (0)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY|            |    15 |  2010 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL   | SOME_TABLE |    15 |  2010 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=15 AND 
              "from$_subquery$_002"."rowlimit_$$_rownumber">5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY  NULL )<=15)

The performance implications get more complicated if you also want to order the results. If that is the case, you may want to post the full query and execution plan.

CodePudding user response：

Since Oracle as I know don't provide something like limit or top you can created by yourself like the following: what is happening here, the inner query gets all the first 10 records and the outer query get those, you can still use any clauses like where or order or any others

SELECT * FROM (
    SELECT * FROM Customers WHERE CustomerID <= 10 ORDER BY CustomerID
)

The full article will be found about this topic here at Oracle-Fetch

I am using Online Oracle so you can try it from your end, please let me know if you still have a problem.