2012/07/31

Query empty table takes long time

If you encounter the issue where you simply query a table using 'select * from ' and it takes a long time only to return 0 row, you may have high water mark issue. In some table where at one point it had thousands of rows in it and then was cleaned up by using delete command, the high water mark stays. This means Oracle will scans all the blocks under high water mark in a full table scan, which could be amount of work. To resolve, truncate the table so Oracle does not have to scan all those blocks. 

A easy approach to identify this issue is to do a trace and from the trace file you can easily tell how much physical or consistent read Oracle has done for the query. If there is a lot of reads involved but the query returns 0 row, then it's clearly that Oracle does something unnecessary.