I have a problem with oracle 12c when I run this query:

select *  from tabl1 join  tabl2 on tabl2.fk1 = tabl1.id where Rownum < 100

it take 120ms but when I run

select *  from tabl1 join  tabl2 on tabl2.fk1 = tabl1.id where Rownum < (100 15)

it take about 6 seconds!! anyone knows why it happen?

CodePudding user response：

When the query is filtered by ID only, Oracle reads the data from memory space, which is the part of the memory that cached the data. When the filter has computational operations, the Oracle query optimization engine reads the data from the hard disk and then filters data. So when the second query is done, a lot more time is consumed.

for more information please read: https://docs.oracle.com/database/121/TGSQL/tgsql_pt_opt.htm#TGSQL174