Taking time when we query for a table data

Posted on 2011-10-27
Last Modified: 2012-05-12
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.
Question by:ajaybelde
    LVL 73

    Accepted Solution

    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
    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    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:
    LVL 73

    Assisted Solution

    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

    Author Closing Comment

    Thank for help, the size was large so i did reset the HWM. noe it work fine.

    Thank you

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now