A m Z o n e

Select takes 10 minutes to retrieve 1 record from a table having 1 record only

Interesting select, what could be the possible reason for a select to take 10 minutes for just one record in the table?

   select * from am100; 

   records retrieved: 1 
   total records in the table: 1 
   time taken: 10 minutes!!!  

Reason: Insert lakhs of records into a table. Now if a full table scan is done oracle will search all the blocks occupied. Delete all the records and insert just one record. Now if a full table scan is done, oracle will scan all the blocks, including those occupied previously by lakhs of records!!! A full table scan is an access path in which Oracle reads all the data blocks for a specific table. The blocks are read from the first block to the block marked the "high water mark" block in the table. The "high water mark" is the highest block that was written for that table. Note that when using the delete command to delete rows in a table, the space previously occupied is not released. This means that if a table contained large amounts of data which were deleted, and the table itself was not reorganized (i.e. data was not unloaded and reloaded) then the full table scan will still read all the data blocks (including those that formerly contained data, but are now empty).

Press the Back button of you Browser to go to previous page
Home