DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2000

    I have a table with unique index built. When I do a select count(*) on this table, it always goes through a full table scan.

    I have verified that the table is valid. Can some one give me some ideas as to where the problem would be.

    Looks like this is true on all the tables I have created so far.

    I am running Oracle EE 8.1.6.

    Thanks for your help in advance.


  2. #2
    Join Date
    Dec 2000
    Ljubljana, Slovenia
    1. It is not necessary that using index with COUNT(*) FROM will be faster than without index.

    2. Oracle can't use UNIQUE index for COUNT(*) unless indexed columns are also NOT NULL.

    3. Analyze your table and index and oracle might change it's mind and start using index for your query.

    4. It is very unlikely oracle will use an index if there is no WHERE condition in your query. Either provide one (like "x > 0" if you know all of your values in X are greater than 0) or suggest oracle to use index with hint ("SELECT /*+ index */ COUNT(*) FROM ..."

    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width