Blog Aggregator - Amardeep Sidhu

On Dec. 3rd. Oracle has released
Enterprise Linux Release 5 Update 1 Media Pack
for x86_64 (64 bit)
and x86_32(32 bit)
for download on Edelivery

Since release 6, Oracle has supported a restricted form of left outerjoin, which uses Oracle-specific syntax. In 9i, Oracle introduced support for ANSI SQL 92/99 syntax for inner joins and various types of outerjoin. The Oracle syntax for left outerjoin and that of ANSI SQL 92/99 are not equivalent, as the latter is more expressive.

There appears to be some confusion about equivalence between ANSI outer join and Oracle outer join syntaxes. The following examples explain the equivalences and inequivalences of these two syntaxes.

Oracle-Specific Syntax

Consider query A, which expresses a left outerjoin in the Oracle syntax. Here T1 is the left table whose non-joining rows will be retained and all non-joining rows of T2 will be null appended.

A.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+);

ANSI Left Outerjoin

In the ANSI outer join syntax, query A can be expressed as query B.

B.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x);

Equivalence

Consider the following queries. In the Oracle semantics, the presence of (+) on the filter predicate (e.g., T2.y (+) > 5 in query C) indicates that this filter must be applied to the table T2 before the outer join takes place.

C.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.y (+) > 5;

The ANSI left outer join query D is equivalent to C. Applying the filter on the right table in the left outer join is the same as combining the filter with the join condition.

D.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x and T2.y > 5);

Similarly, the presence of (+) on the filter predicate, T2.y (+) IS NULL, in query E indicates that this filter must be applied to the table T2 before the outer join takes place.

E.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.y (+) IS NULL;

The ANSI left outer join query F is equivalent to E.

F.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x and T2.y IS NULL);

Consider query G. Oracle will apply the filter, T2.y IS NULL, in query G after the outer join has taken place. G will return only those rows of T2 that failed to join with T1 or those whose T2.y values happen to be null.

G.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.y IS NULL;

The ANSI left outer join query H is equivalent to G, as the WHERE clause in H is applied after the left outer join is performed based on the condition specified in the ON clause.

H.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x)
WHERE T2.y IS NULL;

Consider query I, where the filter on the left table is applied before or after the outer join takes place.

I.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T1.Z > 4;

The ANSI left outer join query J is equivalent to query I.

J.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x)
WHERE T1.Z > 4;

Lateral Views

In Oracle, ANSI left and right outerjoins are internally expressed in terms of left outerjoined lateral views. In many cases, a left outerjoined lateral view can be merged and the ANSI left (or right) outerjoin can be expressed entirely in terms of Oracle native left outerjoin operator. (A lateral view is an inline view that contains correlation referring to other tables that precede it in the FROM clause.)

Consider the ANSI left outer join query K, which is first represented internally as L.

K.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x and T2.k = 5);

L.
SELECT T1.d, LV.c
FROM T1,
LATERAL (SELECT T2.C
FROM T2
WHERE T1.x = T2.x and T2.k = 5)(+) LV;

The lateral view in query L is merged to yield query M.

M.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.k (+)= 5;

Consider query N, which expresses a left outerjoin in the ANSI join syntax. Currently query N cannot be expressed using the Oracle native left outer join operator.

N.
SELECT T1.m, T2.n
FROM T1 LEFT OUTER JOIN T2
ON (T1.h = 11 and T1.y = T2.y)
WHERE T1.q > 3;

The query N is converted into query O with a left outer-joined lateral view. The lateral view in O cannot be merged, since the filter on the left table specified in the ON clause must be part of the left outerjoin condition.

O.
SELECT T1.m, LV.n
FROM T1,
LATERAL(SELECT T2.n
FROM T2
WHERE T1.h = 11 and T1.y = T2.y)(+) LV
WHERE T1.q > 3;

Consider query P with a disjunction in the outer join condition. Currently N cannot be expressed using the Oracle native left outer join operator.

P.
SELECT T1.A, T2.B
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x OR T1.Z = T2.Z);

The query P will be converted into Q with a left outer-joined lateral view containing the disjunctive join condition.

Q.
SELECT T1.A, LV.B
FROM T1,
LATERAL (SELECT T2.B
FROM T2
WHERE T1.x = T2.x OR T1.Z = T2.Z) (+) LV;

ANSI Full Outerjoin

Before Oracle 11gR1 all ANSI full outerjoins were converted into a UNION ALL query with two branches, where one branch contained a left outerjoined lateral view and the other branch contained a NOT EXISTS subquery. A native support for hash full outerjoin was introduced in 11gR1 to overcome this problem. When the native full outerjoin, cannot be used, Oracle reverts to the pre-11gR1 strategy.

Consider query R, which specifies an ANSI full outerjoin.

R.
SELECT T1.c, T2.d
FROM T1 FULL OUTER JOIN T2
ON T1.x = T2.y;

Before 11gR1, Oracle would internally convert query R into S.

S.
SELECT T1.c, T2.d
FROM T1, T2
WHERE T1.x = T2.y (+)
UNION ALL
SELECT NULL, T2.d
FROM T2
WHERE NOT EXISTS
(SELECT 1 FROM T1 WHERE T1.x = T2.y);

With the native support of hash full outerjoin, R will be simply expressed as query T, where the view, VFOJ, is considered unmergeable.

T.
SELECT VFOJ.c, VFOJ.d
FROM (SELECT T1.c, T2.d
FROM T1, T2
WHERE T1.x F=F T2.y) VFOJ;

Conversion of Outerjoin into Inner Join

Consider query U. Here the filter predicate on the outer-joined table T2 does not contain the outerjoin operator (+); thus it will be applied after the left outerjoin has taken place. This will result in the elimination of all null appended rows of T2. Hence, Oracle converts the outer join into an inner join.

U.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.y > 5;

The ANSI left outer join query V is equivalent to query U, as the WHERE clause in V is applied after the left outer join is performed based on the condition specified in the ON clause.

V.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x)
WHERE T2.y > 5;

Oracle converts the queries U and V into query W with an inner join.

W.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x and T2.y > 5;

Q&A

Q1: I do not understand the queries N and O. What is the difference between
a filter appearing in the ON Clause or a filter appearing in the WHERE
clause?

A1: Consider two tables T11 and T22.

T11:
A | B
1 | 2
2 | 3
3 | 5

T22:
X | Y
7 | 2
8 | 4
9 | 4

The following ANSI left outer join query N’ involving
the tables T11 and T22 will return three rows, since
the filter, which always fails, is part of the join
condition. Although this join condition, which
comprises both the predicates in the ON clause,
always evaluates to FALSE, all the rows of the left
table T11 are retained in the result.

N’.
SELECT *
FROM T11 LEFT OUTER JOIN T22
ON (T11.A > 9 and T11.B = T22.Y);

A B X Y
—— ———- ———- ———
1 2
2 3
3 5

However, if the filter, T11.A > 9, is moved to the WHERE clause,
the query will return zero rows.

Q2: Is the outer to inner join conversion a new feature?

A2: No. This feature has been avaliable since Release 7.

Q3: Has native full outer join been made available in
versions prior to 11gR1?

A3: Yes. It is available in 10.2.0.3 and 10.2.0.4, but not by
default.

I gave an 11g for DBA’s “new feature” talk at UKOUG.  It was going pretty good - until…  Well, let me back up and start at the beginning.  I began my talk with:

You are going to see for the first time ever on screen a series of scripts.  I have not used these before in public - live.  Fingers crossed, let’s hope for the best.

I shouldn’t have done that - I jinxed myself

Everything was going great - until I got to some partitioning examples, interval partitioning in particular.  The script goes like this…

Ok, there is this new feature with partitioning called interval partitioning.  If you use this - then Oracle will automatically create new partitions based on your template, your description of how the partitions should be created.  This means you do not have to preallocate your partitions for a rolling window anymore.  For example, suppose you have an audit trail range partitioned by month - at the end of each month, you have to make sure you create the partition for NEXT month to avoid any errors.  With interval partitioning you can tell us to create a new partition on the fly as needed - without DBA intervention.  For example:

ops$tkyte%ORA11GR1> create table audit_trail  2  ( ts    timestamp,  3    data  varchar2(30)  4  )  5  partition by range(ts)  6  interval (numtodsinterval(1,’day’))  7  store in (users, example )  8  (partition p0 values less than  9   (to_date(’23-feb-2007′,’dd-mon-yyyy’)) 10  ) 11  /Table created.

 That creates a range partitioned table with everything having a TS attribute value less than 23-Feb-2007 stored in partition P0.  Further, we desire each new partition automatically added by Oracle to contain 1 days worth of data (numtodsinterval is a function that turns a number into a day to second interval). 

In order to demonstrate that Oracle does “logical things” here, I will insert a date in the future with regards to 23-Feb-2007, please note how Oracle did not create dozens of partitions for all of the interior values - it creates the MINIMAL set of partitions needed to contain our data:

ops$tkyte%ORA11GR1> insert into audit_trail (ts,data) values ( '02-jun-2007', 'xx' );1 row created.

ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value  2    from user_tab_partitions  3   where table_name = ‘AUDIT_TRAIL’  4   order by partition_position;

PARTITION_ TABLESPACE HIGH_VALUE———- ———- ———————————–P0         USERS      TIMESTAMP’ 2007-02-23 00:00:00′SYS_P201   EXAMPLE    TIMESTAMP’ 2020-06-03 00:00:00′

 and then another date further out in the future, we’ll use this to show that - even though it looks like all of the data between the 3rd of June and 15th of Sept will go into it - it won’t:

ops$tkyte%ORA11GR1> insert into audit_trail (ts,data) values ( '16-sep-2007', 'xx' );1 row created.

ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value  2    from user_tab_partitions  3   where table_name = ‘AUDIT_TRAIL’  4   order by partition_position;

two PARTITION_ TABLESPACE HIGH_VALUE———- ———- ———————————–P0         USERS      TIMESTAMP’ 2007-02-23 00:00:00′SYS_P201   EXAMPLE    TIMESTAMP’ 2020-06-03 00:00:00′SYS_P202   EXAMPLE    TIMESTAMP’ 2020-09-17 00:00:00′

Right now, it might appear that anything between 3-jun and 16-sep would go into SYS_P202, but if we insert a date in between the existing dates:

ops$tkyte%ORA11GR1> insert into audit_trail (ts,data) values ( '11-sep-2007', 'xx' );1 row created.

ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value  2    from user_tab_partitions  3   where table_name = ‘AUDIT_TRAIL’  4   order by partition_position;

PARTITION_ TABLESPACE HIGH_VALUE———- ———- ———————————–P0         USERS      TIMESTAMP’ 2007-02-23 00:00:00′SYS_P201   EXAMPLE    TIMESTAMP’ 2020-06-03 00:00:00′SYS_P203   USERS      TIMESTAMP’ 2020-09-12 00:00:00′SYS_P202   EXAMPLE    TIMESTAMP’ 2020-09-17 00:00:00′

We can see that Oracle created SYS_P203 - it will fill in the interior partitions as needed…

It was at this point I received a question from the audience.  It was simply:

Why are your range high values in the year 2020?

Indeed.  I had not noticed that.  I pointed out right there and then “Hey, this is the first time after all…”, but I did not see immediately what I had done.  I knew what I changed in my scripts (I used to use SYSDATE in the example, but as time marches on - I’d prefer to have static dates so I can have a predicable experience).  But it was not immediately obvious - so I said “Watch my blog next week, I’ll write about it”

Which brings us here - what happened?

A few people pointed it out for me after the session - it was the use of the default Oracle DATE format to insert a string into a TIMESTAMP!  On my system, the NLS settings were:

ops$tkyte%ORA11GR1> select * from nls_session_parameters;

PARAMETER                      VALUE—————————— —————————————-NLS_LANGUAGE                   AMERICANNLS_TERRITORY                  AMERICANLS_CURRENCY                   $NLS_ISO_CURRENCY               AMERICANLS_NUMERIC_CHARACTERS         .,NLS_CALENDAR                   GREGORIANNLS_DATE_FORMAT                DD-MON-RRNLS_DATE_LANGUAGE              AMERICANNLS_SORT                       BINARYNLS_TIME_FORMAT                HH.MI.SSXFF AMNLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AMNLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZRNLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZRNLS_DUAL_CURRENCY              $NLS_COMP                       BINARYNLS_LENGTH_SEMANTICS           BYTENLS_NCHAR_CONV_EXCP            FALSE

17 rows selected.

 See that NLS_TIMESTAMP_FORMAT - when applied to 11-sep-2007 for example, it becomes:

• 11 for the DAY
• -
• sep for the MONTH
• -
• 20 for the RR year - which is 2020
• (space is ignored in the format, unless you use fx - for Format eXact)
• 07 for the HOUR

We can see this easily:

ops$tkyte%ORA11GR1> create table t ( x timestamp, y timestamp );Table created.

ops$tkyte%ORA11GR1> insert into t values ( ‘23-feb-2007′, to_date( ‘23-feb-2007′ ) );1 row created.

ops$tkyte%ORA11GR1> select * from t;

X                                   Y———————————– ——————————23-FEB-20 07.00.00.000000 AM        23-FEB-07 12.00.00.000000 AM

 When we convert a string directly into the timestamp - we get 23-feb-2020, hour 7am.  If we use a DATE - which has a different default mask of dd-mon-rr - which works also with dd-mon-rrrr dates since the time is NOT included - we get the expected value.

So, the problem I encountered was an implicit conversion - introduced by me right before the session.  (Note to self: stop changing stuff before sessions, it is a bad idea).

The fix would be to use to_timestamp:

ops$tkyte%ORA11GR1> insert into audit_trail (ts,data) values ( to_timestamp('11-sep-2007','dd-mon-yyyy'), 'xx' );

1 row created.

ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value  2    from user_tab_partitions  3   where table_name = ‘AUDIT_TRAIL’  4   order by partition_position;

PARTITION_ TABLESPACE HIGH_VALUE———- ———- ———————————–P0         USERS      TIMESTAMP’ 2007-02-23 00:00:00′SYS_P204   USERS      TIMESTAMP’ 2007-06-03 00:00:00′SYS_P206   EXAMPLE    TIMESTAMP’ 2007-09-12 00:00:00′SYS_P205   USERS      TIMESTAMP’ 2007-09-17 00:00:00′

So, now the demo works as expected and no (evil) implicit conversions happen…