Tony’s Oracle Tips

September 15, 2008

Partitioned outer join funny

Filed under: SQL Fun — tonyhasler @ 2:39 pm

Look at these two queries designed to run in the SCOTT schema:


select * from dept d partition by (dname)
                   right join emp e on d.deptno=e.deptno;
select * from dept d partition by (deptno)
                   right join emp e on d.deptno=e.deptno;

A partitioned outer join splits one of the two tables in the query into partitions with all rows in each partition having the same value for the specified column(s). The first example above splits the four rows in the “dept” table into four partitions each of which has one row. These four partitions are then individually joined to the “emp” table with 14 rows each. The result is 56 rows – one for each combination of “emp” and “dept” rows.

I would have thought the same result would apply when one partitioned the “dept” table by “deptno” instead of “dname”. However, you only get 14 rows, one for each row in “emp”.

The result is the same when run on 10gR2 and 11g. I think this is a bug. As confirmation I tried this:


select * from dept d partition by (deptno)
                   right join emp e on d.deptno+1=e.deptno+1;

And this returned 56 rows!

Advertisements

Leave a Comment »

No comments yet.

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

Blog at WordPress.com.

%d bloggers like this: