Oracle Database Administration

1. Oracle Database Administration Lecture 3 • Transactions • SQL Language: Additional information • SQL Language: Analytic Functions

2. 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

3. 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)

4. 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

5. 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

6. The ACID model • Database transactions should follow the ACID model • A – Atomicity • C – Consistency • I – Isolation • D – Durability

7. 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

8. Consistency • Consistency states that only valid data will be written to the database • Transactions violating consistency rules (constraints) will be rolled back

9. 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

10. Durability • Transaction once committed can never be lost • Durability is ensured through database backups and redo logs

11. 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)

12. 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.

13. 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

14. 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)

15. 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'

16. NULL Logical conditions

17. 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

18. 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

19. 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”

20. Character case, quotes • SQL language is case insensitive SELECT * 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);

21. 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

22. 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>)

23. 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 ...

24. 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

25. 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

26. 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

27. 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

28. 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

29. 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;

30. Analytic Functions - Example Result: 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

31. 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;