Beyond 32k? How to overcome the 32 KB pagesize limit
Doing so I stumbled upon one change I actually implemented myself that had nearly slipped my mind.
The change is very minor, and, on the face of it has nothing to do with Oracle applications.
What I implemented for DB2 9.7.3 were comparison operators for "small LOBs"
A small LOB in itself is a bit of an oxymoron, given that the L stands for Large.
Also Oracle allows a lot less native operations on LOBs than DB2 to begin with. And comparison operators are not amongst them.
So, what's the motivation?
Imagine you have a table that looks like this:
OK, perhaps the columns in this table are a tad silly.CREATE TABLE emp(empid INTEGER,
But who can earnestly say they have never seen string types maxed out (4000 is Oracle's max for VARCHAR in SQL).
Does anyone have a name 4000 bytes long?
Unlikely, but just because the developer who designed this application ended up taking the easy route when defining the domain of thecolumn doesn't mean we can simply override it to another smaller value.
Also there are cases when strings can indeed get rather big - on occasion.
One example may a "product description" or some "remarks" column.
Now, given DB2's limit of 32KB (32677 to be exact) per row, how do you implement a schema like the above?
One way, of course is to split the table vertically. In that case a join is required to reconstitute the table.
This will be OK for selects. After all the join can be hidden in a view.
But DB2 does not allow updates through joins.
Even INSTEAD OF triggers won't help there because transition variables mustn't exceed the maximum row size either.
Another approach is to "lobify" some of the columns.
But LOB's are said to have some downsides:
So, slow access and slow retrieval of LOBs are simply not true anymore in many cases.
The most important case here, being a VARCHAR(4000) gone CLOB.
We also figured that for those small LOBs which could have been VARCHARs to begin with we could simply compare them as VARCHARs.
If the actual size of the LOB is less than 32KB then the comparison will succeed. If it is bigger it will fail.
A VARCHAR(4000) that has been turned into a CLOB(4000) will therefore always be comparable.
So what DB2 does for LOBs in general is to convert:
<clob> <compare> <something>
CAST(<clob> AS VARCHAR(32672)) <compare> <something>
Similarly DBCLOB (NCLOB) is cast to VARGRAPHIC and BLOB to VARCHAR FOR BIT DATA.The functions supported are:
Not that the IN predicate is absent here for the time being.
That leaves only one limitation: You still cannot index LOBs, even small ones.
However, I think that in many cases when strings are large there is no desire to index the column to begin with.
What's the point of indexing "remarks" or "description"?
These are candidates for full text indexes which is a different topic altogether, but not for a b-tree index.
So our silly table above could be tweaked as such:
I have avoid changing name, firstname and title because they could reasonably indexed.CREATE TABLE emp(empid INTEGER,
A query such as:
will work unchanged thanks to comparison support for small LOBsSELECT jobdesc FROM emp WHERE languages LIKE '%ENGLISH%' AND education = 'University"