15 Using Clusters and IndexOrganized Tables Copyright Oracle

Objectives • Creating and maintaining clusters • Using index-organized tables • Retrieving information about clusters and tables from the data dictionary 15 -2 Copyright � Oracle Corporation, 1998. All rights reserved.

Distribution of Rows Within a Table Cluster Index-organized table Ordering of Rows Random 15

Clusters ORD_NO ----101 102 102 101 PROD -----A 4102 A 2091 G 7830 N 9587 A 5675 W 0824 QTY -----20 11 20 26 19 10 . . . ORD_NO -----101 102 ORD_DT CUST_CD -----05 -JAN-97 R 01 07 -JAN-97 N 45 Unclustered ORD and ITEM tables 15 -4 Cluster Key (ORD_NO) 101 ORD_DT CUST_CD 05 -JAN-97 R 01 PROD QTY A 4102 20 A 5675 19 W 0824 10 102 ORD_DT CUST_CD 07 -JAN-97 N 45 PROD QTY A 2091 11 G 7830 20 N 9587 26 Clustered ORD and ITEM tables Copyright � Oracle Corporation, 1998. All rights reserved.

Cluster Types Index cluster Hash function 15 -5 Copyright � Oracle Corporation, 1998. All

Creating Index Clusters 1. Create a cluster. CREATE CLUSTER scott. ord_clu )ord_no NUMBER(3)) SIZE 200 TABLESPACE DATA 01 STORAGE(INITIAL 5 M NEXT 5 M PCTINCREASE 0); 2. Create a cluster index. CREATE INDEX scott. ord_clu_idx ON CLUSTER scott. ord_clu TABLESPACE INDX 01 STORAGE(INITIAL 1 M NEXT 1 M PCTINCREASE 0); 15 -6 Copyright � Oracle Corporation, 1998. All rights reserved.

Creating Index Clusters 3. Create tables in the cluster. CREATE TABLE scott. ord (ord_no NUMBER(3) CONSTRAINT ord_pk PRIMARY KEY, ord_dt DATE, cust_cd VARCHAR 2(3)) CLUSTER scott. ord_clu(ord_no); CREATE TABLE scott. item (ord_no NUMBER(3) CONSTRAINT item_ord_fk REFERENCES scott. ord, prod VARCHAR 2(5), qty NUMBER(3), CONSTRAINT item_pk PRIMARY KEY(ord_no, prod)) CLUSTER scott. ord_clu(ord_no); 15 -7 Copyright � Oracle Corporation, 1998. All rights reserved.

Creating Hash Clusters 1. Create a cluster. CREATE CLUSTER scott. off_clu (country VARCHAR 2(2), postcode VARCHAR 2(8)) SIZE 500 HASHKEYS 1000 TABLESPACE DATA 01 STORAGE(INITIAL 5 M NEXT 5 M PCTINCREASE 0); 2. Create tables in a cluster. CREATE TABLE scott. office( office_cd NUMBER(3), cost_ctr NUMBER(3), country VARCHAR 2(2), postcode VARCHAR 2(8)) CLUSTER scott. off_clu(country, postcode); 15 -8 Copyright � Oracle Corporation, 1998. All rights reserved.

Parameters Specific to Hash Clusters • HASHKEYS: Number of key values • HASH IS: Optional user-defined hash function Key 21 Key 12 Key 3 Preallocated blocks 15 -9 Copyright � Oracle Corporation, 1998. All rights reserved. Key 22 Overflow block

Dropping Clusters Use INCLUDING TABLES to drop tables and cluster DROP CLUSTER scott. ord_clu INCLUDING TABLES; or drop tables before dropping cluster. DROP TABLE scott. ord; DROP TABLE scott. item; DROP CLUSTER scott. ord_clu; 15 -10 Copyright � Oracle Corporation, 1998. All rights reserved.

Index-Organized Tables Indexed access on table Accessing indexorganized table ROWID Non-key columns Key column

Creating Index-Organized Tables CREATE TABLE scott. sales ( office_cd NUMBER(3), qtr_end DATE, revenue NUMBER(10, 2), review VARCHAR 2(1000), CONSTRAINT sales_pk PRIMARY KEY(office_code, qtr_end)) ORGANIZATION INDEX TABLESPACE data 01 PCTTHRESHOLD 20 OVERFLOW TABLESPACE data 02; 15 -12 Copyright � Oracle Corporation, 1998. All rights reserved.