oracle database administration lecture 3 transactions sql language: additional information sql...
TRANSCRIPT
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
OR TRUE FALSE UNKNOWN
TRUE TRUE TRUE TRUE
FALSE TRUE FALSE UNKNOWN
UNKNOWN TRUE UNKNOWN UNKNOWN
AND TRUE FALSE UNKNOWN
TRUE TRUE FALSE UNKNOWN
FALSE FALSE FALSE FALSE
UNKNOWN UNKNOWN FALSE UNKNOWN
-- TRUE FALSE UNKNOWN
NOT FALSE TRUE UNKNOWN
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 insensitiveSELECT * FROM TABLE;
select * from table; -- identical statements
Select * From Table;
• 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); -- errorCREATE TABLE test ("NUMBER" NUMBER); -- okCREATE 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 rowsSELECT 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 - ExampleSELECT 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: SUMSELECT 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 - ExampleSELECT 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 - ExampleSELECT * 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 Functionsfunction(...) OVER (
PARTITION BY …
ORDER BY …
<window clause>)
• PARTITION BY lets you divide records into separate partitions/groups
• Each group is aggregated separately
Analytic Functions - ExampleSELECT 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 - ExampleResult:DID NAME SALARY TOTAL_DEPT TOTAL RNUM_DEPT RNUM_TOTAL
--- --------------- ---------- ---------- ---------- ---------- ----------
1 Secretary of th 2500 2500 2500 1 1
e Secretary
1 The Big Boss 60000 62500 62500 2 2
1 The Big Boss Se 2000 64500 64500 3 3
cretary
2 Abacki 2000 2000 66500 1 4
2 Babacki 1500 6500 71000 2 5
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;