{15 その他}属性クラスタリングされた表を作成

 

https://docs.oracle.com/cd/F19136_01/dwhsg/attribute-clustering.html

sqlplus test/test@pdb11

SELECT TABLE_NAME, CLUSTERING FROM DBA_TABLES WHERE OWNER='TEST';

SELECT owner, table_name, clustering_type, on_load, on_datamovement, with_zonemap
FROM DBA_CLUSTERING_TABLES WHERE table_name like '%SALES%';

SELECT detail_owner, detail_name, detail_column, position
FROM DBA_CLUSTERING_KEYS
WHERE table_name like '%SALES%';

     線形順序

CREATE TABLE sales1 (
  prod_id        NUMBER(6) NOT NULL,
  cust_id        NUMBER NOT NULL,
  time_id        DATE NOT NULL,
  channel_id     CHAR(1) NOT NULL,
  promo_id       NUMBER(6) NOT NULL,
  quantity_sold  NUMBER(3) NOT NULL,
  amount_sold    NUMBER(10,2) NOT NULL
)
CLUSTERING
  BY LINEAR ORDER (cust_id, prod_id);

 

create table my_products
(prod_id number(6) primary key
,prod_category varchar2(100)
,prod_subcategory varchar2(100)
);

CREATE TABLE my_sales (
  prod_id        NUMBER(6)  NOT NULL,
  cust_id        NUMBER NOT NULL,
  time_id        DATE NOT NULL,
  channel_id     CHAR(1) NOT NULL,
  promo_id       NUMBER(6) NOT NULL,
  quantity_sold  NUMBER(3) NOT NULL,
  amount_sold    NUMBER(10,2) NOT NULL
)
CLUSTERING
  my_sales JOIN my_products ON (my_sales.prod_id = my_products.prod_id)
  BY LINEAR ORDER (cust_id, prod_category, prod_subcategory);

 

     インターリーブ順序
     
CREATE TABLE sales2 (
  prod_id        NUMBER(6) NOT NULL,
  cust_id        NUMBER NOT NULL,
  time_id        DATE NOT NULL,
  channel_id     CHAR(1) NOT NULL,
  promo_id       NUMBER(6) NOT NULL,
  quantity_sold  NUMBER(3) NOT NULL,
  amount_sold    NUMBER(10,2) NOT NULL
)
CLUSTERING
  BY INTERLEAVED ORDER (time_id, prod_id);