Read consistency is an automatic implementation. It keeps a partial copy of the database in
the undo segments. The read-consistent image is constructed from the committed data in the
table and the old data that is being changed and is not yet committed from the undo segment.
When an insert, update, or delete operation is made on the database, the Oracle server takes
a copy of the data before it is changed and writes it to an undo segment.
All readers, except the one who issued the change, see the database as it existed before the
changes started; they view the undo segment’s “snapshot” of the data.
The relationship between the EMPLOYEES and DEPARTMENTS tables is
an equijoin; that is, values in the DEPARTMENT_ID column in both tables must be equal.
Often, this type of join involves primary and foreign key complements.
Note: Equijoins are also called simple joins or inner joins.
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM
employees e, departments d
WHERE e.department_id = d.department_id;
SELECT d.department_id, d.department_name, l.city
FROM
departments d, locations l
WHERE
d.location_id = l.location_id
AND d.department_id IN (20, 50);
NATURAL JOIN
SELECT department_id, department_name,
location_id, city
FROM
departments NATURAL JOIN locations ;
USING
SELECT employee_id, last_name,
location_id, department_id
FROM
employees JOIN departments USING (department_id) ;
ON
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM
employees e JOIN departments d
ON
(e.department_id = d.department_id);
NONEQUIJOINS
SELECT e.last_name, e.salary, j.grade_level
FROM
employees e, job_grades j
WHERE e.salary
BETWEEN j.lowest_sal AND j.highest_sal;
SELECT e.last_name, e.salary, j.grade_level
FROM
employees e JOIN job_grades j
ON
e.salary BETWEEN j.lowest_sal AND j.highest_sal;
The outer join operator is the plus sign (+)
SELECT e.last_name, e.department_id, d.department_name
FROM
employees e, departments d
WHERE e.department_id(+) = d.department_id ;
SELECT e.last_name, d.department_id, d.department_name
FROM
employees e RIGHT OUTER JOIN departments d
ON
(e.department_id = d.department_id) ;
Self join
SELECT worker.last_name || ‘ works for ‘
|| manager.last_name
FROM
employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;
UNDO
(5 minut temu)
select * from testable as of timestamp (SysDate – 5/1440)
select * from testtable as of timestamp(’08-Aug-2011 5:00:00 PM’,'DD-MON-YYYY HH:MI:SS AM’);
flashback table testtable to before drop;
