Re: nonefficient count(*)

From: Vadim Grepan <kezal_at_mail.ru>
Date: Fri, 29 Jun 2001 11:29:39 +0400
Message-ID: <3B3C2E63.790DA5CA@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.

Jonathan Lewis wrote:

> Your unhinted execution plan seems to indicate
> that the table is not analyzed - can you repeat
> this one with the table and its indexes analyzed
> so that we can see the cost that Oracle THINKS
> a full scan will be.
>
> --
> 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 <3B3B4544.89E4C660_at_mail.ru>...
> >Hello All!
> >
> > I have the table with PK (pk_liquid_product on VARCHAR(12)),
> > non-unique index (ndx_liquid_product__artist_id on NUMBER(11)) and
> >around 30 non-indexed fields
> > (VARCHAR2, NUMBER etc). Total records are around 150.000.
> >
> > I've got following observation, optimizer doesn't use PK-index
> >preferring full-scan.
> >Moreover I cannot make to use PK-index even by hint. It's true for
> >analyzed and non-analyzed table.
> >Are there any explanation and solutions? TFHAOT
> >
> >Oracle 8.1.7.1, Sun SPARC
> >
> >### 1 ######
> >SQL> select count(*) from liquid_product;
> >
> >Elapsed: 00:00:03.03
> >Execution Plan
> >----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE
> > 1 0 SORT (AGGREGATE)
> > 2 1 TABLE ACCESS (FULL) OF 'LIQUID_PRODUCT'
> >
Received on Fri Jun 29 2001 - 02:29:39 CDT