Simulate UNION with BLOBs

Posted by in Software

TL;DR: If you have BLOBs, you need to use UNION ALL. If you use UNION ALL, you need to partition off a criterion and select the first row.

The other day I had to figure out how UNION works in Oracle, because we’re retrofitting an application with extra features. Assuming I have 2 tables(table_1 and table_2) with identical structures:

I need to perform an UNION to access both tables’fields:

Unfortunately, I’ve found out that you can’t just do UNION when you have BLOB data because a simple UNION would eliminate duplicates. So… I needed a way to fake that. The best I could come up with is:

Here’s what happens:

  • Using UNION ALL will get us duplicated rows (i.e. duplicated file names)
  • In Union SELECT (1) I’m adding a column src to identify the source (W from table_1 and S from table_2 respectively)
  • In Partition SELECT (2) I’m partitioning by file_name (the key we’re interested in) and create yet another column RW
  • In Eliminate duplicates SELECT (3) I’m picking up the first row of the duplicates.

The main effects are:
– we can perform an UNION-like statement with tables containing BLOB fields, where our uniqueness criteria don’t imply the BLOB itself
– We can prioritise results from either table. E.g. I know table_2 has newer versiond of the file and I’d prefer having the newer version available.

Bonus

You can apply the prioritisation also with simple UNION statements, particularly if you have odd fields like length(file_content) which would render the duplicate removal useless:

HTH,


A little experiment: If you find this post and ad below useful, please check the ad out :-)