Oracle Scratchpad

October 23, 2009

Quiz Night

Filed under: Indexing — Jonathan Lewis @ 6:55 pm BST Oct 23,2009

Okay, so it’s not night-time, or even early evening, in some parts of the world – but somewhere it’s Friday night, and Friday night is quiz night.

Now, most people are aware that the clustering_factor of an index measures the ordering (or, if you want to be slightly more accurate, the clustering) of the rows in a table relative to the order of entries in the index. Consequently  if you check the clustering_factor before and after rebuilding an index it cannot change.

So how do you explan this output – which comes from a clean cut-n-paste, with no selective editing, from an SQL*Plus session running Oracle 10.2.03 with echo on – with no other sessions active on the system:


 SQL> execute dbms_stats.gather_index_stats(user,'i1', estimate_percent=>100)

PL/SQL procedure successfully completed.

SQL>
SQL> select
  2          clustering_factor
  3  from       user_indexes
  4  where      index_name = 'I1'
  5  ;

CLUSTERING_FACTOR
-----------------
              396

1 row selected.

SQL>
SQL> alter index i1 rebuild pctfree 10;

Index altered.

SQL>
SQL> execute dbms_stats.gather_index_stats(user,'i1', estimate_percent=>100)

PL/SQL procedure successfully completed.

SQL>
SQL> select
  2          clustering_factor
  3  from       user_indexes
  4  where      index_name = 'I1'
  5  ;

CLUSTERING_FACTOR
-----------------
              198

1 row selected.

You have two minutes, starting from NOW.

17 Comments »

  1. CREATE TABLE T1
    (N NUMBER,
    NAME VARCHAR2(30)
    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE USERS ;

    insert into T1 select rownum, ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’ from dual connect by level <= 100000;

    commit;

    CREATE INDEX I1 ON T1 (NAME)
    PCTFREE 50 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE USERS ;

    select clustering_factor from user_indexes where index_name = 'I1';

    delete from t1 where mod(n,2)=0;

    commit;

    alter table t1 move;

    — if you just look at the next three commands the results are similar to what you posted

    select clustering_factor from user_indexes where index_name = 'I1';

    alter index i1 rebuild;

    select clustering_factor from user_indexes where index_name = 'I1';

    — Not exactly the same numbers, but the ratio of before and after is about the same 2:1

    Comment by Wolfgang Breitling — October 23, 2009 @ 8:32 pm BST Oct 23,2009 | Reply

  2. Bitmap index after pctfree reduced significantly?

    Comment by dombrooks — October 23, 2009 @ 8:46 pm BST Oct 23,2009 | Reply

  3. Also this:

    SQL> alter table t1 enable row movement;

    Table altered.

    SQL> alter table t1 shrink space cascade;

    Table altered.

    SQL> alter table t1 disable row movement;

    Table altered.

    SQL> select clustering_factor from user_indexes where index_name = ‘I1′;

    CLUSTERING_FACTOR
    —————–
    509

    SQL> alter index i1 rebuild;

    Index altered.

    SQL> select clustering_factor from user_indexes where index_name = ‘I1′;

    CLUSTERING_FACTOR
    —————–
    255

    Comment by Chen Shapira — October 23, 2009 @ 8:54 pm BST Oct 23,2009 | Reply

  4. With my bitmap index:

    SQL> select clustering_factor from user_indexes where index_name = ‘I1′;

    CLUSTERING_FACTOR
    —————–
    81

    SQL> alter index i1 rebuild pctfree 10;

    Index altered.

    SQL> select clustering_factor from user_indexes where index_name = ‘I1′;

    CLUSTERING_FACTOR
    —————–
    4

    SQL>

    Comment by dombrooks — October 23, 2009 @ 9:03 pm BST Oct 23,2009 | Reply

  5. Dom Brooks has the right answer – so four subsidiary questions:

    Why is the change in clustering_factor irrelevant in this scenario ?
    What’s the flaw in the suggestions put forward by Chen and Wolfgang ?
    Which line of code in my example is redundant ?
    Could I have produced the same effect without changing the pctfree ?

    Comment by Jonathan Lewis — October 23, 2009 @ 9:36 pm BST Oct 23,2009 | Reply

    • For question 3 stats gathering step is unnecessary at least after 10G I think (cant remember for 9i)

      SQL> exec dbms_stats.delete_index_stats (user,’IX_T1_D’);

      PL/SQL procedure successfully completed.

      SQL> select clustering_factor from dba_indexes where index_name=’IX_T1_D';

      CLUSTERING_FACTOR
      —————–

      SQL> alter index IX_T1_D rebuild;

      Index altered.

      SQL> select clustering_factor from dba_indexes where index_name=’IX_T1_D';

      CLUSTERING_FACTOR
      —————–
      998141

      Comment by Coskan — October 24, 2009 @ 12:22 am BST Oct 24,2009 | Reply

    • If we play with Hakan Factor I think 4th question can be solved but “alter table minimize records_per_block” doesnt work when there is a bitmap so I had to drop it (at least can be counted as a solution)

      SQL> CREATE BITMAP INDEX IX_T2 ON T2 (gender) PCTFREE 10;

      Index created.

      SQL> select clustering_factor from dba_indexes where index_name=’IX_T2′;

      CLUSTERING_FACTOR
      —————–
      24

      SQL> –to prove that clustering does not change after recreation
      SQL> drop index IX_T2;

      Index dropped.

      SQL> CREATE BITMAP INDEX IX_T2 ON T2 (gender) PCTFREE 10;

      Index created.

      SQL> select clustering_factor from dba_indexes where index_name=’IX_T2′;

      CLUSTERING_FACTOR
      —————–
      24

      —CHANGE THE HAKAN FACTOR

      SQL> alter table t2 minimize records_per_block;
      alter table t2 minimize records_per_block
      *
      ERROR at line 1:
      ORA-28602: statement not permitted on tables containing bitmap indexes

      SQL> drop index ix_t2;

      Index dropped.

      SQL> alter table t2 minimize records_per_block;

      Table altered.

      SQL> CREATE BITMAP INDEX IX_T2 ON T2 (gender) PCTFREE 10;

      Index created.

      SQL> select clustering_factor from dba_indexes where index_name=’IX_T2′;

      CLUSTERING_FACTOR
      —————–
      12

      Comment by Coskan — October 24, 2009 @ 1:49 am BST Oct 24,2009 | Reply

    • Clustering factor does not have the same meaning for a bitmap.
      In a b*tree index, each entry represents a row in the table.
      With a bitmap index, each entry identifies a range of rows.
      By changing the pctfree, do we change the number of entries?
      In example above, changing pctfree changes num_rows and clustering_factor for a bitmap.

      Comment by dombrooks — October 24, 2009 @ 8:28 am BST Oct 24,2009 | Reply

      • > With a bitmap index, each entry identifies a range of rows
        Which is why they are renowned for concurrency issues.

        Comment by dombrooks — October 24, 2009 @ 8:33 am BST Oct 24,2009 | Reply

      • Just to add a little:
        In regards to CLUSTERING_FACTOR, the Oracle Reference Manual for 10gR2 says: “For bitmap indexes, this column is not applicable and is not used”.
        The value for this column appears to be the same as NUM_ROWS, (after sampling a few bitmap indexes).

        Comment by hpdba — October 24, 2009 @ 7:30 pm BST Oct 24,2009 | Reply

  6. The flaw with comments 1 & 3 is that the index statistics are not gathered immediately before checking the cluster factor. (Changes to the table are made in between).

    Comment by hpdba — October 24, 2009 @ 6:22 pm BST Oct 24,2009 | Reply

    • hpdba,

      Correct.
      I was carefuly to include the stats gathering so that the statistics were current before doing the rest of the demonstration.

      Taken to an extreme, of course, I could “demonstrate” any change in statistics that I wanted to by creating some data, collecting stats on that data, then changing the data again. If I published just the code from that point onwards eveyone ought to point out the error in my methodology.

      Creating the correct model of the thing you’re trying to emulate can be very difficult – and it’s very easy to miss critical steps.

      Comment by Jonathan Lewis — October 27, 2009 @ 10:57 am BST Oct 27,2009 | Reply

  7. Question Jonathan – If the table had been an Index Organized Table how would the senario play out? (I have no idea, no where near a test system I can try, thus the question).

    Comment by mwidlake — October 25, 2009 @ 1:58 am BST Oct 25,2009 | Reply

    • Martin,
      The bitmap index on an IOT depends on an intermediate table being created – and I haven’t looked at the side effects of this particular change in that case.

      From memory, you are really building a bitmap index on a heap table, so (with a change in the data, possibly) you should be able to see the same effect.

      Comment by Jonathan Lewis — October 27, 2009 @ 10:51 am BST Oct 27,2009 | Reply

  8. Why is the change in clustering_factor irrelevant in this scenario ?

    Since its a bitmap index

    “The clustering_factor has no direct connection with the scattering of data in the table”
    “clustering_factor in bitmap indexes does not describe the table”

    – Cost based oracle fundamentals,Jonathan lewis

    And also PCTFREE of 10 is going to give more trouble for the DML.

    What’s the flaw in the suggestions put forward by Chen and Wolfgang ?

    The possible flaw in Wolfgang and chang is they show wrong statistics in the first place, they should have recollected the statistics after the delete statement or the shrink commands before selecting the clustering factor for thhe first time.

    Which line of code in my example is redundant ?

    The redundant line is the statisticss collection as the rebuild would have automatically collected the statistics.

    Could I have produced the same effect without changing the pctfree

    One possible option would be to rebuild the index on a bigger block sized tablespace without change in the pctfree.

    Comment by Balaji Ramachandran — October 26, 2009 @ 7:26 am BST Oct 26,2009 | Reply

    • Balaji,

      The bigger block-size without changing the pctfree is a good answer and easy to demonstrate.

      The “automatic compute” for 10g is, of course, the correct answer for the redudant line.

      As a side-note, it’s also possible to get significant changes in the clustering_factor of bitmap indexes by rebuilding it without changing the pctfree or block size. In 10g, the best I’ve managed so far is a drop of 33% rather then 50%. It’s a side effect of marking index entries for deletion, combined with delayed cleanout after commit.

      Comment by Jonathan Lewis — October 27, 2009 @ 11:03 am BST Oct 27,2009 | Reply

  9. I’m thinking about this and the previous post about Bitmap Updates.

    I was taught that a high clustering factor is a measure of how much rows are addressed by a single entry in the index: so the lower the “closer” is the index to the table (i.e. a primary key is “1-to-1″ to the table).

    In this example the change in the clustering factor from 396 to 198 would suggest that the rows in the table are “not so packed”. Probably idea this is wrong.

    Keeping in mind the previous post, where Jonathan suggest that we “must” use bigger bitmap index to solve concurrency problem, the change in the clustering factor can be seen as a grow of the bitmap index.
    Probably right as the change of the pctfree suggest.

    Now, how all this impact performance? And how the optimizer reacts to this change?

    I think that performance can be worst as the index is bigger. On the other hand the optimizer can ignore this index because of is “low” clustering factor (for example if we need to do a range scan).

    How many errors I made? ^_^

    Bye,
    Antonio

    Comment by lascoltodelvenerdi — October 27, 2009 @ 10:03 am BST Oct 27,2009 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,909 other followers