Query not using Index search (HELP)

[Gopal]

Hi:

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.

Giri

[jmodic]

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 ..."

HTH,