Future of Relational Database

I read an interesting article on RWW about future of relational database; thought it might be of interest to you too!

This article talks about the emerging database (key/value database) and compares it to RDBMS. One of the interesting things being that you may not be able to perform JOIN operation. It is being described as the suitable model for cloud service provides (and pay-as-you-go service providers) and big players like Amazon (SimpleDB), Google (AppEngine Datastore), Microsoft (SQL Data services) have already started the offering. There are non-cloud providers too like - CouchDB, Drizzle, Mongo etc. However, all these services (both cloud and non-cloud) are still in beta or alpha phase.

My 2 cents - Personally, I think these emerging database models have a long way to go before then can match feature richness of RDBMS; Scalability can not be the only criteria as described in RWW article. With RDBMS like Oracle adopting to cloud, it can get tougher for key/value database models.

Pour in your thoughts...

Oracle Critical Patch Update - October 2008

Oracle released critical patch update for October 2008 yesterday; this is the last CPU for 2008. There have been 36 new fixes across all products including 15 new security fixes for database products. Please review the following URL to see if the product you are using requires this patch or not.
http://www.oracle.com/technology/deploy/security/critical-patch-updates/cpuoct2008.html

You can also refer to MetaLink Note 735216.1 for more details on database fixes.

Oracle announces Storage server and Database machine

Larry Ellison announced Oracle's foray into hardware arena with the launch of storage server and database machine at the ongoing Oracle Open World 2008 in his keynote address. Oracle has partnered with HP for this. Read the press release.

Oracle has announced a new storage server called "Oracle Exadata" and a database machine partnering with HP. The database machine is named "HP Oracle Database Machine". It's a pre-configured machine with Oracle Enterprise Linux consisting 8 database servers, 64 Intel processor cores, grid of 14 Oracle Exadata storage servers,. It's specially been designed for data warehousing market. It's being seen as a serious contender to Teradata and Netezza. The Exadata Storage server includes two Intel processors, each with four cores, with up to 12 terabytes of raw storage. Here is what James Kobielus of Forrester Research has to say at his blog.

Refer to the following links for more about Exadata and Database machine.

HP Oracle Exadata Storage Server
HP Oracle Database Machine

CURSOR_SPACE_FOR_TIME to be deprecated in future releases

According to a recently published metalink note (565424.1) the parameter "CURSOR_SPACE_FOR_TIME" is going to be deprecated starting release 11.1.0.7 onwards. In fact Oracle's next patch set release for 10g - 10.2.0.5 will also de-support this parameter. We will have to remove this parameter whenever these 2 patch sets - 10.2.0.5 for 10g and 11.1.0.7 for 11g are released and we decided to migrate. CURSOR_SPACE_FOR_TIME was introduced to reduce latch contention; This parameter may no longer be required with introduction of cursor mutexes hence Oracle has decided to deprecate this in future releases.

Oracle 11g New Features – PL/SQL enhancements Part-II

This is the concluding part of the 2 part series on PL/SQL enhancements in 11g. Here are few more useful enhancements in PL/SQL :-

1. Usage of Named and mixed notation with PL/SQL subprograms in a SQL statement

Prior to 11g, to use a sub-program (e.g. function) in a SELECT statement you have to provide the real parameters in positional notation. Let’s look at an example using both 10g and 11g to understand it better.

Here is a small piece of code which calculates the years of employment of an employee :-

CREATE OR REPLACE FUNCTION cal_employment_duration (
empid IN NUMBER,
as_on IN DATE
)
RETURN NUMBER
IS
vduration NUMBER;
BEGIN
SELECT MONTHS_BETWEEN (as_on, hiredate) / 12
INTO vduration
FROM emp
WHERE empno = empid;

RETURN ROUND (vduration);
END;
/

Now I call this function using a SELECT statement in Oracle 10g. Observe the difference when I use named and mixed notation :-

SIPRAS@ORA10G> SELECT empno, hiredate,
2 cal_employment_duration (empno, SYSDATE) || ' Years' "Employee Tenure"
3 FROM emp
4 WHERE empno = 7788
5 /

EMPNO HIREDATE Employee Tenure
---------- --------- ----------------------------------------------
7788 19-APR-87 21 Years

SIPRAS@ORA10G> SELECT empno, hiredate,
2 cal_employment_duration (empid => empno,
3 as_on => SYSDATE
4 )
5 || ' Years' "Employee Tenure"
6 FROM emp
7 WHERE empno = 7788;
cal_employment_duration (empid => empno,
*
ERROR at line 2:
ORA-00907: missing right parenthesis

Now lets use the same example in Oracle 11g :-

SIPRAS@11glab> SELECT empno, hiredate,
2 cal_employment_duration (empno, SYSDATE) || ' Years' "Employee Tenure"
3 FROM emp
4 WHERE empno = 7788;

EMPNO HIREDATE Employee Tenure
---------- --------- ----------------------------------------------
7788 19-APR-87 21 Years

Now lets use named notations and see if it works or not:-

SIPRAS@11glab> SELECT empno, hiredate,
2 cal_employment_duration (empid => empno,
3 as_on => SYSDATE
4 )
5 || ' Years' "Employee Tenure"
6 FROM emp
7 WHERE empno = 7788;

EMPNO HIREDATE Employee Tenure
---------- --------- ----------------------------------------------
7788 19-APR-87 21 Years

SIPRAS@11glab> SELECT empno, hiredate,
2 cal_employment_duration (empno, as_on => SYSDATE)
3 || ' Years' "Employee Tenure"
4 FROM emp
5 WHERE empno = 7788;

EMPNO HIREDATE Employee Tenure
---------- --------- ----------------------------------------------
7788 19-APR-87 21 Years

Great! it works, except in the following case; Note the error message :-

SIPRAS@11glab> SELECT empno, hiredate,
2 cal_employment_duration (empid => empno,
3 SYSDATE)
4 || ' Years' "Employee Tenure"
5 FROM emp
6 WHERE empno = 7788;
cal_employment_duration (empid => empno,
*
ERROR at line 2:
ORA-06553: PLS-312: a positional parameter association may not follow a named
association

Not sure why this was left out of the enhancements.

2. Trigger Enhancements: A couple of enhancements have been done to triggers. I have captured all of them with examples below.

i) You can now ENABLE or DISABLE triggers at creation time e.g

CREATE TRIGGER test_trig
BEFORE INSERT ON EMP
DISABLED/ENABLED

ii) Compound trigger: A new trigger type called “compound” has been introduced. It basically implements actions for all of the DML timing points in a single trigger. Action for each timing point “BEFORE STATEMENT”, “AFTER STATEMENT”, “BEFORE EACH ROW” and “AFTER EACH ROW” can now be written in a single trigger. Here is an example of :-

This trigger tracks updates to the “sal” column; It (a) assigns an update_id, notes start and end time for each update statement (b) keeps old and new salary for every row updated. All of this done with a single trigger.

CREATE OR REPLACE TRIGGER track_sal_upd
FOR UPDATE OF sal ON emp
COMPOUND TRIGGER
max_sal_upd_id number(4);
BEFORE STATEMENT IS
BEGIN
select nvl(max(sal_upd_id),0) into max_sal_upd_id from sal_update_log;
max_sal_upd_id := max_sal_upd_id + 1;
insert into sal_update_log values(max_sal_upd_id,null,null,null,systimestamp,'salary update process '||max_sal_upd_id||' started');
END BEFORE STATEMENT;

AFTER EACH ROW IS
BEGIN
insert into sal_update_log values(max_sal_upd_id,:old.empno,:old.sal,:old.sal,systimestamp,'updated by process '||max_sal_upd_id);
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
insert into sal_update_log values(max_sal_upd_id,null,null,null,systimestamp,'salary update process '||max_sal_upd_id||' finished');
END AFTER STATEMENT;
END track_sal_upd;
/

iii) Ordering of triggers: You can now control the order in which the triggers on a table would get fired. Oracle 11g has introduced a new clauses “FOLLOWS” to implement this feature. It will allow you to control the order in which the triggers fire when you have multiple triggers of same type on the same table. Oracle randomly picks up the triggers (if multiple triggers of same type on same table exist) if FOLLOWS clause is not used, which was the case prior to 11g.

Here an example which uses FOLLOWS clause. In this example, we have 2 triggers – first one “check_sal” is only for updates to SAL column whereas second one “check_update_job” will fire on updates to JOB & SAL column. Mark the FOLLOWS clause on “check_sal” trigger. It states that check_sal trigger should fire only after “check_update_job” has fired..

CREATE OR REPLACE TRIGGER check_update_job
BEFORE UPDATE OF job,sal
ON emp
FOR EACH ROW
DECLARE
v_start_range NUMBER (7, 2);
v_end_range NUMBER (7, 2);
BEGIN
IF :OLD.job IN ('CLERK','SALESMAN') THEN
IF :NEW.JOB != 'MANAGER' THEN
raise_application_error (num => -20001,
msg => 'Cannot change job to ' || :NEW.job
);
END IF;
END IF;
END;
/

CREATE OR REPLACE TRIGGER check_sal
BEFORE UPDATE OF sal
ON emp
FOR EACH ROW
FOLLOWS check_update_job
DECLARE
v_start_range NUMBER (7, 2);
v_end_range NUMBER (7, 2);
BEGIN
SELECT start_range, end_range
INTO v_start_range, v_end_range
FROM sal_range
WHERE job = :NEW.job;
IF :NEW.sal NOT BETWEEN v_start_range AND v_end_range
THEN
raise_application_error (num => -20000,
msg => 'Salary is not in the prescribed range'
);
END IF;
END;
/

SIPRAS@11glab> select empno,job,sal from emp where empno=7369;

EMPNO JOB SAL
---------- --------- ----------
7369 CLERK 800

SIPRAS@11glab> update emp set sal=500, job='PRESIDENT' where empno=7369;
update emp set sal=500, job='PRESIDENT' where empno=7369
*
ERROR at line 1:
ORA-20001: Cannot change job to PRESIDENT
ORA-06512: at "SIPRAS.CHECK_UPDATE_JOB", line 7
ORA-04088: error during execution of trigger 'SIPRAS.CHECK_UPDATE_JOB'

See the above error, it fired “check_update_job” first. In releases prior to Oracle 11g, you would not be able to control this.

3. Enhancements to PL/SQL Native compilation

Prior to Oracle 11g, initialization parameters were used to setup native compilation of PL/SQL code. However starting 11g, only one parameter “PLSQL_CODE_TYPE” needs to be set. It can be set to “INTERPRETED” (default value) or “NATIVE”. This parameter can be set at session level (ALTER SESSION), system level (ALTER SYSTEM) and for specific subprograms too using ALTER PROCEDURE. We all know, native compilation help improve the speed of PL/SQL programs as it compiles them to native code. It is of great help when your code consists of lot of loops, calculations, branches etc.

The trigger enhancements are really cool! I particularly liked the compound trigger feature the most. Share your thoughts on what you like…This was the concluding part of the PL/SQL enhancement series. Here is the link to first post in this series.

Technorati Tags: Oracle 11g,PL/SQL New Features,11g New Features,Trigger Enhancements

Oracle Critical Patch Update July 2008

Oracle has release the 3rd critical patch update for 2008 (CPU July 2008). Critical patch updates mostly contain fixes to security vulnerabilities plus it would have non-security fixes too. This critical patch update contains 45 new security fixes across all products which includes14 for Oracle database. Please review the following URL to see if the product you are using requires this patch or not.

http://www.oracle.com/technology/deploy/security/critical-patch-updates/cpujul2008.html

Also refer to Metalink note id 579278.1 for Oracle database and Fusion middleware products.

The next critical patch update (CPU) would be coming in October 2008.

Happy patching!

Technorati Tags: Oracle,CPU 2008,CPU,Patch Update

Oracle 11g New Features – PL/SQL enhancements Part-I

I was going through the enhancements made for PL/SQL in Oracle 11g and learn that there are quite a few. I am going to post on these new features in two parts. Here goes the first part.

1. Usage of sequences in PL/SQL expressions

Allows you to assign a sequence value in an expression that is, you do not need to use a SQL query to generate sequence value and assign it to variable. Here is an example :-

SELECT seq_name.nextval INTO variable FROM dual; –> this was how we used to generate sequence values inside PL/SQL

From 11g you can simply do this

variable := seq_name.nextval; –> isn’t this great!

Similarly “currval” can also be used in PL/SQL expression.

2. CONTINUE statement in PL/SQL

CONTINUE is the new loop control statement in 11g. We have used “EXIT” in order to exit out of the loop on certain condition, however CONTINUE would allow us to exit the current iteration in the loop and the control would be passed to next iteration. Here is a small example of find out out even numbers :-

BEGIN
   FOR x IN 1 .. 10
   LOOP
      IF MOD (x, 2) = 0
      THEN
         DBMS_OUTPUT.put_line ('Even number');
         DBMS_OUTPUT.put_line (x);
      ELSE
         CONTINUE;
         DBMS_OUTPUT.put_line (x);
      END IF;
   END LOOP;
END;
/

3. CONTINUE-WHEN statement

It’s purpose is to replace IF…THEN…CONTINUE. Lets re-write the above example using CONTINUE-WHEN :-

BEGIN
   FOR x IN 1 .. 10
   LOOP
      CONTINUE WHEN MOD (x, 2) = 1 ;
         DBMS_OUTPUT.put_line ('Even number');
         DBMS_OUTPUT.put_line (x); 
   END LOOP;
END;
/

4. New Datatypes – SIMPLE_INTEGER, SIMPLE_FLOAT and SIMPLE_DOUBLE

SIMPLE_INTEGER supports values ranging –2147483648 to 2147483648 and does not include null values which means it comes with a “NOT NULL” constraint. Apart from the fact that it’s never checked for nulls, overflow checking is also not necessary for SIMPLE_INTEGER. Due to these facts it gives better performance than PLS_INTEGER.

SIMPLE_FLOAT and SIMPLE_DOUBLE are new subtypes of BINARY_FLOAT and BINARY_DOUBLE with “NOT NULL” feature.

5. Changes to Regular expression built-ins

The new REGEXP_COUNT built-in returns the number of times a pattern is found in an input string. The built-ins REGEXP_SUBSTR and REGEXP_INSTR have been improved to return the occurrence you want to find.

I will be covering a few more PL/SQL enhancements in my next post. Do post your comments if you have any suggestions.

Technorati Tags: Oracle,Oracle 11g,PL/SQL,Oracle New Features