Transcript Lecture 3

Oracle Database Administration
Lecture 3

Transactions

SQL Language: Additional information

SQL Language: Analytic Functions
Database transactions


Every operation in Oracle is executed in a
transaction
Transactions are started automatically when
user executes first statement:

after opening new database session

after ending previous transaction
Transactions


Transaction can end with:

COMMIT – changes are saved in the database

ROLLBACK – changes are canceled and removed
from the database
Transaction ends automatically when:

user properly disconnects from the database
(transaction is committed)

database crashes, or user session disconnects
improperly (transaction is rolled back)
Transactions and sessions


Separate sessions created by the same user
work in separate transactions
Transaction is always opened by some
database session

single session usually has only one open
transaction (exception: autonomous transactions)

once transaction is committed or rolled back,
session opens new transaction
Transactions and savepoints



Savepoint saves current state of a transaction
It is possible to roll back to a savepoint (partial
rollback of a transaction)
Example:
delete from test;
savepoint s1;
insert into test(id) values (1);
rollback to s1;
commit;
-- table test is empty
The ACID model

Database transactions should follow the ACID
model

A – Atomicity

C – Consistency

I – Isolation

D – Durability
Atomicity

Database transaction should be atomic:

entire transaction should be treated as a single
operation

other sessions should not see results of
uncommitted transactions

if a database crashes or session disconnects,
partial (uncommitted) transactions should not be
visible, they should automatically be rolled back
Consistency


Consistency states that only valid data will be
written to the database
Transactions violating consistency rules
(constraints) will be rolled back
Isolation



Multiple transactions should be isolated from
each other
If two transactions are issued at the same time,
one should execute before another, so that they
do not interfere with each other
In practice: single transaction should not see
results of another transactions executing at the
same time
Durability


Transaction once committed can never be lost
Durability is ensured through database backups
and redo logs
Transaction concurrency

Typical problems related to concurrency:

dirty read - transaction reads data that is not yet
committed (violates atomicity and isolation)

non repeatable read - transaction reads the same
data twice and sees different results, because it
was modified and committed by another transaction
(violates isolation)
phantoms - transaction executes the same query
twice and in the second execution there are
additional rows that were inserted and committed in
the mean time (violates isolation)

Isolation levels

Standard isolation levels:

READ UNCOMMITTED - transactions see
uncommitted data from other transactions, dirty
read, non repeatable read and phantoms are
possible

READ COMMITTED - transactions see committed
data, phantoms and unrepeatable reads are
possible.

REPEATABLE READ - the same query executed
twice gives the same results with the exception of
phantoms.

SERIALIZABLE - transactions are fully isolated.
Oracle's isolation levels

Oracle supports the following isolation levels:

READ COMMITTED – default isolation level

SERIALIZABLE – can be activated with:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

READ ONLY – non standard level, can be activated
with:
SET TRANSACTION READ ONLY


Isolation level can only be set as the first
statement in the transaction
If set – isolation level remains until the end of
the transaction
SQL Language

NULL values in SQL

NULL value has special “no value” meaning

NULL compared with any other value results in
NULL:




SELECT * FROM users WHERE login = NULL
SELECT * FROM users WHERE login != NULL
Both statements return 0 rows
Logical condition can have 3 values:

TRUE

FALSE

NULL (UNKNOWN)
NULL logical conditions

Example:
INSERT INTO users (login, name) VALUES (NULL, 'Some
user');
INSERT INTO users (login, name)
VALUES ('user1', 'Some other user');
SELECT * FROM users WHERE login = 'user1';
SELECT * FROM users WHERE NOT (login = 'user1');-– does
not
–- return 'Some user'
NULL Logical conditions
--
TRUE
NOT FALSE
FALSE UNKNOW
N
TRUE
UNKNOW
N
OR
TRUE
FALSE
UNKNOW
N
TRUE
TRUE
TRUE
TRUE
FALSE
TRUE
FALSE
UNKNOW
N
UNKNOW
N
TRUE
UNKNOW
N
UNKNOW
N
AND
TRUE
FALSE
UNKNOW
N
TRUE
TRUE
FALSE
UNKNOW
N
FALSE
FALSE
FALSE
FALSE
UNKNOW
N UNKNOWN FALSE
UNKNOW
N
SQL Language - Functions

Text functions:

TO_CHAR – convert date, number to text

TRIM – trim text

UPPER, LOWER – change case

INSTR – search string

LENGTH – measure length of a string

TRANSLATE – replace characters

REPLACE – replace texts
SQL Language - Functions

Number functions:

TO_NUMBER – text to number

ROUND – round to specified precision

FLOOR, CEIL – round up or down

SIN, COS, SINH, COSH, POWER, LN, LOG

MOD – x modulo y
SQL Language - Functions

Date functions:

SYSDATE – current date

TO_DATE – text to date

TO_CHAR – date to text

ADD_MONTHS – add or subtract months from a
date

MONTHS_BETWEEN – number of months between
two dates

NEXT_DAY – next day of week after given date, for
example: “next Tuesday”
Character case, quotes
• SQL language is case insensitive
SELECT * FROM TABLE;
select * from table;
Select * From Table;
-- identical statements
• Character literals are case sensitive:
SELECT * FROM USER_TABLES WHERE TABLE_NAME = 'TEST';
SELECT * FROM USER_TABLES WHERE TABLE_NAME = 'test';
-- different statements
• Character literals are enclosed by single quotes
• Double quotes can be used to quote column
and table names:
CREATE TABLE "test" (ID NUMBER PRIMARY KEY);
Double quotes
• Double quotes, when used:
– make the name case sensitive
– make it possible to use reserved word as identifier
CREATE TABLE test (NUMBER NUMBER); -- error
CREATE TABLE test ("NUMBER" NUMBER); -- ok
CREATE TABLE test1 ("NUMBER" NUMBER, "Number" NUMBER,
"number" NUMBER) – ok
• When not using quotes, Oracle converts the
name to upper case:
CREATE TABLE test (ID NUMBER);
SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = 'test'; --> 0
rows
SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = 'TEST'; --> 1
row
Analytic Functions
• Analytic Functions:
– Advanced SQL feature, can be used for reporting,
or advanced SQL statements
– Similar to aggregate (groupping) functions, but
return result for every row in the table, not one
result for entire group of rows
– Syntax:
function(...) OVER (
PARTITION BY …
ORDER BY …
<window clause>)
Analytic Functions - Example
SELECT e.name,
e.salary,
sum(e.salary) OVER (order by name) AS total
FROM employees e
ORDER BY e.name
NAME
SALARY
TOTAL
-------------------- ---------- ---------Abacki
2000
2000
Babacki
1500
3500
Cabacki
1200
4700
Dabacki
1100
5800
Fabacki
1600
7400
Gabacki
4100
11500
...
Analytic Functions: SUM
SELECT e.name,
e.salary,
sum(e.salary) OVER (order by name) AS total
FROM employees e
ORDER BY e.name
Function SUM returns sum of all values from
the beginning of the table until current record
 Records are sorted by name: OVER (order by

name)

Sort order for the SELECT statement could be
different than sort order for the Analytic Function
Analytic Functions
• Other Analytic Functions:
– ROW_NUMBER – assign numbers to each row: 1,
2, 3 etc., similar to ROWNUM pseudo column
– RANK – similar to ROW_NUMBER, but identical
values get the same rank. For example: 1, 2, 2, 4
– DENSE_RANK – similar to RANK, but if there are
identical values, there is no gap in numbering. For
example: 1, 2, 2, 3
– AVG, MAX, MIN, COUNT – The same as aggregate
functions
– LAG – gives access to previous rows
– LEAD – gives access to next rows
Analytic Functions - Example
SELECT e.name,
ROW_NUMBER() OVER (order by name) RNUM,
RANK() OVER (order by name) RANK,
DENSE_RANK() OVER (order by name) DRANK
FROM emp e
ORDER BY e.name;
NAME
RNUM
RANK
DRANK
--------------- ---------- ---------- ---------Abacki
1
1
1
Babacki
2
2
2
Babacki
3
2
2
Babacki
4
2
2
Cabacki
5
5
3
Dabacki
6
6
4
Analytic Functions - Example
SELECT * FROM (
SELECT e.name,
ROW_NUMBER() OVER (order by name) RNUM
FROM emp e
) WHERE RNUM BETWEEN 5 AND 10 ORDER BY name;
NAME
RNUM
--------------- ---------Cabacki
5
Dabacki
6
Fabacki
7
Gabacki
8
Habacki
9
Human resources Boss
10
Analytic Functions
function(...) OVER (
PARTITION BY …
ORDER BY …
<window clause>)
• PARTITION BY lets you divide records into separate
partitions/groups
• Each group is aggregated separately
Analytic Functions - Example
SELECT e.dept_id, e.name, e.salary,
sum(e.salary) OVER (PARTITION BY dept_id
ORDER BY name) AS total_dept,
sum(e.salary) OVER (
ORDER BY dept_id, name) AS total,
row_number() OVER (PARTITION BY dept_id
ORDER BY name) AS rnum_dept,
row_number() OVER (
ORDER BY dept_id, name) AS rnum_total
FROM emp e
ORDER BY e.dept_id, e.name;
Analytic Functions - Example
Result:
DID NAME
SALARY TOTAL_DEPT
TOTAL
RNUM_DEPT RNUM_TOTAL
--- --------------- ---------- ---------- ---------- ---------- ----------
1 Secretary of th
2500
2500
2500
1
1
60000
62500
62500
2
2
2000
64500
64500
3
3
2 Abacki
2000
2000
66500
1
4
2 Babacki
1500
6500
71000
2
5
e Secretary
1 The Big Boss
1 The Big Boss Se
cretary
Analytic Functions - Example

Display 3 employees with highest salary in each
department:
SELECT * FROM (
SELECT e.name, e.salary, e.dept_id,
ROW_NUMBER() OVER (PARTITION BY dept_id
ORDER BY salary DESC) rnum
FROM emp e)
WHERE rnum <= 3;