- About Optimizer Hints
- August 12–22: Online Training (EU shift)
- Clustered Indexes Dirty Secret
- FOSDEM Impressions
- June 8–18: Online Training (US shift)
- MySQL is to SQL like ??? to NoSQL
- Non-monetary micro sponsoring
- November 11-12 in Frankfurt am Main
- Online-Training in July and August
- Oracle + PostgreSQL
- Oracle Bootcamp
- PDF Registration
- Party time
- PostgreSQL Performance Event
- Quiz Results
- SQL Server Performance Kurs in Stuttgart
- SQL Server performance training in London
- Shipping Terms
- Springtime for SQL
- The two top performance problems caused by ORM tools
- Top Tweets January 2013
- Training Survey
- Training and Conference Dates
- Use The Index, Luke
2011-05-26Oracle 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; /
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_CHARis sensitive to
TO_CHARon both sides cancels that effect—so, it is implemented by a comparison of the weekday for a known Sunday (1st Jan 2012).