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'); |