Search

What are the limits on the number of records returned by a ROQL query?

Environment:

Oracle B2C Service, all supported versions

Resolution:

Depending on whether the query is executed on the reporting (replication) database or on the operational database, the limits for the number of results vary:

Operational database: 20,000 records per query
Reporting database: 100,000 records per query

These limits cannot be increased through the LIMIT clause. For example, if you run a query on the operational database and specify "LIMIT 20001", the maximum amount of records returned is still 20,000.

If the requirement is to pull more than 20,000 records (or 100,000 for the reporting database) through a single query, the LIMIT and OFFSET clauses need to be used. For example, if the query returns a total of 3000 results and you want to get the results in 3 batches of 1000, you would do:

SELECT ... FROM ... WHERE ... LIMIT 1000
SELECT ... FROM ... WHERE ... LIMIT 1000 OFFSET 1000
SELECT ... FROM ... WHERE ... LIMIT 1000 OFFSET 2000

You can specify the database you want the query to run on through the USE clause:

USE REPORT; SELECT ...
USE OPERATIONAL; SELECT ...

Additional information can be found in the ROQL sections of the public APIs documentation:
Answer ID 5169: Technical Documentation and Sample Code