CODEWITHSUNDEEP
oracle-databaseoracle11g
Inquisitor Shm
Inquisitor Shm

Reputation: 1591

Oracle SELECT * FROM LARGE_TABLE - takes minutes to respond

So I have a simple table with 5 or so columns, one of which is a clob containing some JSON data.

I am running

  1. SELECT * FROM BIG_TABLE
  2. SELECT * FROM BIG_TABLE WHERE ROWNUM < 2
  3. SELECT * FROM BIG_TABLE WHERE ROWNUM = 1
  4. SELECT * FROM BIG_TABLE WHERE ID=x

I expect that any fractionally intelligent relational database would return the data immediately. We are not imposing order by/group by clauses, so why not return the data as and when you find it?

Of all the forms of SELECT statements above, only 4. returned in a sub-second manner. This is unexpected for 1-3 which are returning between 1 and 10 minutes before the query shows any responses in SQL Developer. SQL Developer has the standard SQL Array Fetch Size of 50 (JDBC Fetch size of 50 rows) so at a minimum, it is taking 1-10 minutes to return 50 rows from a simple table with no joins on a super high-performance RAC cluster backed by fancy 4-tiered EMC disk subsystem.

Explain plans show a table scan. Fine, but why should I wait 1-10 minutes for the results with rownum in the WHERE clause?

What is going on here?

Upvotes: 0

Views: 640

Answers (1)

Inquisitor Shm
Inquisitor Shm

Reputation: 1591

OK - I found the issue. ROWNUM does not operate like I thought it did and in the code above it never stops the full table scan.

This is because:

RowNum is assigned during the predicate operation (where clause evaluation) and incremented afterwards, i.e.: your row makes it into the result set and then gets rownum assigned.

In order to filter by rownum you need to already have it exist, something like ...

SELECT * FROM (SELECT * FROM BIG_TABLE) WHERE ROWNUM < 1

In effect what this means is that there is no way to filter out the top 5 rows from a table without having first selected the entire table if no other filter criteria are involved.

I solved my problem like this...

    SELECT * FROM (SELECT * FROM BIG_TABLE WHERE 
  DATE_COL BETWEEN :Date1 AND :Date2) WHERE ROWNUM < :x;

Upvotes: 1

Related Questions