Not if there is a smaller structure that can be
guaranteed to contain and entry for every row
in the table, i.e. a bitmap index or an index
including a NOT NULL column.
--
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
Andy Collins wrote in message <993757204.182483_at_dionysos>...
>I'm sure that I must be missing the point here but..... if you select count
>(*) from table_name surely a full table scan must be performed?
>
>Andy.
>
>Vadim Grepan <kezal_at_mail.ru> wrote in message
>news: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'
>>
>> Statistics
>> ----------------------------------------------------------
>> 3197 consistent gets
>> 3132 physical reads
>>
>>
>> ### 2 ######
>> SQL> select /*+ INDEX (pk_liquid_product) */ count(*) from
>> liquid_product;
>>
>> Elapsed: 00:00:01.92
>> Execution Plan
>> ----------------------------------------------------------
>> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)
>> 1 0 SORT (AGGREGATE)
>> 2 1 INDEX (FAST FULL SCAN) OF 'NDX_LIQUID_PRODUCT__ARTIST_ID
>> ' (NON-UNIQUE) (Cost=4 Card=255332)
>>
>> Statistics
>> ----------------------------------------------------------
>> 344 consistent gets
>> 345 physical reads
>>
>> ### 3 ########
>> SQL> select /*+ INDEX (ndx_liquid_product__artist_id) */ count(*) from
>> liquid_product;
>>
>> Elapsed: 00:00:01.30
>> Execution Plan
>> ----------------------------------------------------------
>> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)
>> 1 0 SORT (AGGREGATE)
>> 2 1 INDEX (FAST FULL SCAN) OF 'NDX_LIQUID_PRODUCT__ARTIST_ID
>> ' (NON-UNIQUE) (Cost=4 Card=255332)
>>
>> Statistics
>> ----------------------------------------------------------
>> 344 consistent gets
>> 345 physical reads
>>
>>
>> Rgds, Vadim Grepan
>> --------------------------------
>> Moscow, Russia
>>
>>
>>
>
>
Received on Thu Jun 28 2001 - 14:51:33 CDT
