{15 その他}既存表に属性クラスタリングを追加

 

https://docs.oracle.com/cd/F19136_01/dwhsg/attribute-clustering.html#GUID-589A37EE-61C9-4F12-BD8F-CB12F3A67675

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 sales3 (
  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
);

create table my_customers
(cust_id number primary key
,country_id number
,cust_state_province varchar2(100)
,cust_city varchar2(100)
);

 

ALTER TABLE sales3 
ADD CLUSTERING sales3 JOIN my_customers ON (sales3.cust_id = my_customers.cust_id)
                     JOIN my_products ON (sales3.prod_id = my_products.prod_id)
    BY INTERLEAVED ORDER *1
    YES ON LOAD YES ON DATA MOVEMENT
    WITHOUT MATERIALIZED ZONEMAP;

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

ALTER TABLE SALES1 DROP CLUSTERING;
ALTER TABLE MY_SALES DROP CLUSTERING;
ALTER TABLE SALES2 DROP CLUSTERING;

ALTER TABLE sales3 DROP CLUSTERING;
SELECT TABLE_NAME, CLUSTERING FROM DBA_TABLES WHERE OWNER='TEST';