Single Row table performs better with WHERE clause!!!???

Yeah I know it sounds funny. 

But its true.

Had a customer who was reporting slowness in the system and some one performed the AWR reports and found that this sql is doing tremendous Logical I/Os in the system.

The SQL is very simple. As said, this table will only have one row at any given time (mind you no inserts and deletes also) and the statement is “select column_name from table_name;”.

So, the request comes to change the SQL to have a where condition so that Oracle can do Index scans instead of FTS!!!

Answer to that request: a very big NO

So started pulling hairs to see why there are so many Logical I/O’s on a single row table which is not even a kilo byte size… almost 1025 buffer gets per execution and this sql runs hundred/thousand times in an hour so.

Since it was doing 1025 buffer gets per execution, they tested with a where clause and the buffer gets value drops down rapidly and thus the request comes in to add where clause.

Well, the mystery is resolved.

This particular table usage shows 1025 USED BLOCKS instead of 1 and thus FTS is doing 1025 buffer gets every time the data is read.

Why the usage is shows 1025??? No answer. Well, pretty simple reason. Some one must of performed heavy insert and then deleted the rows by keeping only the required row and thus the high water mark stays at 1025.

Solution provide was: take the backup up of the record and insert it back by truncating the table and then run the stats to see if FTS still does 1025 buffer gets.

Hurray, it only does 5 now…. Yeah… the problem is resolved.

So you still wants add where clause to a single row table SQL? Naa…. I guess we are fine….