Oracle Join Examples


This section contains the create, insert and PL/SQL code to run the examples from Chapter 4, “The Join Operation” in an Oracle 11gR2 database.

CREATE TABLE sales (
  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,
  product_id    NUMBER NOT NULL,
  quantity      number NOT NULL,
  junk          CHAR(200),
  CONSTRAINT sales_pk     
     PRIMARY KEY (sale_id),
  CONSTRAINT sales_emp_fk 
     FOREIGN KEY          (subsidiary_id, employee_id)
      REFERENCES employees(subsidiary_id, employee_id)
);

EXEC DBMS_RANDOM.SEED(0);

INSERT INTO sales (sale_id
                 , subsidiary_id, employee_id
                 , sale_date, eur_value
                 , product_id, quantity
                 , 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
            , TRUNC(DBMS_RANDOM.VALUE(1,25)) product_id
            , TRUNC(DBMS_RANDOM.VALUE(1,5)) quantity
            , '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
 WHERE TO_CHAR(sale_date, 'D') 
    != TO_CHAR(TO_DATE('2012-01-01', 'YYYY-MM-DD'), 'D');

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

Notes:

  • The rows are inserted chronologically to reflect a natural table growth.

  • Only a small fraction of employees have sales at all.

  • No sales on Sundays. This is, however, hard to accomplish because Oracle’s TO_CHAR is sensitive to NLS_TERRITORY settings. Using TO_CHAR on both sides cancels that effect—so, it is implemented by a comparison of the weekday for a known Sunday (1st Jan 2012).

About the Author

Photo of Markus Winand
Markus Winand tunes developers for high SQL performance. He also published the book SQL Performance Explained and offers in-house training as well as remote coaching at http://winand.at/

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

0
votes
1
answer
49
views

Database design suggestions for a data scraping/warehouse application?

Aug 27 at 09:29 Markus Winand ♦♦ 656
mysql optimization database
1
vote
1
answer
209
views

How to query for "previous page" with keyset pagination?

Aug 22 at 04:21 alextsg 16
pagination postgresql
0
votes
0
answers
246
views