Re: Is this a good definition for clustering factor

On Thu, 17 Feb 2005 11:18:55 +0000, Niall Litchfield
<niall.litchfield@xxxxxxxxx> wrote:
> 
> I like the tuning guide example. I don't like the descriptions above.
> In particular it seems to me that the first sentence is very poor
> english 'amount of order' is just an ugly phrase. I think that I would
> have used something like 'indicates how well the physical ordering of
> the rows in the table matches the order of the index'.

I like a demo, and I had time to do it tonight. 

Tired of working on taxes....

Here's a graphic example of clustering factor.  If someone cares to
write some analytic SQL to get rid of the package function, please
feel free to share.  :)

First create a table and indexes:

drop table ctest;
create table ctest
as
select *
from dba_objects
order by object_id
/

create index ctest_objid_idx on ctest(object_id);
create index ctest_objname_idx on ctest(object_name);

exec dbms_stats.gather_table_stats(user,'CTEST')
exec dbms_stats.gather_index_stats(user,'CTEST_OBJID_IDX')
exec dbms_stats.gather_index_stats(user,'CTEST_OBJNAME_IDX')

Now create a packaged function so we can tell when the block id changes.
It is far from perfect ( subject to non-repeatable results - off by one ), but
is good enough for this.

create or replace package bchk
is
   function newblock ( block_number_in integer ) return varchar2;
end;
/

create or replace package body bchk
is

   old_bnum integer := 0;

   function newblock ( block_number_in integer )
   return varchar2
   is
      new_block varchar2(3);
   begin
      if block_number_in != old_bnum then
         new_block := 'YES';
      else
         new_block := 'NO';
      end if;
      old_bnum := block_number_in;
      return new_block;
   end;

end;
/


Now check the clustering factor on the indexes:

23:29:29 hudson - jkstill@ts70 SQL> l
  1  select index_name, clustering_factor
  2  from user_indexes
  3* where table_name = 'CTEST'
23:29:31 hudson - jkstill@ts70 SQL> /
 
INDEX NAME                     CLUSTERING_FACTOR
------------------------------ -----------------
CTEST_OBJID_IDX                              673
CTEST_OBJNAME_IDX                          23924
 
2 rows selected.

How many blocks in the table?
  1  select count(*) block_count
  2  from (
  3     select block_number
  4     from (
  5             select object_id, owner, object_name,
dbms_rowid.rowid_block_number(rowid) block_number
  6             from ctest
  7             order by object_id
  8     )
  9     group by block_number
 10* )
23:31:07 hudson - jkstill@ts70 SQL> /
 
BLOCK_COUNT
-----------
        673
 
1 row selected.

Hey, looks like it matches the clustering_factor for the Object ID index.
Not surprising though, as the table was created in object_id order.  Scanning
the index and reading each row requires reading each of the 673 table blocks
just once.

Now let's count the number of block transitions made when accessing the
table in object_name order:


23:46:16 hudson - jkstill@ts70 SQL> l
  1  select count(*)
  2  from (
  3     select object_name, block_number, bchk.newblock(block_number) newblock
  4     from (
  5             select object_id, owner, object_name,
dbms_rowid.rowid_block_number(rowid) block_number, rownum
  6             from ctest
  7             order by object_name
  8     )
  9  )
 10* where newblock = 'YES'
23:46:18 hudson - jkstill@ts70 SQL> /
 
  COUNT(*)
----------
     23953
 
1 row selected.

Not exactly the same as the clustering factor, but close enough to 
show what is taking place.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
http://www.freelists.org/webpage/oracle-l

Other related posts: