Re: buffering index

Vadim Grepan <kezal_at_mail.ru> wrote in message news:<3B9F5C9A.14BDED06_at_mail.ru>...
> Hello All!
>
> I have large enough table (around 2 million records) with unique index
> on NOT NULL fields.
> So when I try to count all records it takes a lot of time and huge
> physical reads. Index's blocks are not buffering
> even after clause like "ALTER INDEX uni_bookrelease_muzeif_prel STORAGEe
> (buffer_pool default);"
>
> How can I buffer index's block another way? The worst of all I cannot
> shutdown database in a near future and
> increase db_block_buffer.
>
> Oracle 8.1.7, SPARC
>
> Rgds, Vadim Grepan
> ------------------------------
> Moscow, Russia
>
> = = = = cut = = = =
> SQL>
> SQL> SELECT /*+ index (uni_bookrelease_muzeif_prel) */ count(*)
> 2 FROM bookrelease;
>
> COUNT(*)
> ----------
> 1970189
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=RULE (Cost=4 Card=1)
> 1 0 SORT (AGGREGATE)
> 2 1 INDEX (FAST FULL SCAN) OF 'UNI_BOOKRELEASE_MUZEIF_PREL'
> (UNIQUE) (Cost=4 Card=1951504)
>
> Statistics
> ----------------------------------------------------------
> 298 recursive calls
> 32 db block gets
> 5319 consistent gets
> 5233 physical reads
> 0 redo size
> 370 bytes sent via SQL*Net to client
> 425 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 7 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
> = = = = cut = = = =

Vadim, your index hint is wrong so Oracle is ignoring it. Try select /*+ INDEX(label index_name) */ count(*) where the label comes from FROM TABLE_NAME A <- is the label

Note that the CBO can use an index for a count if you perform the count on the first column of an index as in SELECT COUNT(indexed_col) instead of count(*) and the hint will probably not be necessary with version 8.1.7

This is documented in the Common FAQ, but I do not have the web site memorized. You can find the Commom FAQ mentioned in several posts via a goodle search and look under DBA for the article about speeding up count.

• Mark D Powell --
• Mark D Powell --