Wednesday, January 30, 2008

The year has gone..

..since I started blogging. Actually I started it because of real anger against Oracle. The reason was one of the most famous problems in OTN history - inability to change e-mails ;) However about a month or two later it was resolved.
It seems the last year Oracle has made advances towards the community and:
1) listened to it (above mentioned case) as well as for example the case with AWR and ASH licensing. Probably all these steps were already on their way but I'm quite sure blog entries accelerated the result.
2) created public resources (oracle wiki) probably not veeeeery actively used until now.
3) granted free access for active members of public community to Oracle open world.
4) most probably made other positive steps I cannot remember or simply haven't heard about.

Last year was also big improvement in so called Oracle blogosphere - orana.info was founded along with some other aggregators, which can be found on oracle wiki.

For me it was a year of realizing the fact that there are tremendous resources about databases and Oracle in English, but just few in my mother tongue Latvian. So as I don't think English is the only language in the world even for databases :) I started to blog about databases in Latvian. Quite a bit of free time goes there.

So in my little anniversary I wish Oracle remember and support its community (probably sponsor a trip to Open world next year for bloggers khe khe :))) and myself to continue work about databases and Oracle resources especially in Latvian!

Friday, January 25, 2008

SQL Join Types


New enhanced 25 pages long article with 44 examples and visual pictures describing SQL join types can be found here.

I'm studying a bit SQL joins and trying to make clear at least for me what they are and what the relationships among them are. As I've always thought that picture is worth hundred words I've tried to create ER diagram describing metainfo about joins. So the question for everyone is - is this diagram OK? And aren't there obvious bugs? :)
Probably from the strict viewpoint of Set theory and relational algebra it is not OK, but I'm more concerned of practical SQL usage and understanding. In case you know similar diagramm or whatever else visual explanation of relationships among join types please leave comment with link.
So the diagram is as follows (please click on it to get bigger):

SQL Join Types

Now for those that aren’t familiar with all these join types and terms in diagram above I'll try to give SQL examples (without data and results) for each one of them. If you'd like to have also examples with data cehck one of the links at the very end of this post.
Assume we have 2 tables - TableA (id, title) and TableB (id, description).
What the result are for each one of them you can find in other places for example:

  • Cross join:

SELECT * FROM TableA CROSS JOIN TableB

  • Join with restriction:

It is either natural join or qualified join. Each join with restriction has one cross join which is the degraded case of it.

  • Natural join:

Natural joins can be Inner or one of Outer joins, but they always are equi joins. Without explicitly specified inner or outer it is inner join. More about natural joins in my previous blog post.

SELECT * FROM TableA NATURAL JOIN TableB

  • Qualified join:

Qualified joins can be written either specifying join columns (named columns join) or explicitly writing join condition.

  • Join type:

Could not find anything better to somehow unite inner and various outer joins under one roof.

  • Inner join:

SELECT * FROM TableA NATURAL INNER JOIN TableB
SELECT * FROM TableA INNER JOIN TableB USING (id)
SELECT * FROM TableA INNER JOIN TableB ON (tablea.id = tableb.id)

  • Outer join:

Outer join is one of left, right or full outer join.

  • Left outer join:

SELECT * FROM TableA NATURAL LEFT OUTER JOIN TableB
SELECT * FROM TableA LEFT OUTER JOIN TableB USING (id)
SELECT * FROM TableA LEFT OUTER JOIN TableB ON (tablea.id = tableb.id)

  • Right outer join and Full outer join:

Take the same examples as from left outer join and just replace left with right or outer.

  • Qualified join expression:

There are two ways how more explicitly write join condition either specifying join columns or explicitly writing join condition.

  • Named columns join:

This join is written with "USING" clause. Named columns join always is equi join.

SELECT * FROM TableA INNER JOIN TableB USING (id)

  • Conditional join:

This join is the real one that I'd like to suggest using. With explicitly specifying what the join condition is. This also is the only join where one can use not only equi join but also another predicate operator than "=".

SELECT * FROM TableA INNER JOIN TableB ON (tablea.id = tableb.id)

  • Predicate operator type:

Based on predicate operator type joins can be classified as either equi or theta (nonequi) joins.

  • Equi join:

Equi join is join where operator used in join condition is equivalence ("=").
Both natural joins and Named columns joins are always equi joins. Examples:

SELECT * FROM TableA NATURAL LEFT OUTER JOIN TableB
SELECT * FROM TableA FULL OUTER JOIN TableB USING (id)
SELECT * FROM TableA INNER JOIN TableB ON (tablea.id = tableb.id)

  • Theta (Nonequi) join:

Equi join is join where operator used in join condition is something other than equivalence ("=").

SELECT * FROM TableA INNER JOIN TableB ON (tablea.id <>

I've not showed here such beasts like UNION JOINS, SEMI JOINS and ANTI JOINS because the first one (UNION join) is neither in SQL standard nor in any database I worked with.
SEMI JOINS and ANTI JOINS are somehow less interesting for me.

Further reading:


Sunday, January 13, 2008

Some photos


I've been tagged by APC. Usually I don't respond to chain letters but this will be a bit of exception. However I won't tag anyone further. Looking at usual curses happening after NOT forwarding chain letters I hope all my Oracle installations won't silently transform to SQL Server ones ;)
So I won't tell anything about me, I'll just publish some of my best photos taken in various places in Latvia.







































Lilacs in Dobele


Opera square

Lilacs in Dobele gardenGarden if front of National Opera

Nest of Lesser Spotted Eagle


Heath

Nest of Lesser Spotted EagleAiring trail in heath

A lake in fog


Sun set

A lake in fogSun set view over my country landed property

Open air museum in Atte


Heath

Open air museum in AtteEaster eggs by my wife(colouring of course :)

National Opera House in Riga


Rundale Palace

National Opera House in RigaRundale Palace

Riga Dom Cathedral


House Of Blackheads and St.Peter church

Riga Dom Cathedral - biggest church in BalticsHouse Of Blackheads and St.Peter church

Railroad bridge at night


Art Noveau in Riga

Railroad bridge at nightArt Noveau in Riga

Thursday, January 10, 2008

MINUS ALL and INTERSECT ALL in Oracle Revisited

Yesterday I wrote an article explaining that MINUS ALL and INTERSECT ALL is not possible in Oracle.
However today after a bit thinking I got insight - multiset operations! Yes these should be the right thing!
Since 10g Release 1 Oracle supports MULTISET UNION/EXCEPT/INTERSECT for both ALL/DISTINCT. I have only to make some types, a bit type casting and everything should be ok!

So I've started with the simple tables I've shown yesterday.

CREATE TABLE t1 AS SELECT mod(rownum, 1000) rn
FROM dba_source WHERE rownum <=50000;
CREATE TABLE t2 AS SELECT * FROM t1;


Now I can query both tables and try to compare then. To use multiset operations I need to cast subquery as nested table. To be able to use such type casting I have to create two additional types. The first one is simple Object type containing records I'd like to compare. Second one is object table type, to which I'll cast my subqueries. So they are as follows:


CREATE TYPE t1_type
AS OBJECT ( rn NUMBER);
/
CREATE TYPE t1_tab_type
AS TABLE OF t1_type;
/


So far so good. Let's start building queries step by step. The first one just builds nested table from subquery.


SQL> select cast(multiset(
2 select rn from t1 where rownum <=3
3 ) as t1_tab_type)
4 from dual
5 /

CAST(MULTISET(SELECTRNFROMT1WHEREROWNUM<=3)AST1_TAB_TYPE)(RN)
---------------------------------------------------------------
T1_TAB_TYPE(T1_TYPE(1), T1_TYPE(2), T1_TYPE(3))

The second one already uses multiset intersect to get desired result.

SQL> select col1 multiset intersect distinct col2
2 from (
3 select cast(multiset(
4 select rn from t1 where rownum <=3
5 ) as t1_tab_type) col1,
6 cast(multiset(
7 select rn from t2 where rownum <=3
8 ) as t1_tab_type) col2
9 from dual
10 )
11 /

COL1MULTISETINTERSECTDISTINCTCOL2(RN)
---------------------------------------------------
T1_TAB_TYPE(T1_TYPE(1), T1_TYPE(2), T1_TYPE(3))

Now we just need to add one final step - cast nested table rows back to normal rows. Here us helps select from table construct:

SQL> select * from table (
2 select col1 multiset intersect distinct col2
3 from (
4 select cast(multiset(
5 select rn from t1 where rownum <=3
6 ) as t1_tab_type) col1,
7 cast(multiset(
8 select rn from t2 where rownum <=3
9 ) as t1_tab_type) col2
10 from dual
11 )
12 )
13 /

RN
----------
1
2
3

Voila! Everything works!
However then I was torn with doubt - how it will work when not used as a toy but on real volumes? I created million rows worth table with wide column and got - yeahh I got following:

ORA-22813: operand value exceeds system limits

Not pleasing.

Then I started to play with the tables defined above and got quite awful results. What is the performance of normal MINUS set operator?

SQL> select * from t1
2 minus
3 select * from t2 where rn <=998;
RN
----------
999
Elapsed: 00:00:00.10


What is the equivalent for multiset except distinct? I was a bit unpleasantly surprised again:

SQL> select * from table (
2 select col1 multiset except distinct col2
3 from (
4 select cast(multiset(
5 select rn from t1
6 ) as t1_tab_type) col1,
7 cast(multiset(
8 select rn from t2 where rn <= 998
9 ) as t1_tab_type) col2
10 from dual
11 )
12 )
13 /
RN
----------
999
Elapsed: 00:01:17.98

However for multiset except all I was simply shocked!

SQL> select * from table (
2 select col1 multiset except all col2
3 from (
4 select cast(multiset(
5 select rn from t1
6 ) as t1_tab_type) col1,
7 cast(multiset(
8 select rn from t2 where rn <= 998
9 ) as t1_tab_type) col2
10 from dual
11 )
12 )
13 /
RN
----------
999
...
999
50 rows selected.
Elapsed: 00:06:05.39

6 minutes for 2 * 50K rows!? Hmm is it implemented using bubble sort or what? :O

So normal SQL minus needed 0.10 seconds, multiset minus distinct 1 minute 18 seconds, but multiset except all more than 6 minutes.

Yeahh I got another strong argument NOT to use nested tables and their operations ;)

Wednesday, January 09, 2008

MINUS ALL and INTERSECT ALL in Oracle

Recently I was a bit studying SQL standard and a lot to my surprise found that there exists not only UNION ALL set operator, but also INTERSECT ALL and EXCEPT ALL (in Oracle version it would be MINUS ALL).

So you'd say Oracle haven't them and it is also (although partially - somehow minus has been forgotten) documented in Oracle Support for Optional Features of SQL/Foundation:2003.

Hmm, yea but a while ago I was doing several data migrations and then such set operators would really helped me. Now a bit what these non-existant (at least for Oracle) set operators are doing?

Imagine we have tables T1 and T2 with data as follows:

T1: 1, 2, 2, 2, 3, 4, 4
T2: 2, 3, 4, 4, 4, 5

So what is the result for SELECT * FROM T1 MINUS SELECT * FROM T2?

1

What would be the result for SELECT * FROM T1 MINUS ALL SELECT * FROM T2?

1, 2, 2

What is the result for SELECT * FROM T1 INTERSECT SELECT * FROM T2?

2, 3, 4

What would be the result for SELECT * FROM T1 INTERSECT ALL SELECT * FROM T2?

2, 3, 4, 4

As you can see ALL for EXCEPT/MINUS and INTERSECT retains cardinality and doesn't keep only unique records. Let's hope Oracle someday will stop this gap and give us this functionality :)