Oracle SYNONYMs are a great feature. You can implement all sorts of backwards-compatibility tweaks simply by creating SYNONYMs in your database. Consider the following schema:
CREATE TABLE my_table (col NUMBER(7));
CREATE SYNONYM my_table_old FOR my_table;
CREATE SYNONYM my_table_bak FOR my_table_old;
Now you can query your same old table through three different names, it’ll all result in the same output:
SELECT * FROM my_table;
-- Same thing:
SELECT * FROM my_table_old;
SELECT * FROM my_table_bak;
The trouble is, when you see
my_table_bak in code (or some even more obfuscated name), do you immediately know what it really is?
Use this query to find out
We can use the ALL_SYNONYMS table to figure this one out. This query will already give a simple overview:
SELECT *
FROM ALL_SYNONYMS
WHERE TABLE_OWNER = 'PLAYGROUND'
The output is:
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
---------------------------------------------------
PLAYGROUND MY_TABLE_BAK PLAYGROUND MY_TABLE_OLD
PLAYGROUND MY_TABLE_OLD PLAYGROUND MY_TABLE
But as you can see, this is boring, because we have transitive synonyms in there and I don’t want to go through the complete table to figure out that
MY_TABLE_BAK -> MY_TABLE_OLD -> MY_TABLE.
So let’s use CONNECT BY!
Oracle (as well as Informix and CUBRID) have this awesome
CONNECT BY clause for hierarchical SQL. There is also the possibility to express
hierarchical SQL using the more powerful common table expressions, if you dare.
But let’s see how we can transitively resolve our tables. Here’s how:
SELECT
s.OWNER,
s.SYNONYM_NAME,
-- Get to the root of the hierarchy
CONNECT_BY_ROOT s.TABLE_OWNER TABLE_OWNER,
CONNECT_BY_ROOT s.TABLE_NAME TABLE_NAME
FROM ALL_SYNONYMS s
WHERE s.TABLE_OWNER = 'PLAYGROUND'
-- The magic CONNECT BY clause!
CONNECT BY s.TABLE_OWNER = PRIOR s.OWNER
AND s.TABLE_NAME = PRIOR s.SYNONYM_NAME
First off, there is
CONNECT BY, which allows to “connect” hierarchies by their hierarchical predecessors. On each level of the hierarchy, we’ll connect the
TABLE_NAME with its previous (“PRIOR”)
SYNONYM_NAME. This will recurse as long as the chain doesn’t end (or if it runs into a cycle).
What’s also interesting is the
CONNECT_BY_ROOT keyword, which, for each path through the hierarchy, displays the root of the path. In our case, that’s the target
TABLE_NAME.
The output can be seen here:
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
---------------------------------------------------
PLAYGROUND MY_TABLE_OLD PLAYGROUND MY_TABLE
PLAYGROUND MY_TABLE_BAK PLAYGROUND MY_TABLE
PLAYGROUND MY_TABLE_BAK PLAYGROUND MY_TABLE_OLD <-- Useless
If you’re confused by the records that are displayed, just add the
LEVEL pseudo-column to display the recursion level:
SELECT
-- Add level here
LEVEL,
s.OWNER,
s.SYNONYM_NAME,
CONNECT_BY_ROOT s.TABLE_OWNER TABLE_OWNER,
CONNECT_BY_ROOT s.TABLE_NAME TABLE_NAME
FROM ALL_SYNONYMS s
WHERE s.TABLE_OWNER = 'PLAYGROUND'
CONNECT BY s.TABLE_OWNER = PRIOR s.OWNER
AND s.TABLE_NAME = PRIOR s.SYNONYM_NAME
LEVEL OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
----------------------------------------------------------
1 PLAYGROUND MY_TABLE_OLD PLAYGROUND MY_TABLE
2 PLAYGROUND MY_TABLE_BAK PLAYGROUND MY_TABLE
1 PLAYGROUND MY_TABLE_BAK PLAYGROUND MY_TABLE_OLD
^^^^^^
Awesome!
Getting rid of “bad records” using START WITH
As you can see,
some of the results are now synonyms pointing directly to the target table, whereas the last record still points to an intermediate element from the synonym path. This is because we’re recursing into the path hierarchies from
every record in the table, also from the “intermediate” synonym references, whose
TABLE_NAME is yet another synonym.
Let’s get rid of those as well, using the optional
START WITH clause, which allows to limit tree traversals to those trees whose roots fulfil a given predicate:
SELECT
s.OWNER,
s.SYNONYM_NAME,
CONNECT_BY_ROOT s.TABLE_OWNER TABLE_OWNER,
CONNECT_BY_ROOT s.TABLE_NAME TABLE_NAME
FROM ALL_SYNONYMS s
WHERE s.TABLE_OWNER = 'PLAYGROUND'
CONNECT BY s.TABLE_OWNER = PRIOR s.OWNER
AND s.TABLE_NAME = PRIOR s.SYNONYM_NAME
-- Start recursing only from non-synonym objects
START WITH EXISTS (
SELECT 1
FROM ALL_OBJECTS
WHERE s.TABLE_OWNER = ALL_OBJECTS.OWNER
AND s.TABLE_NAME = ALL_OBJECTS.OBJECT_NAME
AND ALL_OBJECTS.OWNER = 'PLAYGROUND'
AND ALL_OBJECTS.OBJECT_TYPE <> 'SYNONYM'
)
So, essentially, we’re requiring the
TABLE_NAME to be any object from
ALL_OBJECTS that is in our schema, but not a
SYNONYM. (yes, synonyms work for all objects, including procedures, packages, types, etc.)
Running the above query gets us the desired result:
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
---------------------------------------------------
PLAYGROUND MY_TABLE_OLD PLAYGROUND MY_TABLE
PLAYGROUND MY_TABLE_BAK PLAYGROUND MY_TABLE
What about PUBLIC synonyms?
Most often, you will not use local synonyms, though, but PUBLIC ones. Oracle has this quirky
PUBLIC pseudo-schema, in which you cannot create objects, but in which you can create synonyms. So, let’s create some more synonyms for backwards-compatibility purposes:
CREATE PUBLIC SYNONYM my_table_bak2 FOR my_table_bak;
CREATE SYNONYM bak_backup_old FOR my_table_bak2;
Unfortunately, this will break our chain, because for some reason only Oracle and the Oracle of Delphi knows,
PUBLIC is well reported as a
OWNER of the synonym, but not as the
TABLE_OWNER. Let’s see some raw data with:
SELECT *
FROM ALL_SYNONYMS
WHERE TABLE_OWNER = 'PLAYGROUND'
… and thus:
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
------------------------------------------------------
PLAYGROUND MY_TABLE_OLD PLAYGROUND MY_TABLE
PLAYGROUND MY_TABLE_BAK PLAYGROUND MY_TABLE_OLD
PUBLIC MY_TABLE_BAK2 PLAYGROUND MY_TABLE_BAK
PLAYGROUND BAK_BACKUP_OLD PLAYGROUND MY_TABLE_BAK2 <-- Not PUBLIC
As you can see, the
PUBLIC SYNONYM MY_TABLE_BAK2 is reported to be in the
PLAYGROUND schema! This breaks recursion, of course. We’re missing a record:
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
------------------------------------------------------
PLAYGROUND MY_TABLE_OLD PLAYGROUND MY_TABLE
PLAYGROUND MY_TABLE_BAK PLAYGROUND MY_TABLE
PUBLIC MY_TABLE_BAK2 PLAYGROUND MY_TABLE <-- Hmm?
In order to work around this issue, we’ll have to tweak our original data set. Any object reported as
(TABLE_OWNER, TABLE_NAME) might in fact be a synonym called
('PUBLIC', TABLE_NAME). The trick is thus to simply duplicate all input data as such:
SELECT
s.OWNER,
s.SYNONYM_NAME,
CONNECT_BY_ROOT s.TABLE_OWNER TABLE_OWNER,
CONNECT_BY_ROOT s.TABLE_NAME TABLE_NAME
-- Tweaked data set
FROM (
SELECT OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
FROM ALL_SYNONYMS
UNION ALL
SELECT OWNER, SYNONYM_NAME, 'PUBLIC', TABLE_NAME
FROM ALL_SYNONYMS
) s
-- Add the synthetic PUBLIC TABLE_OWNER as well
WHERE s.TABLE_OWNER IN (
'PLAYGROUND', 'PUBLIC'
)
CONNECT BY s.TABLE_OWNER = PRIOR s.OWNER
AND s.TABLE_NAME = PRIOR s.SYNONYM_NAME
START WITH EXISTS (
SELECT 1
FROM ALL_OBJECTS
WHERE s.TABLE_OWNER = ALL_OBJECTS.OWNER
AND s.TABLE_NAME = ALL_OBJECTS.OBJECT_NAME
AND ALL_OBJECTS.OWNER = 'PLAYGROUND'
AND ALL_OBJECTS.OBJECT_TYPE <> 'SYNONYM'
)
There it is, our missing record!
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
---------------------------------------------------
PLAYGROUND MY_TABLE_OLD PLAYGROUND MY_TABLE
PLAYGROUND MY_TABLE_BAK PLAYGROUND MY_TABLE
PUBLIC MY_TABLE_BAK2 PLAYGROUND MY_TABLE
PLAYGROUND BAK_BACKUP_OLD PLAYGROUND MY_TABLE <-- Yep!
Displaying the hierarchy
There is also a quirky function called
SYS_CONNECT_BY_PATH, which can be used to actually display the whole hierarchy in a string form (VARCHAR2, with max 4000 characters!). Here’s how:
SELECT
-- Magic function
SUBSTR(
sys_connect_by_path(
s.TABLE_OWNER
|| '.'
|| s.TABLE_NAME, ' <- '
) || ' <- '
|| s.OWNER
|| '.'
|| s.SYNONYM_NAME, 5
)
FROM (
SELECT OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
FROM ALL_SYNONYMS
UNION ALL
SELECT OWNER, SYNONYM_NAME, 'PUBLIC', TABLE_NAME
FROM ALL_SYNONYMS
) s
WHERE s.TABLE_OWNER IN (
'PLAYGROUND', 'PUBLIC'
)
CONNECT BY s.TABLE_OWNER = PRIOR s.OWNER
AND s.TABLE_NAME = PRIOR s.SYNONYM_NAME
START WITH EXISTS (
SELECT 1
FROM ALL_OBJECTS
WHERE s.TABLE_OWNER = ALL_OBJECTS.OWNER
AND s.TABLE_NAME = ALL_OBJECTS.OBJECT_NAME
AND ALL_OBJECTS.OWNER = 'PLAYGROUND'
AND ALL_OBJECTS.OBJECT_TYPE <> 'SYNONYM'
)
The above query will now output the following records:
PLAYGROUND.MY_TABLE <- PLAYGROUND.MY_TABLE_OLD
PLAYGROUND.MY_TABLE <- PLAYGROUND.MY_TABLE_OLD <- PLAYGROUND.MY_TABLE_BAK
PLAYGROUND.MY_TABLE <- PLAYGROUND.MY_TABLE_OLD <- PLAYGROUND.MY_TABLE_BAK <- PUBLIC.MY_TABLE_BAK2
PLAYGROUND.MY_TABLE <- PLAYGROUND.MY_TABLE_OLD <- PLAYGROUND.MY_TABLE_BAK <- PUBLIC.MY_TABLE_BAK2 <- PLAYGROUND.BAK_BACKUP_OLD
Impressive, eh?
Remark: In case you have stale synonyms
If you have “stale” synonyms, i.e. synonyms that point to nowhere, Oracle may report them to be pointing to themselves. That’s unfortunate and creates a CYCLE in
CONNECT BY. To prevent this from happening, simply add another predicate like so:
SELECT
SUBSTR(
sys_connect_by_path(
s.TABLE_OWNER
|| '.'
|| s.TABLE_NAME, ' <- '
) || ' <- '
|| s.OWNER
|| '.'
|| s.SYNONYM_NAME, 5
)
FROM (
SELECT * FROM (
SELECT OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
FROM ALL_SYNONYMS
UNION ALL
SELECT OWNER, SYNONYM_NAME, 'PUBLIC', TABLE_NAME
FROM ALL_SYNONYMS
) s
-- Add this predicate to prevent cycles
WHERE (s.OWNER , s.SYNONYM_NAME)
!= ((s.TABLE_OWNER , s.TABLE_NAME))
) s
CONNECT BY s.TABLE_OWNER = PRIOR s.OWNER
AND s.TABLE_NAME = PRIOR s.SYNONYM_NAME
START WITH EXISTS (
SELECT 1
FROM ALL_OBJECTS
WHERE s.TABLE_OWNER = ALL_OBJECTS.OWNER
AND s.TABLE_NAME = ALL_OBJECTS.OBJECT_NAME
AND ALL_OBJECTS.OWNER = 'PLAYGROUND'
AND ALL_OBJECTS.OBJECT_TYPE <> 'SYNONYM'
)
Can the above query be written in jOOQ?
Yes of course. In
jOOQ, pretty much everything is possible, if you can write it in SQL. Here’s how we use a query similar to the above to resolve Oracle Synonmys in the jOOQ code generator:
// Some reusable variables
AllObjects o = ALL_OBJECTS;
AllSynonyms s1 = ALL_SYNONYMS;
AllSynonyms s2 = ALL_SYNONYMS.as("s2");
AllSynonyms s3 = ALL_SYNONYMS.as("s3");
Field<String> dot = inline(".");
String arr = " <- ";
// The actual qeury
DSL
.using(configuration)
.select(
s3.OWNER,
s3.SYNONYM_NAME,
connectByRoot(s3.TABLE_OWNER).as("TABLE_OWNER"),
connectByRoot(s3.TABLE_NAME).as("TABLE_NAME"),
substring(
sysConnectByPath(
s3.TABLE_OWNER.concat(dot)
.concat(s3.TABLE_NAME),
arr
)
.concat(arr)
.concat(s3.OWNER)
.concat(dot)
.concat(s3.SYNONYM_NAME),
5
))
.from(
select()
.from(
select(
s1.OWNER, s1.SYNONYM_NAME,
s1.TABLE_OWNER, s1.TABLE_NAME)
.from(s1)
.union(
select(
s1.OWNER, s1.SYNONYM_NAME,
inline("PUBLIC"), s1.TABLE_NAME)
.from(s1))
.asTable("s2"))
.where(row(s2.OWNER, s2.SYNONYM_NAME)
.ne(s2.TABLE_OWNER, s2.TABLE_NAME))
.asTable("s3"))
.connectBy(s3.TABLE_OWNER.eq(prior(s3.OWNER)))
.and(s3.TABLE_NAME.eq(prior(s3.SYNONYM_NAME)))
.startWith(exists(
selectOne()
.from(o)
.where(s3.TABLE_OWNER.eq(o.OWNER))
.and(s3.TABLE_NAME.eq(o.OBJECT_NAME))
.and(o.OBJECT_TYPE.ne("SYNONYM"))
.and(o.OWNER.in(getInputSchemata()))
))
.fetch();
today and try it yourself!
Conclusion
If you have an intrinsically hierarchical data set, then you will be very unhappy with these simplistic hierarchical SQL features (also with commont table expressions). They don’t perform very well, and they’re very hard to express if hierarchies get more complex. So you may as well consider using an actual graph database like
Neo4j
But every now and then, a little hierarchy may sneak into your otherwise “standard” relational data model. When it does, be sure to have this useful
CONNECT BY clause ready for action.
CONNECT BY is supported by (at least):
Recursive common table expressions (the SQL standard’s counterpart for
CONNECT BY are supported by (at least):
- DB2
- Firebird
- HSQLDB
- Oracle
- PostgreSQL
- SQL Server
- Sybase SQL Anywhere
and…
- H2 has some experimental support
In a future post, we’re going to be looking into how to do the same thing with recursive CTE.
Like this:
Like Loading...
