Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

January 13, 2007

Oracle Concepts and Architecture – Part 3

Filed under: Oracle Concepts and Architecture — H.Tonguç Yılmaz @ 6:29 am

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

About these ads

1 Comment »

  1. greate document, all three parts. I was just imagining our endless oracle lessons.

    Comment by erturkdiriksoy — January 31, 2007 @ 11:52 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 79 other followers

%d bloggers like this: