clustering factor

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

Other related posts: