CODEWITHSUNDEEP
oracle-databaserownum
baba26
baba26

Reputation: 949

rownum very slow after a particular value

I am running a query in Oracle SQL developer which looks something like this :

select * from dummy_table where col1 < 10 and col2 < 20 and col3 < 40 and rownum <= x

The query takes around 3 seconds and returns x rows if the value of x is <= 12.

But if replace x by anything greater than 12, the query takes more than 7 seconds and returns only 12 results ( in other words, there are only 12 rows satisfying the where clause ) .

Why is rownum behaving like this ? I was expecting this query to take almost same time if value of x is changed from 12 to 13.

Edit : Another thing which I noticed is that there is a composite index on col1,col2 and col3. If I remove the index ( or disable it using a hint ) , the query runs quite fast.

Upvotes: 0

Views: 771

Answers (1)

Aleksej
Aleksej

Reputation: 22949

It's difficult to give a complete explanation without knowing the table structure, the indexes, etc.

However, to keep it simple, if your table only has 12 rows matching your condition, asking for the first 12 rows means that Oracle simply looks for 12 rows and returns them, no matter the number of rows that do not match your condition.

If you ask for, say, 13 rows, Orace needs to scan the whole table, to check if a 13rd row exists.

So, without indexes and hints, asking for the first 13 rows where only 12 exist may need a full table scan, and this can be slow.

Please consider this as a very simplified explanation, not considering indexes, cache, hints. For example, we're not considering that even checking the performance of a query by simply running it may be misleading, because Oracle may use cache, and you can have better performance after the first run.

Upvotes: 3

Related Questions