[20181026]12c Attribute Clustering特性2.txt

[20181026]12c Attribute Clustering特性2.txt

Attribute Clustering實際上按照特定的欄位組織表,使相同的值聚集起來,總的來講就是減少索引的聚集因子.

One of the really cool new features introduced in 12.1.0.2 is Attribute Clustering. This new table based attribute
allows you to very easily cluster data in close physical proximity based on the content of specific columns.

--//主要了解資訊記錄在那個表。

1.環境：
SCOTT@test01p> @　ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

2.測試：
SCOTT@test01p> @ 10046on 12
Session altered.

SCOTT@test01p> create table ziggy2 (id number, code number, name varchar2(30)) clustering by linear order (code，name) without materialized zonemap;
Table created.

SCOTT@test01p> @ 10046off
Session altered.

--//檢查跟蹤執行如下：
insert into clst$ (clstobj#, clstfunc, flags) values(:1, :2, :3)
insert into clstkey$ (clstobj#, tabobj#, intcol#, position, groupid) values (:1, :2, :3, :4, :5)


SCOTT@test01p> select * from sys.clst$ where clstobj# in (select object_id from dba_objects where owner=user and object_name='ZIGGY2');
  CLSTOBJ#   CLSTFUNC CLSTLASTDM CLSTLASTLOAD FLAGS
---------- ---------- ---------- ------------ -----
     22899          2                             3

SCOTT@test01p> select * from sys.clstkey$ where clstobj# in (select object_id from dba_objects where owner=user and object_name='ZIGGY2');
  CLSTOBJ#    TABOBJ#    INTCOL#   POSITION    GROUPID
---------- ---------- ---------- ---------- ----------
     22899      22899          2          1          0
     22899      22899          3          2          0

--// clustering by linear order (code，name) ,2個欄位基本能對上。