| General Information |
| Library Note |
| The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c. |
|
| |
| Demo Tables & Data |
| Join Demo Tables |
CREATE TABLE person (
person_id NUMBER(10),
first_name VARCHAR2(25) NOT NULL,
last_name VARCHAR2(25) NOT NULL,
title_1 VARCHAR2(5),
title_2 VARCHAR2(5))
ENABLE ROW MOVEMENT;
ALTER TABLE person
ADD CONSTRAINT pk_person
PRIMARY KEY (person_id)
USING INDEX;
CREATE TABLE person_role (
role_id VARCHAR2(1),
role_name VARCHAR2(20) NOT NULL)
ENABLE ROW MOVEMENT;
ALTER TABLE person_role
ADD CONSTRAINT pk_role
PRIMARY KEY (role_id)
USING INDEX;
CREATE TABLE person_role_ie (
person_role_id NUMBER(10),
person_id NUMBER(10) NOT NULL,
role_id VARCHAR2(1) NOT NULL)
ENABLE ROW MOVEMENT;
ALTER TABLE person_role_ie
ADD CONSTRAINT pk_person_role_ie
PRIMARY KEY (person_role_id)
USING INDEX;
CREATE TABLE title (
title_abbrev VARCHAR2(5),
title_name VARCHAR2(20))
ENABLE ROW MOVEMENT;
ALTER TABLE title
ADD CONSTRAINT pk_title
PRIMARY KEY (title_abbrev)
USING INDEX;
ALTER TABLE person_role_ie
ADD CONSTRAINT fk_person_role_ie_person
FOREIGN KEY (person_id)
REFERENCES person(person_id);
ALTER TABLE person_role_ie
ADD CONSTRAINT fk_person_role_ie_role
FOREIGN KEY (role_id)
REFERENCES person_role(role_id);
ALTER TABLE person
ADD CONSTRAINT fk_person_title1
FOREIGN KEY (title_1)
REFERENCES title(title_abbrev);
ALTER TABLE person
ADD CONSTRAINT fk_person_title2
FOREIGN KEY (title_2)
REFERENCES title(title_abbrev); |
| Demo Table Data Load |
INSERT INTO title VALUES
('BA', 'Bachelor of Arts');
INSERT INTO title VALUES
('BS', 'Bachelor of Science');
INSERT INTO title VALUES
('MS', 'Master of Science');
INSERT INTO title VALUES
('PhD', 'Doctor of Philosophy');
INSERT INTO title VALUES
('MD', 'Doctor of Medicine');
INSERT INTO person
(person_id, first_name, last_name, title_1)
VALUES
(1, 'Daniel', 'Morgan', 'BS');
INSERT INTO person
(person_id, first_name, last_name, title_1)
VALUES
(2, 'Anne', 'Sweet', 'BA');
INSERT INTO person
(person_id, first_name, last_name, title_1)
VALUES
(3, 'Muriel', 'Dance', 'PhD');
INSERT INTO person
(person_id, first_name, last_name, title_1)
VALUES
(4, 'Elizabeth', 'Scott', 'MS');
INSERT INTO person
(person_id, first_name, last_name)
VALUES
(5, 'Jacqueline', 'Stough');
INSERT INTO person_role VALUES (1, 'Administrator');
INSERT INTO person_role VALUES (2, 'Professor');
INSERT INTO person_role VALUES (3, 'Instructor');
INSERT INTO person_role VALUES (4, 'Employee');
INSERT INTO person_role VALUES (5, 'Student');
INSERT INTO person_role VALUES (9, 'Alumni');
CREATE SEQUENCE seq_pr_id START WITH 1;
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 1, 2);
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 1, 9);
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 2, 3);
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 1, 5);
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 3, 1);
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 3, 9);
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 4, 4);
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 5, 5);
INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 5, 9);
COMMIT;
exec dbms_stats.gather_schema_stats(USER, CASCADE=>TRUE); |
| |
| Traditional Joins |
| Two Table Inner Join |
SELECT <column_name>, <column_name>
FROM <table_name alias>, <table_name alias>
WHERE <alias.column_name> = <alias.column_name> |
SELECT p.last_name, t.title_name
FROM person p, title t
WHERE p.title_1 = t.title_abbrev;
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 128 | 6 (17)|
| 1 | MERGE JOIN | | 4 | 128 | 6 (17)|
| 2 | TABLE ACCESS BY INDEX ROWID | TITLE | 5 | 110 | 2 (0)|
| 3 | INDEX FULL SCAN | PK_TITLE | 5 | | 1 (0)|
|* 4 | SORT JOIN | | 4 | 40 | 4 (25)|
|* 5 | TABLE ACCESS FULL | PERSON | 4 | 40 | 3 (0)|
----------------------------------------------------------------------------- |
| Three Table Inner Join |
SELECT <column_name>, <column_name>
FROM <table_name alias>, <table_name alias>
WHERE <alias.column_name> = <alias.column_name>
AND <alias.column_name> = <alias.column_name> |
SELECT p.last_name, r.role_name
FROM person p, person_role_ie i, person_role r
WHERE p.person_id = i.person_id
AND i.role_id = r.role_id
ORDER BY p.person_id;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 234 | 11 (28)|
| 1 | SORT ORDER BY | | 9 | 234 | 11 (28)|
|* 2 | HASH JOIN | | 9 | 234 | 10 (20)|
| 3 | MERGE JOIN | | 9 | 135 | 6 (17)|
| 4 | TABLE ACCESS BY INDEX ROWID | PERSON | 5 | 50 | 2 (0)|
| 5 | INDEX FULL SCAN | PK_PERSON | 5 | | 1 (0)|
|* 6 | SORT JOIN | | 9 | 45 | 4 (25)|
| 7 | TABLE ACCESS FULL | PERSON_ROLE_IE | 9 | 45 | 3 (0)|
| 8 | TABLE ACCESS FULL | PERSON_ROLE | 6 | 66 | 3 (0)|
------------------------------------------------------------------------------------- |
| Left Outer Join |
SELECT <column_name>, <column_name>
FROM <table_name alias>, <table_name alias>
WHERE <alias.column_name = <alias.column_name>
AND <alias.column_name> = <alias.column_name> (+) |
SELECT p.last_name, t.title_name
FROM person p, title t
WHERE p.title_1 = t.title_abbrev(+);
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 160 | 7 (15)|
|* 1 | HASH JOIN OUTER | | 5 | 160 | 7 (15)|
| 2 | TABLE ACCESS FULL | PERSON | 5 | 50 | 3 (0)|
| 3 | TABLE ACCESS FULL | TITLE | 5 | 110 | 3 (0)|
----------------------------------------------------------------- |
| Right Outer Join |
SELECT <column_name>, <column_name>
FROM <table_name alias>, <table_name alias>
WHERE <alias.column_name> (+) = <alias.column_name>; |
SELECT p.last_name, t.title_name
FROM person p, title t
WHERE p.title_1(+) = t.title_abbrev;
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 160 | 6 (17)|
| 1 | MERGE JOIN OUTER | | 5 | 160 | 6 (17)|
| 2 | TABLE ACCESS BY INDEX ROWID | TITLE | 5 | 110 | 2 (0)|
| 3 | INDEX FULL SCAN | PK_TITLE | 5 | | 1 (0)|
|* 4 | SORT JOIN | | 4 | 40 | 4 (25)|
|* 5 | TABLE ACCESS FULL | PERSON | 4 | 40 | 3 (0)|
----------------------------------------------------------------------------- |
| Self Join |
SELECT <column_name>, <column_name>
FROM <table_name alias>, <table_name alias>, <table_name alias>
WHERE <alias.column_name> = < alias.column_name>
AND <alias.column_name> = <alias.column_name> |
UPDATE person
SET title_2 = 'PhD'
WHERE person_id = 1;
COMMIT;
SELECT p.last_name, t1.title_name, t2.title_name
FROM person p, title t1, title t2
WHERE p.title_1 = t1.title_abbrev
AND p.title_2 =t2.title_abbrev;
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | 4 (0)|
| 1 | NESTED LOOPS | | | | |
| 2 | NESTED LOOPS | | 1 | 58 | 4 (0)|
| 3 | NESTED LOOPS | | 1 | 36 | 3 (0)|
|* 4 | TABLE ACCESS FULL | PERSON | 1 | 14 | 3 (0)|
| 5 | TABLE ACCESS BY INDEX ROWID| TITLE | 1 | 22 | 0 (0)|
|* 6 | INDEX UNIQUE SCAN | PK_TITLE | 1 | | 0 (0)|
|* 7 | INDEX UNIQUE SCAN | PK_TITLE | 1 | | 0 (0)|
| 8 | TABLE ACCESS BY INDEX ROWID | TITLE | 1 | 22 | 1 (0)|
------------------------------------------------------------------------------ |
| |
| ANSI Joins |
| Inner Join |
SELECT <column_name>, <column_name>
FROM <table_name alias> INNER JOIN <table_name alias>
ON <alias.column_name> = <alias.column_name> |
SELECT p.last_name, t.title_name
FROM person p INNER JOIN title t
ON p.title_1 = t.title_abbrev;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 128 | 6 (17)|
| 1 | MERGE JOIN | | 4 | 128 | 6 (17)|
| 2 | TABLE ACCESS BY INDEX ROWID| TITLE | 5 | 110 | 2 (0)|
| 3 | INDEX FULL SCAN | PK_TITLE | 5 | | 1 (0)|
|* 4 | SORT JOIN | | 4 | 40 | 4 (25)|
|* 5 | TABLE ACCESS FULL | PERSON | 4 | 40 | 3 (0)|
---------------------------------------------------------------------------- |
| Left Outer Join |
SELECT <column_name>, <column_name>
FROM <table_name alias> LEFT OUTER JOIN <table_name alias>
ON <alias.column_name> = <alias.column_name> |
SELECT p.last_name, t.title_name
FROM person p LEFT OUTER JOIN title t
ON p.title_1 = t.title_abbrev;
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 160 | 7 (15)|
|* 1 | HASH JOIN OUTER | | 5 | 160 | 7 (15)|
| 2 | TABLE ACCESS FULL | PERSON | 5 | 50 | 3 (0)|
| 3 | TABLE ACCESS FULL | TITLE | 5 | 110 | 3 (0)|
----------------------------------------------------------------- |
| Right Outer Join |
SELECT <column_name>, <column_name>
FROM <table_name alias> RIGHT OUTER JOIN <table_name alias>
ON <alias.column_name> = <alias.column_name> |
SELECT p.last_name, t.title_name
FROM person p RIGHT OUTER JOIN title t
ON p.title_1 = t.title_abbrev;
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 160 | 6 (17)|
| 1 | MERGE JOIN OUTER | | 5 | 160 | 6 (17)|
| 2 | TABLE ACCESS BY INDEX ROWID | TITLE | 5 | 110 | 2 (0)|
| 3 | INDEX FULL SCAN | PK_TITLE | 5 | | 1 (0)|
|* 4 | SORT JOIN | | 4 | 40 | 4 (25)|
|* 5 | TABLE ACCESS FULL | PERSON | 4 | 40 | 3 (0)|
----------------------------------------------------------------------------- |
| Full Outer Join |
SELECT <column_name>, <column_name>
FROM <table_name alias> FULL OUTER JOIN <table_name alias>
ON <alias.column_name> = <alias.column_name> |
SELECT p.last_name, t.title_name
FROM person p FULL OUTER JOIN title t
ON p.title_1 = t.title_abbrev;
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 130 | 7 (15)|
| 1 | VIEW | VW_FOJ_0 | 5 | 130 | 7 (15)|
|* 2 | HASH JOIN FULL OUTER | | 5 | 160 | 7 (15)|
| 3 | TABLE ACCESS FULL | PERSON | 5 | 50 | 3 (0)|
| 4 | TABLE ACCESS FULL | TITLE | 5 | 110 | 3 (0)|
---------------------------------------------------------------------- |
| Natural Join |
SELECT <column_name>, <column_name>
FROM <table_name alias> NATURAL [INNER] JOIN <table_name alias> |
CREATE TABLE parents (
person_id NUMBER(5),
adult_name VARCHAR2(20),
comments VARCHAR2(40))
PCTFREE 0;
CREATE TABLE children (
parent_id NUMBER(5),
person_id NUMBER(5),
child_name VARCHAR2(20),
comments VARCHAR2(40))
PCTFREE 0;
INSERT INTO parents VALUES (1, 'Dan', 'So What');
INSERT INTO parents VALUES (2, 'Ted', 'Who Cares');
INSERT INTO children VALUES (1, 2, 'Anne', 'Who Cares');
INSERT INTO children VALUES (1, 1, 'Julia', 'Yeah Right');
INSERT INTO children VALUES (2, 1, 'David', 'So What');
COMMIT;
SELECT adult_name, child_name
FROM parents NATURAL JOIN children;
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 94 | 7 (15)|
|* 1 | HASH JOIN | | 1 | 94 | 7 (15)|
| 2 | TABLE ACCESS FULL | PARENTS | 2 | 94 | 3 (0)|
| 3 | TABLE ACCESS FULL | CHILDREN | 3 | 141 | 3 (0)|
------------------------------------------------------------------- |
| Self Join |
SELECT <column_name>, <column_name>
FROM <table_name alias> INNER JOIN <table_name alias>
ON <alias.column_name> = <alias.column_name>,
<table_name alias> INNER JOIN <table_name alias>
ON <alias .column_name> = <alias.column_name>; |
SELECT p1.last_name, t1.title_name, t2.title_name
FROM person p1 INNER JOIN title t1
ON p1.title_1 = t1.title_abbrev,
person p2 INNER JOIN title t2
ON p2.title_2 = t2.title_abbrev;
EXPLAIN PLAN FOR
SELECT p1.last_name, t1.title_name, t2.title_name
FROM person p1 INNER JOIN title t1
ON p1.title_1 = t1.title_abbrev,
person p2 INNER JOIN title t2
ON p2.title_2 = t2.title_abbrev;
SELECT * FROM TABLE(dbms_xplan.display);
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 (0)|
| 1 | NESTED LOOPS | | | |
| 2 | NESTED LOOPS | | 1 | 16 (0)|
| 3 | MERGE JOIN CARTESIAN | | 1 | 15 (0)|
| 4 | NESTED LOOPS | | | |
| 5 | NESTED LOOPS | | 1 | 10 (0)|
| 6 | TABLE ACCESS FULL | PERSON | 5 | 5 (0)|
| 7 | INDEX UNIQUE SCAN | PK_TITLE | 1 | 0 (0)|
| 8 | TABLE ACCESS BY INDEX ROWID| TITLE | 1 | 1 (0)|
| 9 | BUFFER SORT | | 5 | 14 (0)|
| 10 | TABLE ACCESS FULL | PERSON | 5 | 5 (0)|
| 11 | INDEX UNIQUE SCAN | PK_TITLE | 1 | 0 (0)|
| 12 | TABLE ACCESS BY INDEX ROWID | TITLE | 1 | 1 (0)|
----------------------------------------------------------------------- |
| Alternative Syntax Joining on commonly named column in both tables |
SELECT <column_name>, <column_name>
FROM <table_name alias> <join_type> <table_name alias>
USING (<common_column_name>) |
--does not work
SELECT s.srvr_id, s.status, i.location_code
FROM servers s INNER JOIN serv_inst i
USING (s.srvr_id)
WHERE rownum < 11;
-- does not work either
SELECT s.srvr_id, s.status, i.location_code
FROM servers s INNER JOIN serv_inst i
USING (srvr_id)
WHERE rownum < 11;
-- works
SELECT srvr_id, s.status, i.location_code
FROM servers s INNER JOIN serv_inst i
USING (srvr_id)
WHERE rownum < 11;
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 150 | 4 (0)|
|* 1 | COUNT STOPKEY | | | | |
| 2 | NESTED LOOPS | | | | |
| 3 | NESTED LOOPS | | 10 | 150 | 4 (0)|
| 4 | TABLE ACCESS FULL | SERV_INST | 10 | 90 | 2 (0)|
|* 5 | INDEX UNIQUE SCAN | PK_SERVERS | 1 | | 0 (0)|
| 6 | TABLE ACCESS BY INDEX ROWID | SERVERS | 1 | 6 | 1 (0)|
--------------------------------------------------------------------------------- |
| |
| Cartesian Join |
| Table And Data For Cartesian Product (Cross-Join) Demo |
CREATE TABLE cartesian (
join_column NUMBER(10));
CREATE TABLE product (
join_column NUMBER(10)); |
| Load Demo Tables |
BEGIN
FOR i in 1..1000 LOOP
INSERT INTO cartesian VALUES (i);
INSERT INTO product VALUES (i);
END LOOP;
COMMIT;
END;
/ |
| Inner Join |
SELECT COUNT(*)
FROM cartesian c, product p
WHERE c.join_column = p.join_column;
EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM cartesian c, product p
WHERE c.join_column = p.join_column;
SELECT * FROM TABLE(dbms_xplan.display);
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 11 (10)|
| 1 | SORT AGGREGATE | | 1 | 26 | |
| 2 | HASH JOIN | | 1000 | 26000 | 11 (10)|
| 3 | TABLE ACCESS FULL| CARTESIAN | 1000 | 13000 | 5 (0)|
| 4 | TABLE ACCESS FULL| PRODUCT | 1000 | 13000 | 5 (0)|
-------------------------------------------------------------------- |
| Not Inner Join |
SELECT COUNT(*)
FROM cartesian c, product p
WHERE c.join_column != p.join_column;
EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM cartesian c, product p
WHERE c.join_column != p.join_column;
SELECT * FROM TABLE(dbms_xplan.display);
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 3076 (2)|
| 1 | SORT AGGREGATE | | 1 | 26 | |
| 2 | NESTED LOOPS | | 999K| 24M| 3076 (2)|
| 3 | TABLE ACCESS FULL| CARTESIAN | 1000 | 13000 | 5 (0)|
| 4 | TABLE ACCESS FULL| PRODUCT | 999 | 12987 | 3 (0)|
-------------------------------------------------------------------- |
| Cartesian (Cross-Join) Product |
SELECT COUNT(*)
FROM cartesian, product;
EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM cartesian c, product p;
SELECT * FROM TABLE(dbms_xplan.display);
----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3076 (2)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | MERGE JOIN CARTESIAN| | 1000K| 3076 (2)|
| 3 | TABLE ACCESS FULL | CARTESIAN | 1000 | 5 (0)|
| 4 | BUFFER SORT | | 1000 | 3071 (2)|
| 5 | TABLE ACCESS FULL | PRODUCT | 1000 | 3 (0)|
---------------------------------------------------------------- |
| Intentional Cartesian (Cross-Join) Product |
SELECT <alias.column_name>, <alias.column_name>
FROM <table_name alias> CROSS JOIN <table_name alias> |
SELECT s.srvr_id, i.location_code
FROM servers s CROSS JOIN serv_inst i
WHERE rownum < 1001;
EXPLAIN PLAN FOR
SELECT s.srvr_id, i.location_code
FROM servers s CROSS JOIN serv_inst i;
-------------------------------------------------------------------------------------------
| Id | Operation | Name |Rows|Bytes| Cost (%CPU)|
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |140K|1238K| 276 (1)|
| 1 | MERGE JOIN CARTESIAN | |140K|1238K| 276 (1)|
| 2 | BITMAP CONVERSION TO ROWIDS | |999 |4995 | 3 (0)|
| 3 | BITMAP INDEX FAST FULL SCAN| BIX_SERV_INST_LOCATION_CODE| | | |
| 4 | BUFFER SORT | | 141| 564 | 273 (1)|
| 5 | INDEX FAST FULL SCAN | PK_SERVERS | 141| 564 | 0 (0)|
------------------------------------------------------------------------------------------- |
| A Cross-Join demo that does more than just demonstrate that they can be expensive |
CREATE TABLE t1 (
part_id VARCHAR2(10),
year VARCHAR2(4),
jan_amount NUMBER,
feb_amount NUMBER,
mar_amount NUMBER,
apr_amount NUMBER,
may_amount NUMBER,
jun_amount NUMBER,
jul_amount NUMBER,
aug_amount NUMBER,
sep_amount NUMBER,
oct_amount NUMBER,
nov_amount NUMBER,
dec_amount NUMBER);
INSERT INTO t1 VALUES ('A', '2012', 1,2,3,4,5,6,7,8,9,10,11,12);
INSERT INTO t1 VALUES ('B', '2012', 1,2,3,4,5,6,7,8,9,10,11,12);
CREATE TABLE t2 (
part_id VARCHAR2(10),
year VARCHAR2(4),
month VARCHAR2(3),
amount NUMBER);
SELECT * FROM t1;
INSERT INTO t2
WITH all_months AS (
SELECT TO_CHAR(ADD_MONTHS(SYSDATE,LEVEL),'MON','NLS_DATE_LANGUAGE=ENGLISH') AS mth_abbr
FROM dual
CONNECT BY LEVEL <= 12)
SELECT x.part_id, x.year, m.mth_abbr, COALESCE(
CASE m.mth_abbr
WHEN 'JAN' THEN x.jan_amount
WHEN 'FEB' THEN x.feb_amount
WHEN 'MAR' THEN x.mar_amount
WHEN 'APR' THEN x.apr_amount
WHEN 'MAY' THEN x.may_amount
WHEN 'JUN' THEN x.jun_amount
WHEN 'JUL' THEN x.jul_amount
WHEN 'AUG' THEN x.aug_amount
WHEN 'SEP' THEN x.sep_amount
WHEN 'OCT' THEN x.oct_amount
WHEN 'NOV' THEN x.nov_amount
WHEN 'DEC' THEN x.dec_amount
END, 0) AS amount
FROM t1 x
CROSS JOIN all_months m;
SELECT * FROM t2; |
| |
| Join Explain Plan Demos |
| Antijoin |
conn hr/hr@pdborcl
explain plan for
SELECT * FROM employees
WHERE department_id NOT IN (
SELECT department_id FROM departments
WHERE location_id = 1700);
select * from table(dbms_xplan.display);
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 7950 | 6 (17)|
|* 1 | HASH JOIN RIGHT ANTI SNA | | 106 | 7950 | 6 (17)|
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 21 | 147 | 2 (0)|
|* 3 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 21 | | 1 (0)|
| 4 | TABLE ACCESS FULL | EMPLOYEES | 107 | 7276 | 3 (0)|
------------------------------------------------------------------------------------- |
| Semijoin |
conn hr/hr@pdborcl
EXPLAIN PLAN FOR
SELECT * FROM departments
WHERE EXISTS
(SELECT * FROM employees
WHERE departments.department_id = employees.department_id
AND employees.salary > 2500);
SELECT * FROM TABLE(dbms_xplan.display);
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 270 | 6 (17)|
| 1 | MERGE JOIN SEMI | | 10 | 270 | 6 (17)|
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 540 | 2 (0)|
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)|
|* 4 | SORT UNIQUE | | 105 | 735 | 4 (25)|
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 105 | 735 | 3 (0)|
----------------------------------------------------------------------------- |
| |
| Join Related Queries |
| Column Join Usage |
conn sys@pdborcl as sysdba
set linesize 121
desc col_usage$
SELECT *
FROM col_usage$
WHERE obj# IN (
SELECT object_id
FROM dba_objects_ae
WHERE owner = 'UWCLASS'); |
