Taking time when we query for a table data

Hi all

I have an issue with one table in my database. It does not have any data in it but when i try to query like say
select * from that table, its taking me some time. many i know why it is doing so and what should be done to reduce this. I dont have this proble on other table. It is happening just for one table. please help me out on this.


Thanks in advance.
ajaybeldeAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
you probably have a lot of empty data blocks in the table.

When you do select * from big_empty_table.

Oracle must still scan all of the blocks looking for rows.

if the table "really" is empty,  try  "truncate table  YOUR_TABLE"


the truncate will move the high water mark of the table back to the beginning so the scan won't need to look through all of the empty blocks
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I agree with sdstuber.

The reason behind this is called the High Water Mark (HWM).  Even with no data, Oracle searches ALL blocks allocated to the object.

truncate and some other commands will lower the HWM.

Refer to the docs for information about the HWM:

http://download.oracle.com/docs/cd/E11882_01/server.112/e25789/logical.htm#CNCPT89022
0
 
sdstuberConnect With a Mentor Commented:
also,  if you have lots of storage space allocated to the table and you think you're likely to fill it up again  you might want to add the "reuse storage" clause


truncate table your_table  reuse storage;


by default the truncate will deallocate extra blocks
0
 
ajaybeldeAuthor Commented:
Thank for help, the size was large so i did reset the HWM. noe it work fine.

Thank you
0
All Courses

From novice to tech pro — start learning today.