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?
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments