Re: nonefficient count(*)

Vadim,

    select num_rows from user_table
    where table_name = 'LIQUID_PRODUCT';

I think you will find that NUM_ROWS will be blank. The command

    analyze table XXX compute statistics     for all indexes

does NOT compute statistics for the table, it restricts itself to analyzing JUST the indexes. (I believe there has been some variation in this behaviour over the different versions of Oracle).

In you case, this means that Oracle knows the indexes have a reasonably large number of rows, but thinks the table is tiny - so uses a tablescan.

Either of the following will analyze the table AND its indexes:

    analyze table XXX compute statistics;

or, to be redundant but totally unambiguous

    analyze table XXX compute statistics     for table
    for all indexes;

The reason for not choosing the PK index is perhaps a matter of luck - if the cost for two different indexes is the same, Oracle probably has some arbitrary rule to decide which one to use - e.g. last one analyzed, last one created, first one hit when scanning the dictionary cache, etc.

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html






Vadim Grepan wrote in message <3B3C2E63.790DA5CA_at_mail.ru>...


>Hello Jonathan!

>

>  Thanks for your answer.

>  Usually I analyze tables with clause ' .. compute statistics for all

>indexes'. In this case I should use

>simple  ' .. compute statistics'. Nevertheless PK-index are not using even

>with hint. I can suppose that

>the second index is more efficient but using hist have to force apply

>choosed index I thought

>

>Rgds, Vadim Grepan

>-------------------------

>Moscow, Russia

>

>SQL> analyze table liquid_product compute statistics for all indexes;

>

>Table analyzed.

>