Curtain Raiser to Oracle 11g

By // No comments:
Today I attended a session on "Introduction to Oracle Database 11g- The Innovation Continues". Before attending the session I was so curious on 11g enhancement, as I was when I attended the 10g workshop. I was sitting among OCP 10g Certified DBAs of 7-8 years experience who have hands-on right from Oracle 7i - 10gR2 .


I felt like a tyro who has toyed with oracle for the last one year. But this session handled by a Senior Consultant from Oracle Corporation was full of insights. I thought it would be great if I share the presentation with all, you can download the same.

One thing was on attending this, now I know what all cannot be done with 9i/10g :). Done miss the Presentation, do have a look on it.

Tables References & Referenced By

By // No comments:
We create many tables in a schema with lot of Constraints among them. The linking constraint Foreign Key which exists among tables will sometime create spaghetti schema. Consider a schema with 4 tables as show in the ER diagram below. (You can download the script for creating these tables and constrains)


It may be some times required to fetch the foreign key relationship between tables to identify the Parent-Child relationship. To identify those you can use the following queries will be handy for you. This query uses the sys user’s constraint definition tables for the database.
To Identify the Tables referencing the Given Table:
select ro.name table_name,
substr(initcap(cc.column_name), 1, 30) column_name, cc.position,
cn.name constraint_name, ru.name owner
from sys.obj$ o, sys.user$ u, sys.con$ cn, sys.cdef$ c, sys.obj$ ro,
dba_cons_columns cc, sys.con$ rcn, sys.user$ ru
where o.owner# = u.user#
and u.name = '&&SCHEMANAME'
and o.name = '&&TABLENAME'
and c.robj# = o.obj#
and c.con# = cn.con#
and ro.obj# = c.obj#
and cc.owner = u.name
and cc.table_name = o.name
and rcn.con# = c.rcon#
and cn.owner# = ru.user#
and c.type# = 4
and cc.constraint_name = rcn.name
order by u.name, ro.name, cn.name, cc.position;
To Identify the Tables referred by the Given Table:
select a.table_name, initcap(b.column_name) column_name, b.position,
a.constraint_name, a.owner
from (select ao.name table_name,
au.name owner,
bcn.name constraint_name,
decode(ac.type#, 4,
decode(ac.refact, 1, 'CASCADE', 2, 'SET NULL', 'NO ACTION'),
null) delete_rule,
bo.name tn
from sys.cdef$ bc, sys.con$ bcn, sys.obj$ bo, sys.user$ bu,
sys.con$ brc, sys.user$ bru, sys.obj$ bro,
sys.cdef$ ac, sys.con$ acn, sys.obj$ ao, sys.user$ au
where bc.con# = bcn.con#
and bc.obj# = bo.obj#
and bo.owner# = bu.user#
and bu.name = '&&SCHEMANAME'
and bo.name = '&&TABLENAME'
and bc.rcon# = brc.con#(+)
and brc.owner# = bru.user#(+)
and bc.robj# = bro.obj#(+)
and ac.con# = bc.rcon#
and ac.con# = acn.con#
and ao.obj# = ac.obj#
and ao.owner# = au.user#
and ac.type# in (2,3)) a,
dba_cons_columns b
where b.table_name = a.tn
and b.constraint_name = a.constraint_name
order by a.table_name, a.constraint_name, b.position;
Hope this is useful for you at times :)

Exploit the Bulk Bind of PL/SQL

By // No comments:
After a long time, let me write a bit of Oracle this week. Improve the performance of multi-row SQL operations with bulk/array processing in PL/SQL. For code snippets of the form


cursor emp_cur is
select employee_id
from employee where department_id = dept_in;

begin
for rec in emp_cur loop
update employee set salary = salary + 1000
where employee_id = rec.employee_id;
end loop;
end;

Bulk processing of PL/SQL can be used to improve its performance to greater levels. Use the FORALL and BULKCOLLECT of PL/SQL that will scale the application. The code snippet can be changed to
select employee_id
bulk collect into collection_var
from employee where department_id = dept_in;

begin
forall i in collection_var.first .. collection_var.last
update employee set salary = salary + 1000
where employee_id = collection_var(i);
end;

The context switch happening in executing PL/SQL and SQL code is reduced in this. The Save Exceptions and SQL%BULK_EXCEPTION is another good introduction on excellent exception handling on these bulk processing.

Back to Basics

By // No comments:
I think many of my friends out there who spent time with me in group studies will never forget this Big book which we had in our engineering syllabus. When I read this book in my sophomore days it imparted a very little knowledge to me.

We always gave importance to C, C++ and finally the Lingua Franca JAVA. Our talent was judged based on how well one can code in any one of the above with importance in the given order. Here in these we write the code, see the results pat ourselves on our success. So this was the best part of our engineering syllabus.

Then came the concepts that had a second round of importance Theoretical Computer Series - Advanced Data Structures, Algorithm Analysis, Theory of computation and Discrete Mathematics. If one explains these concepts well he would be called as a technical Buff. They were considered amongst us as the one who will be giving the next generation of new algorithms and new Languages.

Next , “It’s already done. We need to know the concepts alone – series” - Database Management Concepts, System Software and Operating Systems. These were only improve our knowledge on the computer field, but since we did not try any thing to implement in this more than a Cross compiler which I did as my placement project.

Finally, Artificial Intelligence and Natural Language Processing series – The ultimate abstract concepts where we enjoyed sometimes by laughing at Turing tests and cried sometimes at evaluations in NLP.

But now, in the four grouping I gave, I am working with the third one. I am not working with C, C++ or Java. Rather I am not writing any algorithms. My last algorithm which I wrote was for my Fractal Image Compression using Fractal dimension. This has been selected in one of the International journals, will soon give the info once it is published.

I am not in Powerset to give some AI or NLP solutions. Now I work with database, database and database only. I try to write good number of Sqls and PL/SQL statement every day.

But after working with database almost a year made me read this Database concepts book. Lots of insights I had. So many concepts now are so clear to me. Right from Relational Algebra & Query Evaluation to Transaction & Concurrency control every thing strikes clear to me. The Edgar Codds 12 rules had been my favorite and still remain the same.

I am feeling better after having one revision on my basic study material. You may also give it a try :)

Programming Commitment

By // 1 comment:
As a programmer, I feel every one who program must have their own commitment in job to the maximum. There are people like this not only as contractor but also as experience professionals who add up their experience profile always and update their technologies knowledge profile with whatever technology the project has instead of what they work on. They merely try to exist in the new assignment, by the time their Incompetency strips they are already on their way to be as senior tech lead or architect in any one of the numerous software startups.

After all this ending up as senior software architect like this in some big Servicing companies. Finally the developer's will be put in an impression that the architect has coded more than millions of lines. I did not stick to the point that all architects must sit and write programs, they must have some relevant knowledge on programming, which will really help them in giving better solution, and sure they can get efficient work from developers.

Where is the Software Monopoly Shifting Now?

By // 1 comment:
Starting from search to indexing every individuals web history, Google is proliferating at an unimaginable rate. The software giant of the 90's is trying hard to maintain its monopoly with its live search to Silverlight. Is the giant's dominance has really fallen down as they have said its dead.

There are people who are really looking ahead and started to think about their career decisions. But this means the software monopoly is something happening always like they have mentioned from IBM , Microsoft took over and now is it not shifting towards the Google. Though there is a lot of hype on Web 2.0 bubble, still lot of successful start up are devoured by the mountain valley giant from photo sharing to video sharing startups. Many consider Google is one of the main reason behind the fall of Microsoft monopoly, but it looks like Google will be the monopoly for the next web based software development. Still the Monopoly will be there, every new startup will be struggle to exist or get sold to any one of the defunct Monopoly giants like IBM , AOL or the ruling giant like Microsoft or Google. This presentation by Seth Godin discuss the success strategy of Google, which made me think on the above lines.

Contact Me Using Contactify

By // No comments:




You can contact me at this link. That will be forwarded to my mail box. Cool way to protect spams :)

Simple But Powerful Solution

By // No comments:
Generating CSV files from tables is a task which we do many a times. But as I said Oracle 10g gives many features , this is really cool, simple solution using xml and regular expressions in a single query.

Withouth specifying the column names is the good point to note :)

Oracle 10g My Favorites So Far

By // No comments:

I am in a Oracle 10g developer’s workshop. In these two days some interesting things in Oracle 10g which I found are,

  • Automatic SQL Tuning in Oracle Database 10g using the DBMS_SQLTUNE. On good illustrations you can use this.
  • Resumable timeout, this really saves most of the time in redoing things due to a small mistake we failed to notice. At least for me it will save loads of time on schema imports. You must have a deep look into this.
  • File Transfers, using oracle this will really make database programming stronger.
  • Conditional Compilation will really make debugging easier (Inherits A ‘C’ Language #ifdef Feature).
  • Pattern Matching, this regular expression will give some Java edge.

To try out new features of 10g, you can always request a workspace from oracle at this place.