Re: Is this a good definition for clustering factor

From: Jared Still <jkstill_at_gmail.com>
Date: Sun, 27 Feb 2005 23:48:51 -0800
Message-ID: <bf46380502272348360606bc@mail.gmail.com>

On Thu, 17 Feb 2005 11:18:55 +0000, Niall Litchfield <niall.litchfield_at_gmail.com> 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_at_ts70 SQL> l
  1 select index_name, clustering_factor   2 from user_indexes
  3* where table_name = 'CTEST'
23:29:31 hudson - jkstill_at_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_at_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_at_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_at_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

Received on Mon Feb 28 2005 - 02:51:58 CST