| 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, 'Jack', '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, 'Marcella', '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
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
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 / 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'); |
|
