Impact of sorting/nosorting and usage of parallel clause when creating table on the clustering factor of the index associated with the table

Goal: Impact of sorting/nosorting and usage of parallel clause when creating table on the clustering factor of the index associated with the table.

No parallel loads:
create tablespace test_assm
blocksize 8K
datafile size 10m autoextend on maxsize 50m
extent management local
uniform size 1M
segment space management auto
;

create table scott.t1 tablespace test_assm
as
select * from dba_objects;

Use T1 to populate further tables in sorted manner and non-sorted manner...

drop tablespace test_assm_nosort including contents and datafiles;
drop tablespace test_assm_sort including contents and datafiles;

create tablespace test_assm_sort
blocksize 8K
datafile size 10m autoextend on maxsize 50m
extent management local
uniform size 1M
segment space management auto
;

create tablespace test_assm_nosort
blocksize 8K
datafile size 10m autoextend on maxsize 50m
extent management local
uniform size 1M
segment space management auto
;

create table scott.t1_sort tablespace test_assm_nosort
as
select * from scott.t1
order by object_id;

create table scott.t1_nosort tablespace test_assm_sort
as
select * from scott.t1;
let us now create an index on object_id column....

create index scott.t1_sort_idx on scott.t1_sort(object_id);
create index scott.t1_nosort_idx on scott.t1_nosort(object_id);

exec dbms_stats.gather_table_stats('SCOTT','T1_SORT');
exec dbms_stats.gather_table_stats('SCOTT','T1_NoSORT');

alter session set nls_Date_Format='DD/MM/YYYY HH24:MI';

col owner for a10
col table_name for a10
col index_name for a20
col INDEX_TYPE for a10
select a.owner,a.table_name,a.num_rows,a.last_analyzed,b.index_name,b.index_type,b.uniqueness,b.clustering_factor,b.num_rows
from dba_tables a,dba_indexes b
where a.owner=b.owner
and a.table_name=b.table_name
and a.table_name like 'T1_%'
and a.owner='SCOTT'
order by owner,table_name,index_name;

select a.owner,a.table_name,a.num_rows,a.last_analyzed tbl_analyz,b.index_name,b.index_type,b.last_analyzed indx_analz,b.clustering_factor,b.num_rows
from dba_tables a,dba_indexes b
where a.owner=b.owner
and a.table_name=b.table_name
and a.table_name like 'T1_%'
and a.owner='SCOTT'
order by owner,table_name,index_name;


SQL> select a.owner,a.table_name,a.num_rows,a.last_analyzed,b.index_name,b.index_type,b.uniqueness,b.clustering_factor,b.num_rows
  2  from dba_tables a,dba_indexes b
  3  where a.owner=b.owner
  4  and a.table_name=b.table_name
  5  and a.table_name like 'T1_%'
  6  and a.owner='SCOTT'
  7  order by owner,table_name,index_name;

OWNER      TABLE_NAME   NUM_ROWS LAST_ANALYZED    INDEX_NAME           INDEX_TYPE UNIQUENES CLUSTERING_FACTOR   NUM_ROWS
---------- ---------- ---------- ---------------- -------------------- ---------- --------- ----------------- ----------
SCOTT      T1_NOSORT       91818 14/09/2018 19:28 T1_NOSORT_IDX        NORMAL     NONUNIQUE              2519      91818
SCOTT      T1_SORT         91818 14/09/2018 19:28 T1_SORT_IDX          NORMAL     NONUNIQUE              1541      91818

SQL>

SQL> select a.owner,a.table_name,a.num_rows,a.last_analyzed tbl_analyz,b.index_name,b.index_type,b.last_analyzed indx_analz,b.clustering_factor,b.num_rows
from dba_tables a,dba_indexes b
where a.owner=b.owner
  2    3    4  and a.table_name=b.table_name
  5  and a.table_name like 'T1_%'
and a.owner='SCOTT'
  6    7  order by owner,table_name,index_name;

OWNER      TABLE_NAME   NUM_ROWS TBL_ANALYZ       INDEX_NAME           INDEX_TYPE INDX_ANALZ       CLUSTERING_FACTOR   NUM_ROWS
---------- ---------- ---------- ---------------- -------------------- ---------- ---------------- ----------------- ----------
SCOTT      T1_NOSORT       91818 14/09/2018 19:28 T1_NOSORT_IDX        NORMAL     14/09/2018 19:28              2519      91818
SCOTT      T1_SORT         91818 14/09/2018 19:28 T1_SORT_IDX          NORMAL     14/09/2018 19:28              1541      91818

SQL>


==> so we evidently see there is a difference in the clustering factor for a table loaded in sorted manner vs the other.

Now let us proceed to magnify this further.. inclusion of parallel clause.

create tablespace test_assm_px_sort
blocksize 8K
datafile size 10m autoextend on maxsize 50m
extent management local
uniform size 1M
segment space management auto
;

create tablespace test_assm_px_nosort
blocksize 8K
datafile size 10m autoextend on maxsize 50m
extent management local
uniform size 1M
segment space management auto
;

create table scott.t1_px_sort tablespace test_assm_px_sort
as
select /*+ parallel(a,4) */ * from scott.t1 a
order by object_id;

create table scott.t1_px_nosort tablespace test_assm_px_nosort
as
select /*+ parallel(a,4) */ * from scott.t1 a;


create index scott.t1_px_sort_idx on scott.t1_px_sort(object_id);
create index scott.t1_px_nosort_idx on scott.t1_px_nosort(object_id);

SQL> select a.owner,a.table_name,a.num_rows,a.last_analyzed,b.index_name,b.index_type,b.uniqueness,b.clustering_factor,b.num_rows
  2  from dba_tables a,dba_indexes b
where a.owner=b.owner
  3    4  and a.table_name=b.table_name
  5  and a.table_name like 'T1_%'
and a.owner='SCOTT'
  6    7  order by owner,table_name,index_name;


OWNER     ,TABLE_NAME          ,  NUM_ROWS,LAST_ANALYZED      ,INDEX_NAME          ,INDEX_TYPE,UNIQUENES,CLUSTERING_FACTOR,  NUM_ROWS
----------,--------------------,----------,-------------------,--------------------,----------,---------,-----------------,----------
SCOTT     ,T1_NOSORT           ,     91818,14/09/2018 19:58:43,T1_NOSORT_IDX       ,NORMAL    ,NONUNIQUE,             2519,       91818
SCOTT     ,T1_PX_NOSORT        ,     91818,14/09/2018 19:58:51,T1_PX_NOSORT_IDX    ,NORMAL    ,NONUNIQUE,             3783,       91818
SCOTT     ,T1_PX_SORT          ,     91818,14/09/2018 19:58:50,T1_PX_SORT_IDX      ,NORMAL    ,NONUNIQUE,             1541,       91818
SCOTT     ,T1_SORT             ,     91818,14/09/2018 19:58:43,T1_SORT_IDX         ,NORMAL    ,NONUNIQUE,             1541,       91818

SQL> select a.owner,a.table_name,a.num_rows,a.last_analyzed tbl_analyz,b.index_name,b.index_type,b.last_analyzed indx_analz,b.clustering_factor,b.num_rows
from dba_tables a,dba_indexes b
  2    3  where a.owner=b.owner
and a.table_name=b.table_name
  4    5  and a.table_name like 'T1_%'
and a.owner='SCOTT'
  6    7  order by owner,table_name,index_name;


OWNER     ,TABLE_NAME          ,  NUM_ROWS,TBL_ANALYZ         ,INDEX_NAME          ,INDEX_TYPE,INDX_ANALZ         ,CLUSTERING_FACTOR,  NUM_ROWS
----------,--------------------,----------,-------------------,--------------------,----------,-------------------,-----------------,----------
SCOTT     ,T1_NOSORT           ,     91818,14/09/2018 19:58:43,T1_NOSORT_IDX       ,NORMAL    ,14/09/2018 19:58:44,      2519,      91818
SCOTT     ,T1_PX_NOSORT        ,     91818,14/09/2018 19:58:51,T1_PX_NOSORT_IDX    ,NORMAL    ,14/09/2018 19:58:51,      3783,      91818
SCOTT     ,T1_PX_SORT          ,     91818,14/09/2018 19:58:50,T1_PX_SORT_IDX      ,NORMAL    ,14/09/2018 19:58:50,      1541,      91818
SCOTT     ,T1_SORT             ,     91818,14/09/2018 19:58:43,T1_SORT_IDX         ,NORMAL    ,14/09/2018 19:58:43,      1541,      91818


conclusion: if we observe same set of data and same type of index, but look if the table is loaded with parallel clause with no sort then the clustering factor is nearly 2x more than the viceversa condition.

Thanks