Bitmap I/O

From: Finely, Alistair <Alistair.Finely_at_BSkyB.com>
Date: Wed, 25 May 2005 08:54:14 +0100
Message-ID: <F37A37AD7382D411A0690008C75D54900C4805C0@sssl_exch_usr4_bak.sssl.bskyb.com>

Consider the following table created on both a 9i and 10g Db (16K block size):

CREATE TABLE AF_TEST ( a CHAR(200 BYTE), b CHAR(100 BYTE), c VARCHAR2(100 BYTE) ); CREATE BITMAP INDEX bi_af_test ON AF_TEST (c);

Column 'c' contains 4 distinct values, each value will constitute 25% of the table.I have evenly distributed them and analyzed the table and index with dbms_stats. When I run:

select * from af_test where c = <one of the distinct values>

Oracle performs a full table scan (fair enough - 25% of the rows are being returned) and auto trace returns the following: Statistics

          1  recursive calls
          0  db block gets
      36766  consistent gets
      15794  physical reads
          0  redo size
   24127462  bytes sent via SQL*Net to client
     593661  bytes received via SQL*Net from client
      16040  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     240581  rows processed

However if I force the use of the index autotrace returns this: Statistics

          1  recursive calls
          0  db block gets
      21030  consistent gets
       2766  physical reads
          0  redo size
   24141170  bytes sent via SQL*Net to client
     593661  bytes received via SQL*Net from client
      16040  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     240581  rows processed

The cost of the full table scan was a fraction of that of the index, BUT why is the I/O so much less with the index. I have tried this with various row lengths so that the number of blocks populated changes, but the results are essentially the same. Why would the cost be higher, but the I/O be less?

To populate the table I repeatedly ran the following inserts as a script to put about 1M rows in the table
INSERT INTO AF_TEST (a, b, c) SELECT object_name, object_type, 'England' FROM USER_OBJECTS
INSERT INTO AF_TEST (a, b, c) SELECT object_name, object_type, 'Scotland' FROM USER_OBJECTS
INSERT INTO AF_TEST (a, b, c) SELECT object_name, object_type, 'Wales' FROM USER_OBJECTS
INSERT INTO AF_TEST (a, b, c) SELECT object_name, object_type, 'Northern Ireland' FROM USER_OBJECTS

Information in this email may be privileged, confidential and is intended exclusively for the addressee. The views expressed may not be official policy, but the personal views of the originator. If you have received it in error, please notify the sender by return e-mail and delete it from your system. You should not reproduce, distribute, store, retransmit, use or disclose its contents to anyone. Please note we reserve the right to monitor all e-mail communication through our internal and external networks.

--

http://www.freelists.org/webpage/oracle-l Received on Wed May 25 2005 - 03:59:57 CDT