1

Topic: The clustering factor - table reorganization

Dear colleagues, good afternoon.
In log "Swagger" in article about optimization of subqueries in section 5 "the Index has the big factor of clustering CLUSTERING_FACTOR" read such phrase:

Miheichev wrote:

the First decision at big  is to remove an existing index as not the effective. The second decision if the given index is most often applied in requests and it is necessary to rebuild table structure so that lines in table units have been arranged in the same order in which the information on the given lines in an index is allocated, i.e. to make cluster table units, reducing thus number of relocation from one unit to another by index operation.

Whether and somebody can set an example such reorganization? So to say to complete the picture?

2

Re: The clustering factor - table reorganization

The current should to be forgotten that if on one index CF it is refined, as a rule, on another spoils sad(( [spoiler]

tst> create table t1 as select object_id, object_name from all_objects;
Table created.
tst> create unique index pk_t1 on t1 (object_id);
Index created.
tst> create index t1_idx_name on t1 (object_name);
Index created.
tst> alter table t1 add primary key (object_id);
Table altered.
tst> exec dbms_stats.gather_table_stats (user, ' T1 ', cascade => true, method_opt =>'for all columns size 1 ')
PL/SQL procedure successfully completed.
tst> select index_name, t.num_rows, t.blocks, clustering_factor
2 from user_tables t, user_indexes i
3 where t.table_name=i.table_name and t.table_name ='T1 ';
INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ---------- -----------------
==> T1_IDX_NAME 6493 30>>> 2372 <<<
PK_T1 6493 30>>> 93 <<<
tst> create table t1_bis as select * from t1 where 1=2;
Table created.
tst> alter table t1_bis modify (object_id null, object_name null);
Table altered.
tst> set serveroutput on
tst> declare n_errs number: = 0;
2 begin
3 dbms_redefinition.start_redef_table (user, ' T1 ', ' T1_BIS',>>> orderby_cols =>'OBJECT_NAME ' <<<);
4 dbms_redefinition.copy_table_dependents (user, ' T1 ', ' T1_BIS', num_errors => n_errs);
5 dbms_output.put_line (' copy_table_dependents: num_errors = ' || n_errs);
6 end;
7 /
copy_table_dependents: num_errors=0
PL/SQL procedure successfully completed.
tst> exec dbms_redefinition.finish_redef_table (user, ' T1 ', ' T1_BIS')
PL/SQL procedure successfully completed.
tst> exec dbms_stats.gather_table_stats (user, ' T1 ', cascade => true, method_opt =>'for all columns size 1 ')
PL/SQL procedure successfully completed.
tst> select index_name, t.num_rows, t.blocks, clustering_factor
2 from user_tables t, user_indexes i
3 where t.table_name=i.table_name and t.table_name ='T1 ';
INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ---------- -----------------
==> T1_IDX_NAME 6493 30>>> 26 <<<
PK_T1 6493 30>>> 3009 <<<
tst>

[/spoiler]

3

Re: The clustering factor - table reorganization

Vyacheslav, thanks huge!
Went attentively to study....

4

Re: The clustering factor - table reorganization

[quote =-2] In article it is sounded, for what methods of reversal to an index it is necessary to worry for ""?

Well then I will share a link, yes worthy moderators for the advertiser of indirect resources do not consider me and not there will be I for it painfully bit, can even feet ():
http://www.fors.ru/upload/magazine/07/h … tions.html