Re: buffering index

From: Vadim Grepan <kezal_at_mail.ru>
Date: Thu, 13 Sep 2001 10:01:04 +0400
Message-ID: <3BA04BA0.4983B95B@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'

Statistics

---

...

        5319  consistent gets
        5233  physical reads

• = = cut = = =

Without hint I've got real full scan:
= = = cut = = =
..
  2 1 TABLE ACCESS (FULL) OF 'BOOKRELEASE' Statistics

---

...

      19634  consistent gets
      19634  physical reads

...
= = = cut = = =

  2. Caluses SELECT COUNT(indexed_col) and COUNT (*) are identical in fact for table having PK or unique index on NOT NULL fild(s) I think you mentioned about
http://asktom.oracle.com/pls/ask/f?p=4950:8:120933::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1253672482768,%7Bselect%7D%20and%20%7Bcount%7D%20and%20%7Bindex%7D

Rgds, Vadim Grepan

---

Moscow, Russia

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 --

>
>
>

> -- Mark D Powell --
Received on Thu Sep 13 2001 - 01:01:04 CDT