In part 1 I have written on below subjects;
1- Flashback Versions Query, Drop Table and Recyclebin Management
2- How shared pool works in Oracle and binding example
3- The Storage Hierarchy Summary in an Oracle Database
4- Concurrency and Isolation Levels Concepts
5- Automated Checkpoint Tuning and Mean time to recover(MTTR) Concepts
6- Concurrency and Consistency Concepts
7- Latch and Lock Concepts
In part 2 I have written on below subject;
8- Oracle Database Architecture: Overview
On this post I want to discuss Atomicity and NULL values with Oracle.
9- How Atomicity is implemented on Oracle
One of the primary tests for DBMS reliability is what’s known as the ACID test. ACID stands for Atomicity, Consistency, Isolation and Durability. ACID-compliant systems are as close as you can get to guaranteed not to lose your data. Essentially, as long as your database files are intact, you are guaranteed that your data is consistent. Meaning of the word “atomic”: indivisible, and each transaction is “atomic”. Atomicity is a short-hand way of saying that when a transaction that changes the data occurs, either the whole transaction occurs, or none of the transaction occurs.
Atomicity is implemented in Oracle(and most other DBMSs) by commit and rollback statements. Oracle does not return from a COMMIT statement until the redo log changes corresponding to that commit have been physically written to disk, not just to buffer. Durability, the final property in the ACID test, means that any changes made by committed transactions are persistent: they are permanent, and if a failure occurs, the information in the database will reflect all transactions committed before the failure.
I love the atomicity examples on Mr.Thomas Kyte’s book transactions chapter, really improves understanding;
DROP TABLE t2 PURGE ; CREATE TABLE t2 ( count INT ); INSERT INTO t2 VALUES ( 0 ); DROP TABLE t1 PURGE ; CREATE TABLE t1 ( x INT CHECK ( x>0 ) ); CREATE TRIGGER t1_trigger BEFORE INSERT OR DELETE ON t1 FOR EACH ROW BEGIN IF ( INSERTING ) THEN UPDATE t2 SET count = count + 1; ELSE UPDATE t2 SET count = count - 1; END IF; DBMS_OUTPUT.PUT_LINE( 'I fired and updated ' || SQL%ROWCOUNT || ' rows' ); END; / SET SERVEROUTPUT ON; INSERT INTO t1 VALUES ( 1 ); I fired and updated 1 rows 1 row created. INSERT INTO t1 VALUES ( -1 ); I fired and updated 1 rows INSERT INTO t1 VALUES ( -1 ) * ERROR at line 1: ORA-02290: check constraint (HR.SYS_C005726) violated SELECT * FROM t2; COUNT ---------- 1
Initially the row in t2 was 0, after the first successful insert the row in t2 incremented by trigger to value 1. With the second insert 02290 error recieved, since the trigger is a part of the atomicity it is also rollbacked with the second insert statement and value on t2 table still remains 1. In this second example we will use a procedure to show the same behavior;
CREATE OR REPLACE PROCEDURE p AS BEGIN INSERT INTO t1 VALUES ( 1 ); INSERT INTO t1 VALUES (-1 ); END; / SELECT * FROM t1; X ---------- 1 SELECT * FROM t2; COUNT ---------- 1 BEGIN p; END; / I fired and updated 1 rows I fired and updated 1 rows BEGIN * ERROR at line 1: ORA-02290: check constraint (HR.SYS_C005726) violated ORA-06512: at “HR.P”, line 5 ORA-06512: at line 2 SELECT * FROM t1; X ---------- 1 SELECT * FROM t2; COUNT ---------- 1
This time also the first insert is rollbacked since the procedure is atomic. Ok this time lets see how exception handling affects the behaviour;
BEGIN p; EXCEPTION WHEN OTHERS THEN NULL; -- kara delik END; / I fired and updated 1 rows I fired and updated 1 rows PL/SQL procedure successfully completed. SELECT * FROM t1; X ---------- 1 SELECT * FROM t2; COUNT ---------- 2
Since the constraint exception is handled, Oracle does not rollback this time, value on t2 table is 2. Be carefull with your exception handling strategy, these give flexablity to developer but it may break the atomicity if used uncarefully and cause corrupted data.
And especially “when others” exception block with “null” implementation must only be used for testing pusposes, it is as dangerous as a bomb in your code..
Another great example is this update statement for atomicity;
DROP TABLE t3 PURGE ; CREATE TABLE t3 ( x INT UNIQUE ); INSERT INTO t3 VALUES ( 1 ); INSERT INTO t3 VALUES ( 2 ); UPDATE t3 SET x = x + 1; commit ; select * from t3; X ---------- 2 3
Because of the unique constraint Oracle would not be able to do this update if violation was checked after each row.
10- NULL values and Oracle
Allowing NULL values into your columns introduces a whole new degree of uncertainty into your database. The primary reason behind is comparing NULL to a value will result neither TRUE or FALSE, in fact we may say it is UNKNOWN. Here is a simple demostration;
set serveroutput on
create or replace procedure IsNotApple(b varchar2) as
begin
if b !='Apple' then
dbms_output.put_line('Not Apple');
else
dbms_output.put_line('Apple');
end if;
end;
/
begin
isNotApple(Null);
end;
/
Apple
PL/SQL procedure successfully completed
Oracle implements NULL values as ANSI SQL requires them to be implemented. But databases are different, experience in one will in part carry over to another. You must be ready for some fundamental differences, such as locking, as well as some very minor differences, such as treatment of Nulls in this example, can have unexpected impacts on your applications. Being aware of the database and how it works and how its features are implemented is the only way to overcome these issues, again another “read the documentation” conclusion :)
drop table tab_null_test purge ; create table tab_null_test ( team_no number primary key, team_name varchar2(64) unique, availability varchar2(1)); insert into tab_null_test values (1, ‘Bir', NULL); insert into tab_null_test values (2, ‘Iki', ‘Y'); insert into tab_null_test values (3, ‘Uc', ‘N'); commit; analyze table tab_null_test compute statistics for table for all columns; set null ^ -- will print "^" instead of NULL select * from tab_null_test ; TEAM_NO TEAM_NAME A ---------- ---------------------------------------------------------------- - 1 Bir ^ 2 Iki Y 3 Uc N select count(team_no) from tab_null_test ; COUNT(TEAM_NO) -------------- 3 select count(availability) from tab_null_test ; COUNT(AVAILABILITY) ------------------- 2 select * from tab_null_test where availability <> ‘Y' ; TEAM_NO TEAM_NAME A ---------- ---------------------------------------------------------------- - 3 Uc N select * from tab_null_test where nvl(availability, ‘N') <> ‘Y' ; TEAM_NO TEAM_NAME A ---------- ---------------------------------------------------------------- - 1 Bir ^ 3 Uc N select * from tab_null_test where availability = NULL ; no rows selected select * from tab_null_test where availability <> NULL ; no rows selected select * from tab_null_test where availability IS NULL ; TEAM_NO TEAM_NAME A ---------- ---------------------------------------------------------------- - 1 Bir ^
Also if you need to find NULL values with B*tree indexes on Oracle, you must be carefull;
drop table tab_null_test purge; create table tab_null_test ( x int, y int ); create unique index unique_tab_null_test on tab_null_test(x,y); insert into tab_null_test values ( 1, 1 ); insert into tab_null_test values ( 1, NULL ); insert into tab_null_test values ( NULL, 1 ); insert into tab_null_test values ( NULL, NULL ); analyze table tab_null_test compute statistics for table for all columns; analyze index unique_tab_null_test validate structure; select name, lf_rows from index_stats; NAME LF_ROWS ------------------------------ ---------- UNIQUE_TAB_NULL_TEST 3
The table has four rows, whereas the index only has three. The first three rows, where at least one of the index key elements was not Null, are in the index. The last row, with (NULL, NULL) is not in the index.
insert into tab_null_test values ( NULL, NULL ); 1 row created. insert into tab_null_test values ( NULL, 1 ); * ERROR at line 1: ORA-00001: unique constraint (HR.UNIQUE_TAB_NULL_TEST) violated insert into tab_null_test values ( 1, NULL ); * ERROR at line 1: ORA-00001: unique constraint (HR.UNIQUE_TAB_NULL_TEST) violated select x, y, count(*) from tab_null_test group by x,y having count(*) > 1; X Y COUNT(*) ---------- ---------- ---------- ^ ^ 2
In Oracle, (NULL, NULL) is in deed not equal to (NULL, NULL). That is something to consider: each unique constraint should have at least one NOT NULL column in order to be truly unique.
set autotrace traceonly explain
select * from tab_null_test where x is null;
Execution Plan
----------------------------------------------------------
Plan hash value: 3396731321
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 6 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TAB_NULL_TEST | 3 | 6 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X" IS NULL)
set autotrace off
drop table tab_null_test purge;
create table tab_null_test ( x int, y int NOT NULL );
create unique index unique_tab_null_test on tab_null_test(x,y);
insert into tab_null_test values ( 1, 1 );
insert into tab_null_test values ( NULL, 1 );
analyze table tab_null_test compute statistics for all columns;
set autotrace traceonly explain
select * from tab_null_test where x is null;
Execution Plan
----------------------------------------------------------
Plan hash value: 2452341312
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| UNIQUE_TAB_NULL_TEST | 1 | 4 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("X" IS NULL)
NULLS can also affect the outcome of the analytic functions, especially when you use a descending sort. By default, NULLS are greater than any other value;
set autot off drop table emp purge ; create table emp as select employee_id empno, first_name||' ‘||last_name ename, commission_pct comm from employees order by employee_id ; analyze table emp compute statistics ; select ename, comm from emp order by comm desc; ENAME COMM ---------------------------------------------- ---------- Steven King ^ Neena Kochhar ^ Lex De Haan ^ Alexander Hunold ^ Bruce Ernst ^ David Austin ^ Valli Pataballa ^ Diana Lorentz ^ Nancy Greenberg ^ Daniel Faviet ^ John Chen ^ ... William Smith ,15 Oliver Tuvault ,15 Mattea Marvins ,1 David Lee ,1 Sundar Ande ,1 Charles Johnson ,1 Sundita Kumar ,1 Amit Banda ,1 107 rows selected. select ename, comm, dr from (select ename, comm, dense_rank() over (order by comm desc) dr from emp) where dr <= 5 order by comm; ENAME COMM DR ---------------------------------------------- ---------- ---------- David Bernstein ,25 5 Peter Hall ,25 5 Clara Vishney ,25 5 Sarath Sewall ,25 5 Alyssa Hutton ,25 5 Lisa Ozer ,25 5 Peter Tucker ,3 4 Lindsey Smith ,3 4 Ellen Abel ,3 4 Gerald Cambrault ,3 4 Alberto Errazuriz ,3 4 Karen Partners ,3 4 Louise Doran ,3 4 Patrick Sully ,35 3 Janette King ,35 3 Allan McEwen ,35 3 John Russell ,4 2 Steven King ^ 1 Neena Kochhar ^ 1 Lex De Haan ^ 1 Alexander Hunold ^ 1 Bruce Ernst ^ 1 ...
While this may technically be “correct” it probably is not what you really wanted. You would either not want NULLS to be considered at all or to have NULLS be interpreted as “small” in this case. So, we can either remove NULLS from consideration, using “where comm is not null”:
select ename, comm, dr from (select ename, comm, dense_rank() over (order by comm desc) dr from emp where comm is not null) where dr <= 5 order by comm desc; ENAME COMM DR ---------------------------------------------- ---------- ---------- John Russell ,4 1 Janette King ,35 2 Allan McEwen ,35 2 Patrick Sully ,35 2 Karen Partners ,3 3 Alberto Errazuriz ,3 3 Peter Tucker ,3 3 Gerald Cambrault ,3 3 Louise Doran ,3 3 Ellen Abel ,3 3 Lindsey Smith ,3 3 ...
or we can use the NULLS LAST extension to the ORDER BY clause:
select ename, comm, dr from (select ename, comm,dense_rank() over (order by comm desc nulls last) dr from emp) where dr <= 5 order by comm desc; ENAME COMM DR ---------------------------------------------- ---------- ---------- John Russell ,4 1 Janette King ,35 2 Patrick Sully ,35 2 Allan McEwen ,35 2 Louise Doran ,3 3 Ellen Abel ,3 3 Peter Tucker ,3 3 Gerald Cambrault ,3 3 Lindsey Smith ,3 3 Karen Partners ,3 3 Alberto Errazuriz ,3 3 ...
It should be noted that the NULLS LAST works on a normal ORDER BY as well; its use is not limited to the analytic functions.
Also for aggreagate functions like sum, count, avg will be affected by NULL values. For your need you may prefer to use NVL function;
SQL> select avg(salary), avg(nvl(salary, 0)) from hr.employees ; AVG(SALARY) AVG(NVL(SALARY,0)) ----------- ------------------ 9304,822429 9304,82242990654 SQL> update hr.employees set salary = NULL where employee_id = 100 ; 1 row updated SQL> select avg(salary), avg(nvl(salary, 0)) from hr.employees ; AVG(SALARY) AVG(NVL(SALARY,0)) ----------- ------------------ 9066,566037 8981,83177570093
Continue reading with part 4
Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0
Refences Used :
“Null” thread on AskTom
Expert one-on-one Oracle by Thomas Kyte Database Independence?, Page 57 – Indexes and Nulls, Page 378 – NULLS and Sorting, Page 731
http://www.databasedesign-resource.com/null-values-in-a-database.html
Nulls: Nothing to Worry About By Lex de Haan and Jonathan Gennick
Title is NULL by APC
Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog 