Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
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);
