RE: pct_direct_access for a secondary index on an iot

I was just reading about that recently. Here's what I think happens (though a correction would be welcome):

In a secondary b-tree index on an IOT, the b-tree index contains, for each key, a guess as to which block contains the "row". In case of a block split, the guess can become incorrect. Hence the pct_direct_access statistic.

In a secondary bitmap index on an IOT, the bitmap points to a row in the mapping table, and the mapping table contains a "logical rowid" pointing to the location of the "row" in the IOT. The mapping table is always updated with each DML on the IOT so the bitmap index would always find the right "row" without guessing. Therefore the statistic would have no meaning for a bitmap secondary index.

> -----Original Message-----
> From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com]On Behalf Of
> bulbultyagi_at_now-india.net.in
>
> Hello list, I am unable to get a value in
> dba_indexes(pct_direct_access) for
> a bitmap index I created on an iot. I am using Oracle 9.2.0.1.0
> enterprise
> edition on windows .
> Can someone help me by telling me what I need to do.
> I have done the following :
>
> --First create the iot with a mapping table.
> sql> create table countries
> ( country_id char(2) ,
> country_name varchar2 (40) ,
> currency_name varchar2(25),
> constraint country_c_id_pk primary key (country_id ) )
> organization index including country_name
> pctthreshold 20
> tablespace users
> overflow tablespace indx
> mapping table;
>
> Table created.
>
> SQL> insert into HR.countries values ( 'C', 'A', NULL ) ;
> 1 row created.
>
> SQL> insert into HR.countries values ( 'D', 'A', NULL ) ;
> 1 row created.
>
> SQL> insert into HR.countries values ( 'E', 'A', NULL ) ;
> 1 row created.
>
> SQL> COMMIT ;
> Commit complete.
>
>
> --Now create a bmp index on it.
> SQL> create bitmap index myindex on countries (country_name ) ;
> Index created.
>
> --This command should analyze the iot and its mapping table.
> SQL> analyze table countries compute statistics;
> Table analyzed.
>
> --Might as well analyze the bmp index ( this does no harm right ? )
> SQL> analyze index myindex compute statistics;
> Index analyzed.
>
> SQL> select index_name, pct_direct_access from DBA_indexes
> where index
> _name = 'MYINDEX' and owner=user;
>
> INDEX_NAME PCT_DIRECT_ACCESS
> ------------------------------ -----------------
> MYINDEX
>
> --As you can see I get a null value in the
> pct_dircect_access column.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).