Re: buffering index

Vadim Grepan <kezal_at_mail.ru> wrote in message news:<3BA04BA0.4983B95B_at_mail.ru>...
> Hello Mark!
>
> Thanks for your feedback.
> Here is my considerations:
>
> 1. Really I've forgot to add table name into hint. It should be like /*+ INDEX
> (bookrelease uni_bookrelease_muzeif_prel) */
> However I may not agree that Oracle has ignored index.
> This line from trace has testified that:
> = = = cut = = =
> ..
> 2 1 INDEX (FAST FULL SCAN) OF 'UNI_BOOKRELEASE_MUZEIF_PREL'
>
> Mark D Powell wrote:
>
> > 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 --
> >

I missed the statistics when I saw the hint was wrong. Oracle is using the index via fast full scan to count the rows instead of reading the table. This is probably the fastest way to perform this query. Because a fast full index scan reads the index leaf blocks using a sequential read of the index extents the blocks are treated like table blocks read this way. Prior to 8.1 I know blocks read by full table scan were placed on the least recently used end of the buffer pool LRU list so they were immediately available to be flushed, but in 8.1 this may have changed.

If you really need to cache the index you should look into using a keep pool; however, this will require a bounce of the instance to make the change. Personally I prefer to allow Oracle to manage the pool. If the index is heavily used then the index blocks will have a tendency to stay in the buffer. If they are heavily used but are not in the buffer then this would indicate that Oracle had no choice but to flush them to make room for other read requests indicating that the buffer pool is just plan too small.

Sorry abouth the mistake.

• Mark D Powell --