Oracle Example Scripts for “Clustering Data”


This page for

This section contains the create and insert statements to run the examples from Chapter 5 in an Oracle 11gR2 database.

Index-Organized Table

The following creates a second sales table as index-organized table. A secondary index on SALE_DATE is created.

CREATE TABLE sales_iot (
  sale_id       NUMBER NOT NULL,
  employee_id   NUMBER NOT NULL,
  subsidiary_id NUMBER NOT NULL,
  sale_date     DATE   NOT NULL,
  eur_value     NUMBER(17,2) NOT NULL,
  junk          CHAR(200),
  CONSTRAINT sales_iot_pk     
     PRIMARY KEY (sale_id),
  CONSTRAINT sales_iot_emp_fk 
     FOREIGN KEY          (subsidiary_id, employee_id)
      REFERENCES employees(subsidiary_id, employee_id)
) ORGANIZATION INDEX;

EXEC DBMS_RANDOM.SEED(0);

INSERT INTO sales_iot (sale_id
                     , subsidiary_id, employee_id
                     , sale_date, eur_value, junk)
SELECT rownum, data.*
  FROM (
       SELECT e.subsidiary_id, e.employee_id
            , TRUNC (SYSDATE 
                   - DBMS_RANDOM.VALUE(0, 3650)) sale_date
            , DBMS_RANDOM.VALUE(10,10000)/100 eur_value
            , 'junk'
         FROM employees e
            , ( SELECT level n
                  FROM dual
               CONNECT BY level < 1800
              ) gen
        WHERE MOD(employee_id, 7) = 4
          AND gen.n < employee_id / 5
        ORDER BY sale_date
       ) data;

CREATE INDEX sales_iot_date ON sales_iot (sale_date);

BEGIN
     DBMS_STATS.GATHER_TABLE_STATS(null, 'SALES_IOT', 
     METHOD_OPT=>'for all indexed columns', CASCADE => true);
END;
/

About the Author

As an author, trainer, and coach Markus Winand specializes in helping developers cope with SQL performance issues. He also published the book SQL Performance Explained and tweets his best performance tips via @SQLPerfTips.http://winand.at/

Recent Questions at Ask.Use-The-Index-Luke.com

0
votes
1
answer
69
views
0
votes
0
answers
321
views

Fanout in R-Tree

Mar 27 at 08:07 jamie 1
tree indexing
0
votes
1
answer
104
views

Think About It

Mar 26 at 12:54 Markus Winand ♦♦ 511
reflection