Collect in 10G

Collections can be a great help in speeding up the PL/SQL programs. By using bulk collect operations it is possible to get great performance improvements.

In 9.2 we needed to use the bulk collect clause to fetch rows into a collection. 10G brings a new function called COLLECT, which takes a column as a parameter and returns a nested table containing the column values. Using this we can get the data into a collection without using bulk collect.

Here is a very simple demo of this new function.

YAS@10G>create table t as select * from all_objects;

Table created.

(more...)

Relational algebra: division in sql

There was a question in one of the Turkish Oracle mailing lists which got me interested. The question simply was:

I have a table holding the parts of a product and another table holding the suppliers of these parts. How can I find the suppliers which supply all the parts?

Someone suggested using the division operation of relational algebra but did not provide how to do it in Oracle. So I started with the Wikipedia link he provided to solve the problem in sql.

Here are the tables:

SQL> create table parts (pid number);

Table created.

SQL> create table catalog (more...)

Local vs. remote connection performance

It has been asked several times in several places; is there a performance difference between running a query locally in a client on the server and running the same query in a remote client?

The obvious answer given by the respondents including myself is: "if you do not return thousands of rows through the network, there must not be any difference". This type of response is opposed to what I believe; even if the answer seems obvious test it before you make any suggestions.

Tanel Poder got the same question and did what is needed to be done, he tested it (more...)

Database version control

Coding horror is one of the software development blogs I keep a close eye on.

Jeff Atwood posted a nice piece about database version control recently. Database version control is maybe one of the most important and unfortunately most overlooked things in software development. The post is a good read including the links he provides.

The blog tagging thing

During the last few days lots of Oracle bloggers have been busy tagging each other and posting eight unknown things about themselves. I was also tagged by some friends and was asked to post eight things about myself. I have never forwarded any chain e-mails or messages to anyone and in parallel to that I have not written anything about myself after this either.

What I think about this blog tagging thing is very similar to what Howard Rogers thought about it. He shut his site down for some time and you can read what he thinks when you go (more...)