clustering factor
- From: Anupam Pandey <my.oralce@xxxxxxxxx>
- To: oracle Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 19 Aug 2010 16:45:27 +0530
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:
