buffering index

From: Vadim Grepan <kezal_at_mail.ru>
Date: Wed, 12 Sep 2001 17:01:15 +0400
Message-ID: <3B9F5C9A.14BDED06@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 = = = =
Received on Wed Sep 12 2001 - 08:01:15 CDT