clustering factor

From: Anupam Pandey <my.oralce_at_gmail.com>
Date: Thu, 19 Aug 2010 16:45:27 +0530
Message-ID: <AANLkTim7dY_vsEHTLcnAaSev9OPiSxpqjs5RqrRLc6hk_at_mail.gmail.com>

Hi Listers,

                 I am trying to improve the clustering factor for one index

.I created the new table and inserted data sorted based on index column . but still there is no improvement in clustering factor .

I have attached the script and outputs from data dictionary. Plz suggest if I am doing anything wrong here or anything else I need to do to improve clustering factor .

Thanks And Regards,
  Anupam Pandey

CREATE TABLE TEST_CLUSTER
(

  KEY           NUMBER,
  ID            VARCHAR2(16 CHAR),
  PID     VARCHAR2(16 CHAR),
  PATH       VARCHAR2(3000 CHAR),
  A    VARCHAR2(512 CHAR),
  B    VARCHAR2(512 CHAR),
  C    VARCHAR2(512 CHAR),
  D    VARCHAR2(512 CHAR),
  E    VARCHAR2(512 CHAR),
  F    VARCHAR2(512 CHAR),

  G VARCHAR2(512 CHAR),
  H VARCHAR2(512 CHAR),

  I          VARCHAR2(4000 CHAR),
  J     VARCHAR2(3000 CHAR),
  K  VARCHAR2(3 CHAR),
  L      NUMBER,
  M       NUMBER,
  N       VARCHAR2(3 CHAR),
  O           VARCHAR2(1 CHAR)           DEFAULT 'N',
  P       DATE,
  Q         VARCHAR2(64 CHAR),
  R      DATE,
  S        VARCHAR2(64 CHAR),
  T        VARCHAR2(32 CHAR),
  U      VARCHAR2(512 CHAR)

)
PARTITION BY HASH (L, ID)
  PARTITIONS 16
NOCOMPRESS
NOCACHE
PARALLEL ( DEGREE 16 INSTANCES 1 )
MONITORING
ENABLE ROW MOVEMENT
/

alter session disable parallel dml;

insert into TEST_CLUSTER_NOPARALLEL
select /*+ NOPARALLEL */ *
from TEST_CLUSTER
order by id ;

commit;

alter session enable parallel dml;

drop index noparallel_id_idx on table TEST_CLUSTER_NOPARALLEL;

create indexnoparallel_id_idx on TEST_CLUSTER_NOPARALLEL(id);

begin

        dbms_stats.gather_table_stats(
                user,
                'TEST_CLUSTER_NOPARALLEL',
                cascade => true,
                estimate_percent => null,
                method_opt => 'for all indexed columns size 250'
        );

end;
/

insert /*+ append */ into TEST_CLUSTER_PARALLEL select *
from TEST_CLUSTER_PARALLEL
order by id ;

commit;

drop index id_idx on table TEST_CLUSTER_PARALLEL;

create index id_idx on TEST_CLUSTER_PARALLEL(id);

begin

        dbms_stats.gather_table_stats(
                user,
                'TEST_CLUSTER_PARALLEL',
                cascade => true,
                estimate_percent => null,
                method_opt => 'for all indexed columns size 250'
        );

end;
/

select

    table_name,
    blocks,
    num_rows
from user_tables
where table_name in
('TEST_CLUSTER_NOPARALLEL','TEST_CLUSTER_PARALLEL','TEST_CLUSTER') ;

TABLE_NAME                         BLOCKS   NUM_ROWS
------------------------------ ---------- ----------
TEST_CLUSTER                         450794   49870000
TEST_CLUSTER_PARALLEL                569226   49203017
TEST_CLUSTER_NOPARALLEL               652720   49203017

select table_name, index_name,

    num_rows, distinct_keys,
    blevel, leaf_blocks, clustering_factor,     avg_leaf_blocks_per_key, avg_data_blocks_per_key from

    user_indexes
where table_name in
('TEST_CLUSTER_NOPARALLEL','TEST_CLUSTER_PARALLEL','TEST_CLUSTER') and index_name in ('NOPARALLEL_ID_IDX','ID_IDX','ID_IDX') ;

TABLE_NAME                     INDEX_NAME                       NUM_ROWS
DISTINCT_KEYS     BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR

AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY

------------------------------ ------------------------------ ----------
------------- ---------- ----------- -----------------
----------------------- -----------------------
TEST_CLUSTER_NOPARALLEL     NOPARALLEL_ID_IDX              49203017
49203017          2       38246          46165444
1                       1
TEST_CLUSTER                       ID_IDX                      51651633
49870000          2       76806          51629744
1                       1
TEST_CLUSTER_PARALLEL               ID_IDX
49203017      49203017          2       42553
46202928                       1                       1

select table_name,

    column_name,
    num_nulls, num_distinct, density,
    low_value, high_value
from

    user_tab_columns
where table_name in
('TEST_CLUSTER_NOPARALLEL','TEST_CLUSTER_PARALLEL','TEST_CLUSTER') and column_name in ('ID')
order by

    column_name
;

TABLE_NAME                     COLUMN_NAME                     NUM_NULLS
NUM_DISTINCT    DENSITY
LOW_VALUE                                                        HIGH_VALUE
------------------------------ ------------------------------ ----------
------------ ----------
----------------------------------------------------------------
----------------------------------------------------------------
TEST_CLUSTER_PARALLEL               ID                                 0

49203017 2.03239569
2D31
39393939393937

TEST_CLUSTER_NOPARALLEL             ID                                 0

49203017 2.03239569
2D31
39393939393937

TEST_CLUSTER                        ID                                 0

49870000 2.00521355
31303030313631333435
39393830313132

--
http://www.freelists.org/webpage/oracle-l




application/octet-stream attachment: cluster.log

Received on Thu Aug 19 2010 - 06:15:27 CDT