ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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>
EXPLAIN PLAN FOR
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;
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;
EXPLAIN PLAN FOR
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;
SELECT * FROM TABLE(dbms_xplan.display);
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | 10 (0)|
| 1 | NESTED LOOPS | | 1 | 58 | 10 (0)|
| 2 | NESTED LOOPS | | 1 | 58 | 10 (0)|
| 3 | NESTED LOOPS | | 1 | 36 | 9 (0)|
|* 4 | TABLE ACCESS FULL | PERSON | 1 | 14 | 9 (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)|
------------------------------------------------------------------------------
Note
-----
- this is an adaptive plan
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>;
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;
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;
A variation of an ANSI CROSS JOIN in which only rows from the table on the left side of the join that produce a result set from the table_reference or collection_expression are returned
SELECT <column_name_list>
FROM <schema_name.object_name> [object_alias]
CROSS APPLY (
<inline_view_select_statement> <inline_view_alias>
WHERE <filter_condition>
[ORDER BY <column_list>]
SQL> SELECT d.department_name, v.employee_id, v.last_name
2 FROM departments d
3 CROSS APPLY (
4 SELECT *
5 FROM employees e
6 WHERE e.department_id = d.department_id) v
7 WHERE d.department_name IN ('Marketing', 'Operations', 'Public Relations')
8 ORDER BY d.department_name, v.employee_id;
A variation of an ANSI CROSS JOIN or an ANSI LEFT OUTER JOIN with left correlation support. You can specify a table_reference or collection_expression to the right of the APPLY keyword.
The table_reference can be a table, inline view, or TABLE collection expression. The collection_expression can be a subquery, a column, a function, or a collection constructor.
Regardless of its form, it must return a collection value — that is, a value whose type is nested table or varray. The table_reference or collection_expression can reference columns of tables defined in the FROM clause to the left of the APPLY keyword. This is called left correlation.
Specify OUTER APPLY to perform a variation of an ANSI LEFT OUTER JOIN. All rows from the table on the left side of the join are returned. Rows that do not produce a result set from table_reference or collection_expression have the NULL value in the corresponding column(s).
SELECT <column_name_list>
FROM <schema_name.object_name> [object_alias]
OUTER APPLY (
<inline_view_select_statement> <inline_view_alias>
WHERE <filter_condition>
[ORDER BY <column_list>]
SQL> SELECT d.department_name, v.employee_id, v.last_name
2
FROM departments d
3
OUTER APPLY (
4
SELECT *
5
FROM employees e
6
WHERE e.department_id = d.department_id) v
7
WHERE d.department_name IN ('Marketing', 'Operations', 'Public Relations')
8
ORDER by d.department_name, v.employee_id;
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29 | 899 | 5 (20)|
| 1 | SORT ORDER BY | | 29 | 899 | 5 (20)|
| 2 | NESTED LOOPS OUTER | | 29 | 899 | 4 (0)|
|* 3 | TABLE ACCESS FULL | DEPARTMENTS | 3 | 48 | 3 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID
BATCHED| EMPLOYEES | 10 | 150 | 1 (0)|
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)|
-------------------------------------------------------------------------------------------------
Note
-----
- this is an adaptive plan
Join Explain Plan Demos
Antijoin
conn hr/hr@pdbdev
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@pdbdev
EXPLAIN PLAN FOR
SELECT * FROM departments
WHERE EXISTS
(SELECT * FROM employees
WHERE departments.department_id = employees.department_id
AND employees.salary > 2500);
